Hi FS,

It depends on the percentage of which number you are calculating for.

Your formula is (100000 - 110000) / 110000 

Which is equal to 0.0909 so 9% is correct when rounded.

Regards

Phil

Hello,

You must divide with A2.

=(B2-A2)/A2

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!

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

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.

Hi Anders,

Thanks for your reply.

Could you give me an example of an if statement as the previous years amounts could be increases or decreases.

Hello,

Not sure what you are looking for, as for when checking your sample file you get correct result with your current formula. Or have I misunderstood something?

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

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.