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) ) )
I'm sure it's possible. Just have both sets of data included in the dataset that is graphed.
How to do THAT depends a lot on how your spreadsheet is organized. Could you post a copy, so long as it includes no confidential information?
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?
