Populate a list based on a single cell

Hi, I am helping my son with the display of a quadratic in Excel. I won't go into detail about what he is required to do but essentially I would like to do the following:

When I enter a value for 'Number of Steps' from a drop down a table is generated with the x values changing accordingly.

For example:

Number of Steps 5

x x^2
------------
0 0
1 1
2 4
3 9
4 16

Number of Steps 9

x x^2
------------
0 0
0.5 0.25
1 1
1.5 2.25
2 4
2.5 6.25
3 9
3.5 12.25
4 16

You will notice that the number of steps is the number of x values that take us up to 4. The 4 is fixed.
I would be able to work out the maths myself as I am a maths teacher but I was wondering how I could autopopulate the table based on a drop down box selection. If it involves going into VBA then don't worry my son needs to get to grips with what I have done.

Hope you can help.
Regards,
David.

Edit. The formatting does not seem to work above.
On the left of each table is supposed to be the x value and on the right is x^2.

Edit: Thanks for the comment. Changed accordingly.

Hi,

You have only nine steps in your second example. Could you please clarify?

Regards

Thanks.

Assuming your number of steps is in D1:

In A2 and copy down:

=IF(ROWS($1:1)>$D$1,"",4/($D$1-1)*(ROWS($1:1)-1))

In B2 and copy down:

=IF(A2="","",A2^2)

The examples you gave both resulted in the x values being integers for all steps. Obviously this wil not always be the case: however, you gave no indication of any rounding you would like to see, so none has been applied to these formulas.

Note that you can also make the upper limit a variable by replacing the 4 in the first formula by a suitable cell reference.

Regards

You're welcome.

Hi XOR LX,
I know that it is asking a lot but I have another question.
When I try to graph the quadratic the x axis values display incorrectly. For example if I fill your first formula down 30 rows and then select Number of Steps to be 10 say, the x axis values are displayed on the graph as simply integers 1,2,3 etc. even though they are displayed correctly in the table.

If I the Number of steps is equal to the number of cells that I have filled with the formula the graph displays correctly.

This has the effect of me having to insert a new graph each time and correctly selecting cells that only have values in them. This removes the dynamic feel of things.

To sum up. If I ask excel to display a graph where the x coordinates do not have values in them it causes the x coordinates to be replaced with 1,2,3 etc. for the whole graph.

Edit. I think I have solved this one. Rather than put "" in the empty cells I am now putting na() and the graph appears to cope with these well. Then I had #na in all of my cells but I painted them white and now you can't see them! This is called Whack a Mole isn't it?

Hey - let's just say that you're not alone in thinking that Excel's charting features aren't exactly top-of-the-range .

Looks like you (somehow) hit upon a workaround, though, so well done!

Cheers