X Y -10 100 -5 25 -2.5 6.25 0 0 2.5 6.25 5 25 10 100
So I have the above sample data sitting in the range C4:D10
In A5 I place the number of data points (rows) I wish to include in my graph
Since A5 can change I come up with two formulas that will give me a dynamic range.
X-Values =Sheet1!$C$4:INDEX(Sheet1!$C:$C,3+Sheet1!$A$5) Y-Values =Sheet1!$D$4:INDEX(Sheet1!$D:$D,3+Sheet1!$A$5)
X-Values =OFFSET($C$4,0,0,$A$5,1) Y-Values =OFFSET($D$4,0,0,$A$5,1)
I also put the first formula into a named range
When I try to enter the formulas or named ranges in the area for the X and Y values in an X-Y Scatter plot, I get an error.
Doing some research, I stumbled across an entry somewhere that said something about placing the name of the workbook out front. This worked for the named ranges.
However when I tried to do it with just the formulas, I got the error again.