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 found an even better solution for displaying multiple scenarios simultaneously in one chart, without Power Query, based on these two articles: "https://www.dummies.com/software/microsoft-office/excel/build-drop-scenarios-financial-model/" and "https://www.dummies.com/software/microsoft-office/excel/conveying-message-charting-scenarios-financial-model/", apparently from the book "Financial Modeling in Excel For Dummies".
(I had to reverse engineer the second article, because it's incomplete and has an error in it, "Highlight cells A24:F2" should be "A24:F27").
The workbook attached contains a step-by-step explanation.
In short, it looks like this:
- The blue table is where the calculation is done (for one scenario at a time), fetching values from the orange table (C12 and C13), where the variables live.
- In B1, there's a "scenario switcher" (a simple data validation list), to choose the active scenario
- the values in C12 (variable a) are fetched from D12:H12 using an XLOOKUP with lookup value B1 (active scenario). Similar for C13 (variable b). This makes the worksheet and the calculated values respond to the drop-down in B1, and lets you change scenarios easily. But it still doesn't allow for simultaneous showing of all scenarios in one graph.
- the really smart part is the data table in B16:H21 (What-if Analysis > Data Table). It looks like a two-variable Data Table, but it's not. Instead, it's a one-variable Data Table with as "Column input cell" B1 (and no row input cell).
The data table then allows for the simultaneous showing of all scenarios in one graph.
What's great about this solution is that you can change the formula in the blue table, and all scenarios follow. And instead of copy-pasting the results for each scenario in a separate (static) table, the data table follows dynamically. And you can of course easily see, and display, the variable values for each scenario.
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)
)
)