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

bartvana 

 

No problem, thanks for thinking with me 🙂

 

It was a pleasure.

 

And now that you've gotten some solutions, beginning with your own via Power Query [which isn't available to my Mac world yet], and then solutions from true MVPs SergeiBaklan  and Riny_van_Eekelen -- solutions that go way beyond my abilities -- you can end 2020 on an Excel high!

 

It's fun to be part of this creative, and learning, community!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 26, 2020

mathetes , don't simplify, with your great experience your input is always valuable. Each of us has strong and wear points. That's advantage of this resource we may discuss concrete task from different points of view.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Dec 26, 2020
    Thank you, Sergei. I do believe I add useful input, especially around seeing the value of distinguishing between the input and output end of things, and the power of data tables at that front (or Input) end of a design.

    There are times though when I'm very aware of how much there is for me to learn. Fortunately, I enjoy learning.

Resources