How to calculate cumulative percentage?

[boazstuff]

<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="8" bgcolor="#0c266b">
<table width="100%" align="center" border="0"><tbody><tr><td align="left">Microsoft Excel - Book1</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 12.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="8" bgcolor="#d4d0c8"><table valign="MIDDLE" width="100%" align="center" border="0"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td valign="center" align="right"><form name="formCb755237"><input *******="window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);" value="Copy Formula" name="btCb873980" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="8" bgcolor="white"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name="sltNb935705"><option value="???" selected="selected">B5</option></select></td><td width="3%" align="right" bgcolor="#d4d0c8">=</td><td align="left" bgcolor="white"><input size="80" value="Jan" name="txbFb426622"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" width="2%" align="middle">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>E</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>F</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>G</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>H</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>5</center></td><td style="border: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Jan</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Feb</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Mar</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Apr</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">May</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Jun</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Total</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">-3%</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">-2%</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">5%</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">8%</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">5%</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">5%</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">???</td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="8"><table valign="TOP" width="100%" align="left"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet1</td><td>
</td></tr></tbody></table></td></tr></tbody></table>

I have this example above which i want to know what is the total % change from Jan to Jun. In the total column, the result i got is 19% using formula
=1*(1+B6)*(1+C6)*(1+D6)*(1+E6)*(1+F6)*(1+G6)-1.

Is there any other more simplified or better formula that can be used actually?

Thanks in advance!

Regards,
Boaz
​

</center>

 

[Dave Patton]

The solution depends on if this is a single requirement or if you continue to extend the data.
If you extend the data, do you insert columns?

1. formula in say H4 =SUM($B3:G3)/SUM($B2:G2)-1

2. if July - Dec Columns are hidden
formula in N4 =SUM(B3:M3)/SUM(B2:M2)-1


3. If you insert Columns for new months such as July
Use a Dynamic formula

O1 formula say =COUNTA(A2:M2)

H8 could have a formula like

=SUM(B7:INDEX(7:7,1,$O$1))/SUM(B6:INDEX(6:6,1,$O$1))-1

 

[boazstuff]

Hi WsJackman,

Your solution is PERFECT! It simply worked. Thanks mate.

Hi all,
Thanks for your wonderful suggestion and assistance too!

 

[Dave Patton]

Does the arithmetic logic work?

 

[braindiesel]

Regarding the Product formula, I may be wrong, but I believe that will only work if each month is the same weighting, for instance the forecast in every month is 100.

I don't think that will work if January had a forecast of 50 and February is 200
(My example being that if I achieve 110% of Jan's forecast = 55, and 90% of Feb's (180), the balance is 94%, NOT 100%)

 

[wsjackman]

The product formula simplifies the equation that had been used.

I agree it is not perfect for all situations, such as the condition suggested by braindiesel.

 

[boazstuff]

Hi All,

The product formula will work for me as what is being tracked each month is the percentage of growth or deficit. The last column is merely tracking the cumulative or compounding effect. I'll just need to amend the range for the array formula every month which is enough for me.

Regards,
Boaz