Scenarios - how can I list a number of results exceeding the limit of 32 cells

[tony365]

Hi,

new here and need some assistance to find a way to overcome the 32 cells limit of the scenario function. Have searched the forum and internet for a while but did not manage to resolve the problem, yet.

My task: I'd like to prepare a comparison for different setups and conditions. The idea is to compare the performance of four different engines (parameter A: 1 - 4) under ten test conditions (parameter B, 1-10) and calculate for all the 40 cases a set larger set of values for each of the engines. It was my idea to use parameter A and B to define / select the different SCENARIOS (define engine and test procedure for each scenario) which worked fine. Unfortunately I can't add all result cells I'd like to see to the Scenario Manager (most probably due to the limit of variable cells) and by this can't list the calculation results completely into the Scenario Report.

Would be very kind if somebody has a hint how I could manage to list all results for each scenario.

Thank you.

[JohnTopley]

Please yellow banner on how to post a sample workbook.

[tony365]

Please yellow banner on how to post a sample workbook.

Thanks for the hint. Have now uploaded an example what hopefully describes what I'm trying to do here. Any idea?

[JohnTopley]

In B29

="Test Procedure " &D29

in B30

=IFERROR(INDEX(tab_PROCEDURES[[Idle 1]:[No. of Cycles]],MATCH($B$29,tab_PROCEDURES[Test Procedure],0),MATCH($A30,tab_PROCEDURES[[#Headers],[Idle 1]:[No. of Cycles]],0)),"")

copy down to B38

D29 is Data Validation list

[tony365]

Hi John,

thanks for your swift reply. Maybe I did not understand your proposal correctly, but have the feeling that the description of the problem I gave was not entirely clear. It seems all scenarios have been deleted by you. My intention was to use scenarios as the tool to link all individual calculation results (cells B42 to B141) for all combinations (4 different engines, 10 test programmes) into one big data "container" (which I was only able to manage for cells B42 to B73, so far as you can see in the scenario report - due to the limits of the scenario manager). I'd like to use this one big spreadsheet as source of data for any other post-processing of the results. In case I need to update engine and / or test program data I would only need to update this, re-run the scenarios, rename the report - taht's it. ...at least my idea. Do you think that's possible?

Cheers

In B29

="Test Procedure " &D29

in B30

=IFERROR(INDEX(tab_PROCEDURES[[Idle 1]:[No. of Cycles]],MATCH($B$29,tab_PROCEDURES[Test Procedure],0),MATCH($A30,tab_PROCEDURES[[#Headers],[Idle 1]:[No. of Cycles]],0)),"")

copy down to B38

D29 is Data Validation list

[JohnTopley]

If I deleted anything, it was out of ignorance (re Scenario Manager) so my apologies. I only changed the VLOOKUP (b30:B38) to make it more generic i.e removed the "Hard-coded" column references so that should have had no impact of the Scenarios.

Soo now you need explain how the calculations will work "outside" of Scenario Manager as this function is the limiting factor.

[tony365]

HI John,

thanks again. So, there seem to be no smart workaround (which I hoped would exist). Let's see if I'll find another way wo Scenario-Manager...

Cheers!