Scenarios
 

Hi,

I have a detailed calculation in Excel and wish to run that calculation
for a range of input values. I can use the Tools, Scenarios but this
requires that I manually enter each of the input values. I am looking
for a way to get the input values from a list of values in Excel.

To simplify. I have a range of input values in the range A1:A100. I
wish for the result of the calculation for each input value to be
placed in the cells next to the corresponding input value., i.e.
results to ge on the range B1:B100.

For arguments sake lets assume that the input cell for the calculation
is in cell E1 and that the result of the calculation can be found in
cell G30. The calculation does not lend itself to being placed on a
single line as it consists of multiple sheets using lookup tables.

I could write a macro to take each cell in turn from a named range
(InputRange), place it in the InputCell, collect the resulting value
from the OutputCell and place it in the cell adjacent to the
corresponding cell in the InputRange.

Is there a way to do this in Excel without writing a macro?

Any assistance will be appreciated.

Sean

... input cell for the calculation is in cell E1 and
... the result of the calculation can be found in cell G30

Sounds like a one variable data table could do it here. Try this ..

Put the range of 100 input values into A2:A101
Put in B1: =G30

Select A1:B101
Click Data Table

In the Table dialog:
Leave Row input cell empty
Put in the Column input cell: E1
Click OK

B2:B101 should return the 100 calculated results
for the 100 inputs in A2:A101

And if you change the inputs within A2:A101,
the table will recalc accordingly

Note: The formula: {=TABLE(,E1)} will appear in every cell within B2:B101.
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
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Sean" wrote in message
oups.com...
Hi,

I have a detailed calculation in Excel and wish to run that calculation
for a range of input values. I can use the Tools, Scenarios but this
requires that I manually enter each of the input values. I am looking
for a way to get the input values from a list of values in Excel.

To simplify. I have a range of input values in the range A1:A100. I
wish for the result of the calculation for each input value to be
placed in the cells next to the corresponding input value., i.e.
results to ge on the range B1:B100.

For arguments sake lets assume that the input cell for the calculation
is in cell E1 and that the result of the calculation can be found in
cell G30. The calculation does not lend itself to being placed on a
single line as it consists of multiple sheets using lookup tables.

I could write a macro to take each cell in turn from a named range
(InputRange), place it in the InputCell, collect the resulting value
from the OutputCell and place it in the cell adjacent to the
corresponding cell in the InputRange.

Is there a way to do this in Excel without writing a macro?

Any assistance will be appreciated.

Sean

Max,

Thanks for the help, exactly what I wanted.

Sean

Glad to hear that, Sean !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Sean" wrote in message
oups.com...
Max,

Thanks for the help, exactly what I wanted.

Sean