Forum Discussion

bartvana's avatar
bartvana
Iron Contributor
Nov 20, 2020
Solved

Multiple scenarios in one graph (using Scenario Manager)

I have a simple question but cannot seem to find the answer (unless it is "no"). How can I show multiple scenarios (what-if, using the Scenario Manager) in one and the same graph?   I know how to ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 26, 2020

    bartvana 

    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)
 )
)
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 26, 2020

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. 

bartvana's avatar
bartvana
Iron Contributor
Dec 28, 2020

Riny_van_Eekelen 

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.