SOLVED

Multiple Values in Scenario Manager

%3CLINGO-SUB%20id%3D%22lingo-sub-1037290%22%20slang%3D%22en-US%22%3EMultiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1037290%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20Is%20there%20a%20way%20to%20put%20multiple%20values%20in%20a%20changing%20cell%20using%20scenario%20manager.%20I%20want%20to%20try%20different%20values%20in%20a%20couple%20of%20changing%20cells.%20I%20think%20that%20I%20can't%20use%20data%20table%20because%20the%20cells%20are%20not%20directly%20related%2C%20so%20no%20formulas%20involving%20both.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20basically%2C%20the%20values%20that%20I%20want%20to%20try%20in%20one%20changing%20cell%20are%3A%2030%25%2C%2035%25%2C%2040%25%2C%2045%25%2C%2050%25%3C%2FP%3E%3CP%3EAnd%20in%20the%20other%20one%20are%3A%204%2C5%2C6%2C7%2C8%2C9%2C10%2C11%2C12%2C13%2C14%2C15%2C16.%20This%20cell%20is%20related%20to%20a%20lot%20of%20cells%20across%20the%20worksheet%2C%20that's%20why%20I've%20chosen%20Scenario%20manager.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20do%20it%20manually%20value%20per%20value%20it%20would%20take%20me%20forever.%26nbsp%3B%20Is%20there%20a%20way%20to%20put%20multiple%20values%20in%20Scenario%20Manager%2C%20or%20what%20other%20alternatives%20can%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1037290%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039059%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039059%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469616%22%20target%3D%22_blank%22%3E%40Dromerolovo95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20does%20sound%20like%20DataTables%20are%20what%20you%20need.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20attached%20a%20couple%20of%20examples%20and%20an%20article%20I%20wrote%20on%20it%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159383iAD2D415A6FCD030C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039106%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039106%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20Wyn!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20there%20was%20a%20lack%20of%20explaining%20from%20my%20part%2C%20and%20I've%20already%20worked%20it%20out%2C%20but%20I%20think%20that%20there%20are%20better%20ways%20to%20do%20it.%20Anyway%2C%20I%20think%20that%20it's%20going%20to%20be%20good%20for%20future%20cases%20if%20we%20cover%20this%20up.%20This%20case%20is%20a%20little%20bit%20more%20complex%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20helping%20my%20brother%20with%20the%20projections%20of%20his%20business.%20so%20basically%20he%20runs%20some%20workshops.%20And%20the%20tricky%20part%20here%20is%20that%20the%20number%20of%20students%20triggers%20a%20lot%20of%20different%20things%20across%20the%20worksheet.%20So%20for%20example%2C%20if%20there%20are%2015%20students%20enrolled%2C%202%20teachers%20are%20needed%2C%20if%20there%20are%208%20students%20enrolled%2C%20only%201%20teacher%20is%20needed.%20If%20there%20are%205%20students%20enrolled%205%20tools%20are%20needed%2C%20with%2012%2C%208%20tools%20are%20needed%2C%20etc.%20So%20I%20need%20to%20do%20a%20scenario%20for%20all%20the%20possibilities%20of%20students%2C%204%2C5%2C6%2C7%2C8%2C9%2C%20etc.%20Because%20each%20number%20triggers%20plenty%20of%20different%20cells%20that%20affect%20the%20estimated%20cost%20of%20the%20workshop.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20percentage%20part%20is%20the%20scenarios%20of%20the%20utility%20so%2C%20how%20much%20would%20be%20the%20price%20of%20the%20workshop%20if%20we%20want%2050%25%2C%20or%2040%25%20percent%20of%20utility.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20end%2C%20the%20final%20calculations%20are%20fittable%20for%20the%20data%20table%2C%20but%20only%20after%20all%20the%20scenarios%20have%20been%20made.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20I%20made%20myself%20clear!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039115%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039115%22%20slang%3D%22en-US%22%3EI'm%20thinking%20you%20could%20utilise%20the%202nd%20approach%20I%20put%20in%20the%20original%20attached%20file.%20Where%20the%20DataTable%20is%20just%20used%20to%20trigger%20scenarios.%20You%20would%20have%20to%20layout%20those%20scenarios%20in%20a%20few%20columns.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039119%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039119%22%20slang%3D%22en-US%22%3EGreat%2C%20I%20would%20take%20a%20look!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2607686%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2607686%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20apply%20your%20tables%20paper%20to%20a%20workbook%20which%20has%20multiple%20tabs%20and%20the%202%20variables%20I%20am%20changing%20are%20set%20in%20different%20tabs.%26nbsp%3B%20I%20am%20trying%20to%20change%20the%20interest%20rate%20and%20the%20number%20of%20months%20the%20loan%20amortizes%20to%20determine%20how%20much%20the%20total%20principal%20and%20interest%20would%20be%20over%205%20and%2010%20years.%20Each%20of%20the%20variables%20are%20in%20a%20different%20tab%20(they%20are%20highlighted%20in%20yellow).%26nbsp%3B%20I%20am%20not%20clear%20how%20to%20set%20up%20the%20table%20to%20change%20these%20variables%20and%20have%20the%20sums%20calculating%20in%20a%20third%20tab%20(DS%20comparison%20tab).%26nbsp%3B%20Can%20you%20guide%20me%20in%20the%20right%20direction%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2609040%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Values%20in%20Scenario%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2609040%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119826%22%20target%3D%22_blank%22%3E%40PamKurtzmanTX%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheck%20if%20the%20additional%20page%20in%20the%20attached%20file%20helps%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EP.S.%20best%20to%20start%20a%20new%20thread%20rather%20than%20tagging%20on%20to%20a%20solved%20one%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi! Is there a way to put multiple values in a changing cell using scenario manager. I want to try different values in a couple of changing cells. I think that I can't use data table because the cells are not directly related, so no formulas involving both. 

 

So basically, the values that I want to try in one changing cell are: 30%, 35%, 40%, 45%, 50%

And in the other one are: 4,5,6,7,8,9,10,11,12,13,14,15,16. This cell is related to a lot of cells across the worksheet, that's why I've chosen Scenario manager.

 

If I do it manually value per value it would take me forever.  Is there a way to put multiple values in Scenario Manager, or what other alternatives can work?

 

Thanks in advance!

 

 

 

 

5 Replies

Hi @Dromerolovo95 

 

It does sound like DataTables are what you need. 

 

I've attached a couple of examples and an article I wrote on it

clipboard_image_2.png

HI Wyn! @Wyn Hopkins 

 

Maybe there was a lack of explaining from my part, and I've already worked it out, but I think that there are better ways to do it. Anyway, I think that it's going to be good for future cases if we cover this up. This case is a little bit more complex

 

I'm helping my brother with the projections of his business. so basically he runs some workshops. And the tricky part here is that the number of students triggers a lot of different things across the worksheet. So for example, if there are 15 students enrolled, 2 teachers are needed, if there are 8 students enrolled, only 1 teacher is needed. If there are 5 students enrolled 5 tools are needed, with 12, 8 tools are needed, etc. So I need to do a scenario for all the possibilities of students, 4,5,6,7,8,9, etc. Because each number triggers plenty of different cells that affect the estimated cost of the workshop.

 

The percentage part is the scenarios of the utility so, how much would be the price of the workshop if we want 50%, or 40% percent of utility. 

 

So in the end, the final calculations are fittable for the data table, but only after all the scenarios have been made. 

 

I hope that I made myself clear!

 

 

best response confirmed by Dromerolovo95 (Occasional Contributor)
Solution
I'm thinking you could utilise the 2nd approach I put in the original attached file. Where the DataTable is just used to trigger scenarios. You would have to layout those scenarios in a few columns.

@Wyn Hopkins 

I am trying to apply your tables paper to a workbook which has multiple tabs and the 2 variables I am changing are set in different tabs.  I am trying to change the interest rate and the number of months the loan amortizes to determine how much the total principal and interest would be over 5 and 10 years. Each of the variables are in a different tab (they are highlighted in yellow).  I am not clear how to set up the table to change these variables and have the sums calculating in a third tab (DS comparison tab).  Can you guide me in the right direction? 

 Hi @PamKurtzmanTX 

 

Check if the additional page in the attached file helps

 

 

P.S. best to start a new thread rather than tagging on to a solved one