Active Member

September 13, 2019

I don't know why my formula isn't working.

I have created a simple formula to work out the percentage difference between 2 numbers.

=(B2-A2)/B2

When I enter the below it should show 10%, but it's showing 9%.

What do I need to do?

Enter Previous plan budget |
Enter new plan budget |
Increase/Decrease in plan budget |

100000 | 110000 | 9% |

Formula in spreadsheet attached.

VIP

Trusted Members

December 7, 2016

Active Member

September 13, 2019

I want to work out what percentage decrease or increase of the first number compared to the second number

Example: Previous year 110,000 current year 100000

If I do =(B2-A2)/ABS(A2) I get -9.09%

If I do =(B2-A2)/ABS(B2) I get -10.00% (correct)

But when I reverse the numbers to 100000 previous year, 110000 current year

If I do =(B2-A2)/ABS(B2) I get 9.09%

If I do =(B2-A2)/ABS(A2) I get 10.00% (correct)

How can I get a formula that shows percentage correctly for increase AND decrease.

Should there be an IF in there somewhere?

It's driving me bonkers!

October 5, 2010

Hi FS,

Let's use smaller numbers, all those 0000 are hurting my eyes 🙂

If we start with 10 and it increases to 11 then the change is +1. The % change is 1/10 = 10%

If we start with 11 and it decreases to 10 then the change is -1. The % change is -1/11 = -9.09%

As Anders said the formula is just

** =(B2-A2)/A2**

Cheers

Phil

VIP

Trusted Members

December 7, 2016

Hello,

Rule of thumb when calculating difference you should always subtract the lesser value from the greater. So it means you then need an IF statement if you have increased and/or decreased changes.

Syntax (1) for increased change: =(New value - Current value) / Current value

Syntax (2) for decreased change: =(Current value - New value) / Current value

————————

Example (1): Current = 110 | New = 100

Formula using syntax 2: =(110-100) / 110 which will give 9.09 % as result.

————————

Example (2): Current = 110 | New = 140

Formula using syntax 1: =(140-110) / 110 which will give 27.27 % as result.

VIP

Trusted Members

December 7, 2016

July 16, 2010

Hi FS,

I think you're all confused 🙂

If Previous year is 110,000 (in column A) and current year is 100,000 (in column B) then this is a -9% change (reduction) on the previous year. You're comparing to your starting point i.e. the previous year, so this should be your divisor, not the current year value of 100,000.

Taking your example above:

Previous year 110,000 current year 100,000

If I do =(B2-A2)/ABS(A2) I get -9.09% (correct)

If I do =(B2-A2)/ABS(B2) I get -10.00% (incorrect)

But when I reverse the numbers to 100000 previous year, 110000 current year

If I do =(B2-A2)/ABS(B2) I get 9.09% (incorrect)

If I do =(B2-A2)/ABS(A2) I get 10.00% (correct)

Note: you should remove the ABS function, just a reference to cell A2 is correct. Otherwise you will get errors if your previous period is a negative value.

Mynda

VIP

Trusted Members

December 7, 2016

Hello FS,

The calculation you use in your sample file is (removing the IFs) =B2/A2-1 which is just another method to calculate and is correct.

As Mynda and Philip points out your problem seems to be an misunderstanding of the change in percentage, as the number value of the change is the same. But as you can see in my attempt to explain the two calculation methods I know about, you always divide with the Old value, the value before the change, thus you get different percentage result as in your examples.

To my knowledge there are two methods to calculate percentage change.

1) (New value - Old value) / Old value

2) New value / Old value - 1

For a decreased change both methods results in a negative percentage value. If you don’t want to have this minus sign then you with method 1 just make sure that you subtract the lesser value from the greater, you do still divide with the Old value. How you want to present the percentage I don’t know, but it looks better (in my opinion) to show a positive number (no minus sign) but in text explain that it is a decrease.

If you want to have such result, then you need an IF statement, else if you are fine with having negative numbers (with a minus sign) then you are good to go.

1 Guest(s)