Simple Formula with complex number of scenarios

[gmooney]

I have a very simple formula that needs to be expanded upon given the number of possible outcomes among the 3 cells that the formulas are looking at. The formulas that need adjusted are in cells C3 and C4. Those formulas must review the data in cells B2, B3 and B4.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CELL A1[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD="width: 164"]Sales Change vs Last Year[/TD]
[/TR]
[TR]
[TD="width: 164"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<tbody>[TR]
[TD="width: 159"]% of Sales Change vs Last Year[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD="class: xl65, width: 164"]$125,000,000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 164"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]East[/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"][TABLE="width: 164"]
<tbody>[TR]
[TD="class: xl65, width: 164"]$50,000,000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 164"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<tbody>[TR]
[TD="class: xl65, width: 159"]40%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]West[/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"][TABLE="width: 164"]
<tbody>[TR]
[TD="class: xl65, width: 164"]$75,000,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<tbody>[TR]
[TD="class: xl65, width: 159"]60%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Pretty straight formula!

Now ONLY change the values for East or West (cells B3 and B4) which will change the value of Total of $125M.

Depending on what values you gave East and West, the Total number is now either positive or negative.

With your new numbers in cells B3 and B4 the 40% and 60% should change and the Total must always equal either 100% or 0%

Continue to play with cells B3 and B4 and you will see that certain scenarios return the wrong results.

Here is the kicker...any Sales Change vs Last Year number for East or West that is negative in cell B3 or B4 must return a negative percentage in cells C3 or C4.

 

[cyrilbrd]

Not sure where is the catch...

	A	B	C
Sales Change vs Last Year	% of Sales Change vs Last Year
Total
East
#>West

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$73,777,800 [/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]($1,222,200)[/TD]
[TD="align: right"]-2%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$75,000,000 [/TD]
[TD="align: right"]102%[/TD]

</tbody>

gmooney​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUM(B3:B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUM(C3:C4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=B3/$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=B4/$B$2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gmooney]

PM
cyrilbrd,

I failed to mention that the results in cells C3 and C4 cannot be greater than 100% or -100%.

Not sure where is the catch...

	A	B	C
Sales Change vs Last Year	% of Sales Change vs Last Year
Total
East
#>West

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$73,777,800[/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]($1,222,200)[/TD]
[TD="align: right"]-2%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$75,000,000[/TD]
[TD="align: right"]102%[/TD]

</tbody>

gmooney​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=SUM(B3:B4)[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=SUM(C3:C4)[/TD]
[/TR]
[TR]
[TH]C3[/TH]
[TD="align: left"]=B3/$B$2[/TD]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"]=B4/$B$2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

so here you wish to see 98% in C2?

 

[gmooney]

so here you wish to see 98% in C2?

no in this example it should be -100 in C2 and +100 in C3. The East had 100% of the decline for the declining regions and the West had 100% of the increase in the increasing regions.