1
I have a chart that should have a recommended line going through it. I would normally do this by having a "recommended" value for every value I chart. Is it possible to have a formula that repeats a bunch of values so that I don't actually have to maintain a list of "recommended" values? Here's a screenshot example
In my head it works like: =MakeArray(2.23, 7)
where 2.23 is the value and 7 is the number of repetitions. Then I could assign that to the series and not actually have to maintain another series of data that I don't actually care about.
1=REPT(2.23,7) may help you, you can replace the values with cell values etc. – PeterH – 2018-07-06T15:28:12.493
It seems that REPT only returns a text string, not an array. I couldn't get Excel to chart the values returned from =REPT(2.23,7) – J Woltman – 2018-07-06T16:28:01.533
You could always write a udf that returns an array. – Scott Craner – 2018-07-06T16:37:46.007
Since I'm writing UDFs for other parts of my spreadsheet, that is probably the way to go. – J Woltman – 2018-07-06T16:44:12.803
But, you can't reference the UDF directly in the chart, at least not that I have found in the past 5 minutes of searching. so if you do find that to work please come back and answer your own question detailing how you did it. – Scott Craner – 2018-07-06T16:48:10.387
Why do you need a value to plot for each column when you're plotting a fixed value? Just create a point for the first and last column. Excel will draw a line between the endpoints. – fixer1234 – 2018-07-06T22:24:48.577
@fixer1234 Thanks for the idea, I will experiment with it. – J Woltman – 2018-07-08T01:33:25.563
@fixer1234 I tried it out and it worked great. I'm not sure of Superuser etiquette/norms. Should I accept my answer because it technically answers my original question, or should I rewrite it with your solution instead? – J Woltman – 2018-07-09T18:42:55.997
Another option would be to add it to your own answer or post it as a second answer (since it's an entirely different approach). What you select as the accepted answer is entirely your prerogative as the question author. You get to decide which answer you think is best for your own needs. You also now have enough rep that you can upvote any answers you think are helpful, in additional to accepting one answer as "best for you" (which can be your own, but you can't vote on your own answers). – fixer1234 – 2018-07-09T22:00:16.907