PivotTable, Scenarios, Macro??

  • Thread starter Thread starter Dave Wilson
  • Start date Start date
D

Dave Wilson

Friends,

I have a complex spreadsheet that calculates a value. I want to use that
sheet to calculate the results using a column of numbers as inputs to the
sheet and putting the results adjacent to the input value so that I can then
graph to array of input value vs. the output values. I am tired of doing
this one value at a time.

Thanks, Dave
 
Think a one variable data table will do it ..

A simple illustration?

Suppose you have an output cell C1: =A1*2-5
where C1 will change, depending on inputs into A1
(A1 is a precedent to C1)

Put a value in A1: 10 (say)

On the same sheet, put in say, F1: =C1
(point to the output cell of interest)

List some possible inputs into A1
down in E2:E7 (say): 10,20,30 .. 60

Select E1:F7

Click Data > Table
Leave "Row input cell" empty
For "Column input cell", put: $A$1
(or just inside the box, then click outside on cell A1)
Click OK

You'll get the results in E2:F7

10 15
20 35
30 55
40 75
50 95
60 115

with:
Input values in E2:E7
Output values in the adjacent F2:F7
(Use the above to graph)

Try changing the input values listed in E2:E7, you'll see
the output values in F2:F7 will recalc correspondingly

Extend to suit ..
 
Max,

Thanks a million, I can use this all over the place!

Dave, Lat N7 59 Lon W 79 52, Panama. Where are you?
 
Back
Top