Generating Data for Graphing

[ricksewell]

I've been looking for a way to have Excel generate data from a
worksheet so that I can graph it. I want to be able to run through a
series of inputs for one of the variables in in my worksheet and
generate the corresponding outputs to a table that I can then graph. I
could change the variable in the input cell manually and copy the
result by hand to a separate table but that would be a very static
data set. I'd have to redo it every time I changed something in the
source table. Am I missing something, it seems like this should be
fairly simple. The closest thing I've been able to find are the "What-
if" tools, but they don't do it for me. Man, I need some help. I've
been scratching my head on this for quite some time now.

-rick

 

[Jon Peltier]

Well, I don't know what your data will look like, so I'll generalize. If I
wanted to plot three linear relationships, I'd set up six columns. I'd leave
the top few rows blank for now. The linear relationships need a slope and an
intercept, so I'd put the slope in the second row of the first column of
each pair of data columns, and the intercept in the second row of the second
column. I'd skip another row, put a label like "Line 1" in the fourth row of
the second column of each pair of columns, and place a range of appropriate
X values in the first column starting in row 5. In row five of the second
column I'd write a formula relating the slope and intercept to the X value
to the left. If I were using columns A and B and this formula is in cell B5,
the formula would be

=B$2+A$2*A5

I'd fill this down the second column as far as the X values reached in the
first column. Then I'd copy these two columns, move to the right two columns
and paste, then again, so I had three sets of data. Now I can manually or
programmatically change the slope and intercept and change the three sets of
data. I'd make an XY chart with three series.

If your relationship is more complicated, you need more cells for your
arguments and your formula needs to be more detailed. But this is the
general approach you should take.

- Jon