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.
bartvana Followed this tread the last couple of days. Was wondering why you need such a complicated approach at all. A simple structured table with the calculations, linked to the variables for each scenario (your orange table) achieved all in one go. And it expands automatically. Add an extra year end the graph will expand with it. See attached.
But perhaps I'm missing the point all together.
Riny_van_Eekelen wrote:bartvana Followed this tread the last couple of days. Was wondering why you need such a complicated approach at all. A simple structured table with the calculations, linked to the variables for each scenario (your orange table) achieved all in one go. And it expands automatically. Add an extra year end the graph will expand with it. See attached.
But perhaps I'm missing the point all together.
Thanks for answering!
My example is indeed extremely simplified in an attempt to make my point clear, and in its simplified form, it does allow for a solution like yours (which is also interesting of course).
But imagine you don't have one column with a formula, but 7, each with a different formula, and you want these 7 columns to adapt to 5 scenarios. You'd have 7*5 = 35 columns in the table. Plus, if you'd want to adapt one formula, you'd have to repeat this adaptation 7 times, which is monkey work I always try to avoid.