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) ) )
mathetes No problem, thanks for thinking with me 🙂
Nesting formulas would help, but would make the calculations a lot harder to understand later on. (I like to add columns for intermediate steps, so I can follow what's being calculated more easily, and also detect and correct mistakes more easily afterwards.)
I'm thinking that instead of the Scenario Manager, something could be done with Power Queries instead, but haven't worked it out in detail yet.
Eg.
1) put the basic data in a table on a worksheet (no formulas)
2) put the variables and their different values for each scenario in a second table
3) create a Power Query that does the calculations. This query fetches the variable values from the second table.
4) duplicate the Power Query for each scenario, and have each duplicate fetch a different column (scenario) of variable values. The column number could in turn be a variable defined in the Power Query itself. This way, the Power Queries could be simply duplicated, and one could just change one value (column number of the scenario) to have it fetch the correct scenario.
I think something similar would work, but I'm still not very happy with it, in the sense that when one formula changes, you'd have to delete the duplicates and start duplicating all over again.
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!
- 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. 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.