Forum Discussion
Multiple scenarios in one graph (using Scenario Manager)
One more scenario is with dynamic arrays
where
years: =TRANSPOSE(tData[[#All],[Year]]) scens: =TRANSPOSE(INDEX(tVar[#Headers],1,SEQUENCE(,COLUMNS(tVar)-1,2))) data: =TRANSPOSE(tData[Value])* TRANSPOSE(INDEX(tVar,1,SEQUENCE(,COLUMNS(tVar)-1,2))/ INDEX(tVar,2,SEQUENCE(,COLUMNS(tVar)-1,2) ) )
mathetes OK, I attached a new workbook which maybe makes it clearer? It's fictional again, but let's say I have 5 variables that all play a role in the end result of a complicated table.
I can change the values of the 5 variables with one click of the button (using scenarios) and get the new results and graph instantly. Which is great.
But I want to compare, in one glance, the result of 7 scenarios, all in one graph, without copying and pasting 7 times the original table and variables. Can that be done, and if so, how?
Is this any clearer?
Ah, it finally sank in that your question really had to do with using Excel's Scenario Manager Tool and graphing the results of multiple scenarios from that. I was reading your use of the word as more generic, using the word scenario to mean, just, different scenarios; no fancy tool.
And I'll acknowledge, I don't know of a way to do it using the scenario tool.
Depending on how you're actually using it, however, I don't even see an advantage to that tool. It would come in handy, I suppose, if the only way you're going to present the data is in print form.
Here I did what you don't want to do (copied and pasted the datatable three times, not seven), placed three sets of variables in one area, and the graph off to the side. With this, in a live presentation, you can change any one or more variables "real time" and immediately see the comparison and the effect of the change. To my way of thinking, that would be more powerful anyway.
I like the scenario manager for some purposes, and it may be that another Excel guru knows of a way to do exactly what you've asked. I see this as not only a way to accomplish the same goal, but maybe even a way that has benefits. It also didn't take much time, frankly.
I suspect as well that there may be a different way to arrange the basic table and the formulas contained therein, perhaps even eliminating the copy/paste routine. Need to give it some more thought.
mathetes I edited the title and original question to make it clearer.
Yes, your proposal works, but as you say, it's not exactly what I'm after. I think that what I asked for specifically cannot be done, but wanted to make sure.
The closest would probably be to add a set of columns (1 set for each scenario) in the original table, but 1) that would make the table extremely large horizontally and 2) if I'd want to change a formula in a column, I'd have to repeat that change manually for each scenario. Still not ideal.
just looking at it briefly (it IS Christmas, after all) I wonder if you could make Qantity1 another of the variables that changes (as it does), and then, given that all the rest is formulas, I wonder if those columns could be reduced (by nesting formulae) to two or three, maybe even one. You could then have the variables appear above the results, for fine-tuning whatever it is that the real-world data represents.
I do apologize for the amount of time we spent speaking (writing) past each other. You obviously were aware of what I was proposing; and no doubt couldn't understand why I kept asking such stupid questions.
Have a good Christmas day, and (hoping this for all of us) a better 2021!
mathetes No problem, thanks for thinking with me 🙂
Nesting formulas would help, but would make the calculations a lot harder to understand later on. (I like to add columns for intermediate steps, so I can follow what's being calculated more easily, and also detect and correct mistakes more easily afterwards.)
I'm thinking that instead of the Scenario Manager, something could be done with Power Queries instead, but haven't worked it out in detail yet.
Eg.
1) put the basic data in a table on a worksheet (no formulas)
2) put the variables and their different values for each scenario in a second table
3) create a Power Query that does the calculations. This query fetches the variable values from the second table.
4) duplicate the Power Query for each scenario, and have each duplicate fetch a different column (scenario) of variable values. The column number could in turn be a variable defined in the Power Query itself. This way, the Power Queries could be simply duplicated, and one could just change one value (column number of the scenario) to have it fetch the correct scenario.I think something similar would work, but I'm still not very happy with it, in the sense that when one formula changes, you'd have to delete the duplicates and start duplicating all over again.