Log In
Register
Home
Active Member
September 13, 2019
Offline
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.
October 5, 2010
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
Hello,
You must divide with A2.
=(B2-A2)/A2
Active Member
September 13, 2019
Offline
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
Offline
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
Offline
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.
Active Member
September 13, 2019
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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?
July 16, 2010
Offline
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
Offline
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)
