Getting x values
The next task is to get a list if x values we can use for the x-axes of the chart. A little known fact, is that when a defined name is used to name a formula this formula will be an array formula by default. We're going to put that to use.
First we need a set of incrementing numbers. We'll use the ROW worksheet function for that.
When we define this name local to worksheet Sheet1:
and we enter an array formula into cells A1:A20 (select A1:A20 in Sheet1 and type =x, then hit control-shift-enter) we get this result:
But we want the number of x-values to be dependent on a number entered into a cell. We'll use the OFFSET worksheet function for this purpose:
If we replace the previous formula in "Sheet1!x" with the one above, the result will remain the same as shown above.
All it takes now is replace the fixed 20 with a cell reference:
Now we have the numbers 1 to 20 (or up to whatever number we enter into cell B5 on sheet1).
But we need more flexibility, we want to be able to set a minimum and a maximum value for x and use the 1-20 range to space out these limits.
Let's define these named ranges:
To get a series of "xNumberOfPoints" from xStart to xEnd, the following formula applies:
xPoint=xStart+xRange/(xNumberOfPoints-1)*Counter(1 to xNumberOfPoints-1)
So applying the approach depicted above:
We'll name this new formula (surprisingly): Sheet1!x
Editing and creating names is much easier with the free Name Manager.