• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Percentage difference question?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Percentage difference question?|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Percentage difference question?
sp_PrintTopic sp_TopicIcon
Percentage difference question?
Avatar
FS
Australia

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
September 13, 2019
sp_UserOfflineSmall Offline
1
September 13, 2019 - 12:06 pm
sp_Permalink sp_Print

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.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1516
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
September 13, 2019 - 2:23 pm
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
September 13, 2019 - 2:25 pm
sp_Permalink sp_Print

Hello,

You must divide with A2.

=(B2-A2)/A2

Avatar
FS
Australia

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
September 13, 2019
sp_UserOfflineSmall Offline
4
September 13, 2019 - 3:57 pm
sp_Permalink sp_Print sp_EditHistory

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!

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1516
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
September 13, 2019 - 5:35 pm
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
September 14, 2019 - 4:24 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
FS
Australia

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
September 13, 2019
sp_UserOfflineSmall Offline
7
September 16, 2019 - 9:36 am
sp_Permalink sp_Print

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.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
8
September 17, 2019 - 6:40 am
sp_Permalink sp_Print

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?

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
September 17, 2019 - 8:27 am
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
10
September 17, 2019 - 3:42 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Riny van Eekelen
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.