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

enter image description here

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.

9
  • 1
    =REPT(2.23,7) may help you, you can replace the values with cell values etc.
    – PeterH
    Jul 6, 2018 at 15:28
  • 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) Jul 6, 2018 at 16:28
  • You could always write a udf that returns an array. Jul 6, 2018 at 16:37
  • Since I'm writing UDFs for other parts of my spreadsheet, that is probably the way to go. Jul 6, 2018 at 16:44
  • 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. Jul 6, 2018 at 16:48

3 Answers 3

2

Edited: The following is a solution to the question I posted, but might not be the best way to actually provide "recommended" or "threshold" lines, which was my actual goal.

Technically Correct Answer

I think I figured out a decent solution:

  1. Create a user-defined function (UDF) that returns an array, as suggested by @ScottCraner.
  2. Define a named range that refers to ("calls") that function.
  3. Set the source of the series to that named range.

An example of the results and the macro-enabled spreadsheet is on my site at https://woltman.com/m/filer_public/cc/ba/ccba65d2-e29c-4208-b3be-53ead02845ff/arraysforchartdataexample.xlsm

Probably a Better Solution

This solution follows @fixer1234's suggestion:

  1. Make a pair of XY data points. The Y values are your recommended data. The first X value is 1, the second X value is however many bars you have in your bar chart.
  2. Create a normal bar chart
  3. Add another series to the chart, and set the series type to XY with line.
  4. Set the source data for the series to the XY pair you made in Step 1.
  5. Excel will move the series to the secondary axis. Put the series back on the primary axis.
  6. Remove the markers from the XY series so that it just looks like a plain line.

This requires no extra code at all, and is very clean. Sample using XY scatter with a line

1
  • good job. please show the UDF as text it will help those in the future. Many will not download a .xlsm from an open forum. Jul 6, 2018 at 17:35
0

Put 2.23 in the top cell.

In the next one down put =B2.

Assuming that the 2.23 was put in B2 and the formula in B3, then just copy down.

enter image description here

Then if you want to change the number you only need to change B2 and the rest will change

enter image description here

1
  • Basing the rest of the values off of the first cell is definitely a good idea, but still requires a bunch a column of data. I'm not sure Excel can support what I want, so I'll probably go with your solution. Jul 6, 2018 at 16:31
0

I would like to suggest a Macro (VBA) which repeats any value either Number, Text or even Date to N number of times.

enter image description here

Sub RepeatData()

Dim Rng As Range
Dim InputRng As Range, OutRng As Range

xTitleId = "Repeat Data"

Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")

For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
Next

End Sub

How it works:

  • Insert this Macro as Module with the related Sheet.
  • Run the Macro & respond the first Input Box by selecting A3:B3.
  • Respond the Second Input Box by selecting any Single Cell like E3.
  • Finish with Ok.

You get the desire result as shown in Screen shot.

Note, I've purposely included Situation 2, to show that the Macro can repeat any Value to any number of times.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .