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.

i suggest that each sub-calculations be separated in different work books. Do
not place them in a single workbook or worksheet...Iteration can be powerful
and may give you the best if the parameters are linked in a circular
manner...the only thing you have to decide is on which parameter you have to
start and which parameter you have to complete all the sub-sub-multi-sub
iterations...iterations cannot bother a closed workbook...<your key to assure
that each parameter or conditions are sustained per each workbook.
4 parameters or sub-calculations = 4 workbook , for me...
good luck
--
*****
birds of the same feather flock together..



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

Thanks, but I'm not sure I understand.

OK, I can re-write the workbooks and split each calculation into a different
sheet. But I need one number, based upon a few parameters, into one cell.

In effect, x = f(a, b, c, d), where f() is the model. Is there a function
in Excel 2000 that enables me to do this? What is the prepatory work? I can
take the model - the complete model - into a single, separate worksheet,
ready for the summary table to call upon its services with parameters. And
this latter bit is the thing I don't know.

(fingers crossed that there is an easy solution)


"dribler2" wrote:

i suggest that each sub-calculations be separated in different work books. Do
not place them in a single workbook or worksheet...Iteration can be powerful
and may give you the best if the parameters are linked in a circular
manner...the only thing you have to decide is on which parameter you have to
start and which parameter you have to complete all the sub-sub-multi-sub
iterations...iterations cannot bother a closed workbook...<your key to assure
that each parameter or conditions are sustained per each workbook.
4 parameters or sub-calculations = 4 workbook , for me...
good luck
--
*****
birds of the same feather flock together..



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

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.

Maybe a little bit of imagination...
if the parameters a,b,c & d have to undergo an iteration...try releasing
your parameters under a single built-in or UDF function.

Expand the single function into bits of linked formulas where a, b, c & d
can reside in a separate workBOOK. and may i add one more workBOOK for the
$ummary.

iterations may not work on all parameters under a single function - if u
need the ultimate desired parameters.
--
*****
birds of the same feather flock together..



"Martin James Thornhill" wrote:

Thanks, but I'm not sure I understand.

OK, I can re-write the workbooks and split each calculation into a different
sheet. But I need one number, based upon a few parameters, into one cell.

In effect, x = f(a, b, c, d), where f() is the model. Is there a function
in Excel 2000 that enables me to do this? What is the prepatory work? I can
take the model - the complete model - into a single, separate worksheet,
ready for the summary table to call upon its services with parameters. And
this latter bit is the thing I don't know.

(fingers crossed that there is an easy solution)


"dribler2" wrote:

i suggest that each sub-calculations be separated in different work books. Do
not place them in a single workbook or worksheet...Iteration can be powerful
and may give you the best if the parameters are linked in a circular
manner...the only thing you have to decide is on which parameter you have to
start and which parameter you have to complete all the sub-sub-multi-sub
iterations...iterations cannot bother a closed workbook...<your key to assure
that each parameter or conditions are sustained per each workbook.
4 parameters or sub-calculations = 4 workbook , for me...
good luck
--
*****
birds of the same feather flock together..



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

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.

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.

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.

If it is as easy as you suspect, likely there is a solution out there. Spend
some time with Google. Maybe you'll stumble upon something.

Else, maybe reconsider how easy it is...

Dave
--
Brevity is the soul of wit.


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

Martin James Thornhill -

You may be able to automate some of the process using one-way and two-way
data tables (see "About data tables" in Help, and choose Data | Table).

- Mike
http://www.mikemiddleton.com

"Martin James Thornhill"
wrote in message ...
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.

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