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) ) )
Hi, sorry for the delay (I got sidetracked) and thanks for looking into my question!
Attached a workbook with a simple example explaining what I want.
Below a screenshot.
In the upper part, I have a simple table, where one column depends on the variable Var. I have two scenarios, and the graph changes when I display another scenario, but I can't show the two scenarios at once.
In the lower part, I copied the values from scenario 1 and 2 into a new table, and I can of course show these 2 results in one graph. But these are unlinked from my scenario.
So what I would like is to have the lower double bar chart, but still dynamically linked to my scenarios.
Well, your example does make clear what you want. Unfortunately, that was never my question.
The question was how to get there from whatever it is that produces those two scenarios in the first place. Where are your scenarios calculated in the first place.
THAT's what we need to see. You have some data, somewhere, that shows conditions X, Y and Z, modified by percentage P over time T. Scenario one uses certain assumptions for P and T; Scenario 2, other assumptions for P and T. What I was asking was to see that arrangement of the "raw data" that underlies the graph(s)
How do you get two "What-ifs" into one graph. To answer that involves some kind of modification to the way you've set up the scenario in the first place. So we need to go back to that request, to see how your actual data are arrayed.
Short of seeing your original sheet, I can only offer general ideas, like having a separate column for Scenario 2 that bases its numbers on the same conditions as in Scenario 1, but changes one (or more) of the variables. That seems, in some ways, so obvious that I have to assume your situation has something in it that makes it more complicated......
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.