PivotTable, Scenarios, Macro??
 

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 ..

--
Note: The formula: {=TABLE(,A1)} will appear in F2:F7
Albeit it looks like an array formula, it cannot just
be entered as such. The construct must be done/invoked
via the Data Table steps outlined
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave Wilson" wrote in message
...
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

Max,

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

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

"Max" wrote in message
...
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 ..

--
Note: The formula: {=TABLE(,A1)} will appear in F2:F7
Albeit it looks like an array formula, it cannot just
be entered as such. The construct must be done/invoked
via the Data Table steps outlined
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave Wilson" wrote in message
...
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

"Dave Wilson" wrote
Thanks a million, I can use this all over the place!

You're welcome, Dave !
Glad it helped ..

.. Where are you?

In Singapore .. just a couple of degrees
off - dead centre in this island-city-nation <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

For "Column input cell", put: $A$1
(or just inside the box, then click outside on cell A1)

Minor typo correction: 2nd line above should read as
(or just click inside the box, then click outside on cell A1)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----