+ Reply to Thread
Results 1 to 8 of 8

Variance Percentage Formulas - Can't Fix!

  1. #1
    Registered User
    Join Date
    11-15-2020
    Location
    Essex
    MS-Off Ver
    2020
    Posts
    4

    Variance Percentage Formulas - Can't Fix!

    Hi All,

    Hoping someone could help me, would really appreciate it.

    I am an accountant and often have two variance columns when comparing against Actual results to Budget - One for absolute variances between the actual and budget and one for the percentage variance against the actual and budget.

    The issue I have is, if I start the variance percentage formula with '=+' if the budget number is a negative and the actual number is a positive, the percentage will be a minus percentage instead of a plus and if both numbers are minus, e.g. actual is -1 and budget is -5 it will show a negative percentage instead of a positive and vice versa. To fix this, I've always had to keep changing the start of the formula to say either '=-' or '=+' depending on what the numbers are. Is there a way to fix this?

    Furthermore, if I'm comparing an actual number of say 50 and a budget number of 0 the percentage will show the '#DIV/0!' sign instead of +100% and if I'm comparing an actual number of say -50 and budget number of 0 it will show the '#DIV/0!' instead of -100% so I always have to manually type in 100% or -100%.

    If anyone could fix these 2 issues it would be an absolute life saver!!

    Hope I explained this well enough and many thanks in advance!

    Chris

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Variance Percentage Formulas - Can't Fix!

    Second problem:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-15-2020
    Location
    Essex
    MS-Off Ver
    2020
    Posts
    4

    Re: Variance Percentage Formulas - Can't Fix!

    Thank you so much! That's perfect for 2nd problem, works great!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Variance Percentage Formulas - Can't Fix!

    You're welcome. Thanks for the rep.

    I kinda think you could use the SIGN function to address the first problem, but I have to be honest and say I don't really understand your logic.

  5. #5
    Registered User
    Join Date
    11-15-2020
    Location
    Essex
    MS-Off Ver
    2020
    Posts
    4

    Re: Variance Percentage Formulas - Can't Fix!

    No problem.

    I've attached an excel file with 4 different examples. Two of the examples work with the formula starting with '=+' and two don't. I would have the same issue if I started it with '=-' where two of the examples would be correct and two of them wouldn't be.

    Hope this explains better?

    Thanks,

    Chris
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-15-2020
    Location
    Essex
    MS-Off Ver
    2020
    Posts
    4

    Re: Variance Percentage Formulas - Can't Fix!

    I think I found a way to fix it! By using the formula '=F8/ABS(E8)'. Is there a way I can combine this formula and your formula together?

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Variance Percentage Formulas - Can't Fix!

    OK, it's taken me a while to get round to looking at your file, but I think this addresses the problem:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This reverses the sign in the examples marked as incorrect, and leaves them alone in the examples marked as correct.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Variance Percentage Formulas - Can't Fix!

    Is this resolved now?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Actual vs. Budget Variance Percentage
    By Midnightstevens01 in forum Excel General
    Replies: 3
    Last Post: 02-19-2019, 08:30 AM
  2. Excel IF statement based on percentage variance
    By avs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2015, 01:47 AM
  3. Variance or Percentage Change?
    By HangMan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2015, 01:30 PM
  4. Formula for Convert Variance to a Percentage Using SUMPRODUCT
    By HangMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2015, 03:06 PM
  5. Convert Variance to a Percentage Using SUMPRODUCT
    By HangMan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-01-2015, 02:33 PM
  6. Percentage Variance Graphs
    By Bobo6782 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-04-2014, 07:00 AM
  7. Conditional Formatting using Percentage Variance
    By Skarnald in forum Excel General
    Replies: 3
    Last Post: 02-09-2012, 11:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1