# Thread: Formula to Calculate % increase/(decrease

1. I have tried to use the following formula to calculate the % Increase(decrease-current year VS prior Year

=IF(OR(B9>0,B23<0),B9/B23-1,(-B9-B23)/B23)

The formula calculates the increase/decrease if the values in B9 and B23 are both positive or negative. However, if one value is positive & one is negative, the result is incorrect.

For Eg if B9 is (455389) and B23 is 131025, the result should be -447.55%

Your assistance will be most appreciated

2. No mattter what the sign, the results seem to me to be:

=B9/B23-1

which is essentially a simplification of:

=(B9-B23)/B23

The change divided by the original

Using either of these formulas with your examples gives the value you indicate as corrrect...

The problem seems to stem from the issue if the original (and we are talking about something like dollars where negative is bad) is negative that is is desired to have a negative increase since that brings the value closer to a positive. (Interpreted as a negative increase in "loss" or a positive increase in revenue)

Steve

3. Hi Steve

Thanks for the help. I have developed this formula which works perfectly =IF(AND(B9<0,B23>0),-B9/B23+1,B9/B23-1)

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•