Percent Formula

[Alex Sanchez]

Hi there,

I would like to know how to create a formula that gives me % of increase or decrease. It is just a matter of dividing, but the problem I’m having is when there are negatives or zeros involved in either column.

Here is an example.

2011 2010 $ INC (DEC) % INC (DEC)
50,000 45,000 5,000 11.11% standard FORMULA (C2/B2)
0 -17,000 17,000 -100.00% should be positive 100%
-10,708 7,867 -18,575 -236.11%
200 -1,700 1,900 -111.76% should be positive 100% -10,000 5,000 -15,000 -300.00%
200 0 200 #DIV/0! should be positive 100%

Any help would be appreciated.

Thanks,
Alex

 

[crusader]

Hi there,

I would like to know how to create a formula that gives me % of increase or decrease. It is just a matter of dividing, but the problem I’m having is when there are negatives or zeros involved in either column.

Here is an example.

2011 2010 $ INC (DEC) % INC (DEC)
50,000 45,000 5,000 11.11% standard FORMULA (C2/B2)
0 -17,000 17,000 -100.00% should be positive 100%
-10,708 7,867 -18,575 -236.11%
200 -1,700 1,900 -111.76% should be positive 100% -10,000 5,000 -15,000 -300.00%
200 0 200 #DIV/0! should be positive 100%

Any help would be appreciated.

Thanks,
Alex

You can use the =ABS formula to change negative values to positive.

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">2011</td><td style="font-weight: bold;text-align: center;;">2010</td><td style="font-weight: bold;text-align: center;;">$ INC (DEC)</td><td style="font-weight: bold;text-align: center;;">% INC (DEC)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">50,000</td><td style="text-align: center;;">45,000</td><td style="text-align: center;;">5,000</td><td style="text-align: center;;">11.11%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">-17,000</td><td style="text-align: center;;">17,000</td><td style="text-align: center;;">100.00%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">-10,708</td><td style="text-align: center;;">7,867</td><td style="text-align: center;;">-18,575</td><td style="text-align: center;;">236.11%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">200</td><td style="text-align: center;;">-1,700</td><td style="text-align: center;;">1,900</td><td style="text-align: center;;">111.76%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">200</td><td style="text-align: center;;">0</td><td style="text-align: center;;">200</td><td style="text-align: center;;">100.00%</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">B2=0,1,ABS(<font color="Red">C2</font>)/ABS(<font color="Red">B2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[shg]

I think the average reader would expect a positive change to be an improvement, and a 100% increase to be a doubling, over the prior period.

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2011</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2010</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">$ INC (DEC)</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">% INC (DEC)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">50,000</TD><TD style="TEXT-ALIGN: center">45,000</TD><TD style="TEXT-ALIGN: center">5,000</TD><TD style="TEXT-ALIGN: center">11.11%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">-17,000</TD><TD style="TEXT-ALIGN: center">17,000</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">-10,708</TD><TD style="TEXT-ALIGN: center">7,867</TD><TD style="TEXT-ALIGN: center">-18,575</TD><TD style="TEXT-ALIGN: center">236.11%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">-1,700</TD><TD style="TEXT-ALIGN: center">1,900</TD><TD style="TEXT-ALIGN: center">111.76%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR></TBODY></TABLE>

 

[Alex Sanchez]

Thanks,
Alex