# Formula to calculate Increase/Decrease

1. ## Formula to calculate Increase/Decrease

I have a spreadsheet where I need to calculate the increase in % one year over the other in column D for Eg if Profits are 95415 in Jan 2008 (Column B) and 80215 in Jan 2008 (Column C), then the % Increase will be (95415-80215)/80215 *100

I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/

Your assistance or anybody's on the forum will be greatly appreciated

Regards

Howard

http://www.mrexcel.com/forum/showthr...ghlight=howardneed  Register To Reply

2. You can't meaningfully compute a percentage increase from a negative or zero number to a positive number.  Register To Reply

3. ## Formula to calculate Increase/Decrease

Hi shg

1) If I have a loss in say Jan 2008 of say -95140 and in Jan 2007 I made a profit of 48150 then the % decrease will be -297.59%.(-95140+48150)/48150 *100

2) If I make a profit of 60000 in Jan 2008 and a profit of 45000 in Jan 2007 then the % increase will be 33.33% (60000-45000)/45000 * 100

3) If i make a profit of 60000 in Jan 2008 and a loss of 35000 in Jan 2007
then the % increase will be 271.43% (60000+35000)/35000 *100

I would like to incorporate this into one formula in column D, which will take all the scenarios above into account

Your assistance in this regard will be most appreciated

Howard  Register To Reply

4. Howard,

The formula is the same in all cases: = current / prior - 1, and gives the same results in your post. But consider this:

1. If the prior profit was zero, the formula blows up. (#DIV/0!)

2. If the prior profit was a penny, and the current profit is a dollar, touting that to analysts as a 1000% increase might not get a warm reception.

3: A positive change in profitability is always good. What about a negative change, e.g.:

Jan 2007 = -35K, Jan 2008 = +60K, change = -271% -- very good
Jan 2007 = +35K, Jan 2008 = -60K, change = -271% -- not so good

BTW, don't multiply the result by 100 to get percent; just format the result as %  Register To Reply

5. ## Formula to calculate Increase/Decrease

Hi shg

Thanks for the advice, much appreciated. It works well, except if the prev years profit is in a loss and the current year is in a profit. I have highlighted the incorrect percentage & have shown the amemded formula in D12:D16

Please find sample file showing the formula suggested by yourself and my amendment using your formula and amending using the if formula.

Regards

Howard  Register To Reply

6. Note how the second and third rows give the same result (I reversed the order of the first two columns to show them in ascending date order): ``Please Login or Register  to view this content.``
The formula is only meaningful if given the change and the current profit one can compute the prior profit.  Register To Reply

7. ## Re: Formula to calculate Increase/Decrease

But if you want to calculate the increase / decrease of ordinary minus / plus numbers the previous solutions do not seem to work. Viz.

(col A, col B, col C)
number, number, % increase / decrease
-4, 5, ?
-4, -5, ?
4, 5. ?
4, -5, ?

I think the function ABS might be relevant but can't think it through just yet.

Would be grateful for help  Register To Reply

8. ## Re: Formula to calculate Increase/Decrease

Crockhamtown,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.  Register To Reply