....looks like my first try didn't take. Sorry if this is a duplicate.
~sb1920alk
_________________________

I think you want to use a circular reference. It sounds like you have your
parameters set up for each scenario already in a table.

He's what I set up. I have two parameters, one in Column B, and the other in
Column C, starting in row 2. My simple model seeks to multiply each value in
column B by 10, then add it to the value in column C. The result will be
recorded in the same row in column D.

In Column A, I've numbered each scenario...A2=1, A3=2, A4=3...

In B1 =SUMPRODUCT(B2:B10,--($A2:$A10=$A1)) ...I only went to row 10.
In C1 = =SUMPRODUCT(C2:C10,--($A2:$A10=$A1))
....repeat for each of your parameter columns, and put these where you
actually input your parameters to your model

My model has only one solution, and it's in D1 =B1*10+C1. Compare to you
model, B1, and C1 hold the parameters for the "current" scenario, and D1 is
the result of the model's calcuation.

I can control which scenario I wish to calculate or examine by typing it's
number in A1. This turns B1 and C1 to match their parameters for the
appropriate scenario.

I record the result from D1 in D2 for scenario 1, D3 for scenario 2, etc.
using =IF(A2=A$1,D$1,IF(D20,D2,"")) in D2 and copy down. The results are
saved when you change scenarios.

You can reset the results by clicking in the formula bar and pressing enter
while the scenario number is different, although this could be tedious if you
had to do it a lot. I find that having a global "reset" option can be
helpful. For example, entering something in E1 will clear my saved values.
Just encapsulate the circular formula with something like IF(E1<"",0 or "",
etc.

You can also protect your result from changing parameters if you like by
placing the circle before the result reference in the circular formula.

If you get tired of typing 1, then 2, then 3...in A1, you can have the
computer do it for you with this in A1: =A1+1. On my simple scenario, the
result appear instantly.

Make sure the Iterations box it checked. If the number of iterations is less
then the number of scenarios, you'll have to re-calc until you get there.

Regards,

"Martin James Thornhill" wrote:

That's a shame.

One of the most elementary things that a computer should do is "work the
sausage factory". It's where computing started!

"Here is the way the factory works, here are a few ingredients (in varying
proportions), what is the result for each combination?"

It seems that one needs to be a brain surgeon to program in VisualBasic to
do something that I knew how to do using BBC Basic (all those years ago!).

Thanks for your help - likewise to driber2 - but the only realistic (and
time-effective) manner to do this is to punch the parameters into the model,
copy the result and paste-value it into the correct cell of the summary.

Note to Microsoft or anybody else who can write Excel 2000+ Add-Ins: invent
a function =CALLSCENARIO(<name,p1,p2,p3,...pn,o):

* where <name is a defined scenario on the server worksheet (that might
exist in a different worksheet than the client worksheet), e.g.
(c:\test.xls[Sheet1]!"Scenario Test");

* where p1...pn are the parameters that the scenario requires; and

* where o describes the outcome cell required per scenario, e.g. if the
scenario summarises 3 cells, then o = 2 would return the second cell.


In cell, say, B10, the user punches in =CALLSCENARIO(Test,a10,b3,100,200,3).

How easy is this?!


Many thanks.


"Dave F" wrote:

I don't believe the Scenario Manager can be used in that manner. Perhaps
there is a VBA-based solution to your problem but you said in your original
post that you're not interested in going down that road....

Dave
--
Brevity is the soul of wit.


"Martin James Thornhill" wrote:

Thanks Dave.

This is more like it, but it is incredibly tedious. Rather than type
parameters into dialogue box for every combination of parameter required, can
I use a formula to run the scenario, taking its parameters from elsewhere in
the calling sheet?


"Dave F" wrote:

I would look at the Scenario tool.

Tools--Scenarios...

Post back if you have questions.

Dave


--
Brevity is the soul of wit.


"Martin James Thornhill" wrote:

In Excel 2000, I have a complex calculation (in a worksheet) that takes 4
parameters and uses them in 4 different sub-calculations, involving
relative/conditional sub-sub-calculations. In effect, it's a model.

Having done this, I now want to pour a series of numbers into this model, to
see its effect on various combinations of parameters.

Rather than type each parameter in, copy-paste-value the result into a
summary table, how can I get Excel to run the model on call from a worksheet?

I am illiterate in VisualBasic, so anything that avoids macroes would be cool.