+ Reply to Thread
Results 1 to 4 of 4

Actual vs. Budget Variance Percentage

  1. #1
    Registered User
    Join Date
    02-18-2019
    Location
    Boston, Massuchusetts
    MS-Off Ver
    2013
    Posts
    4

    Actual vs. Budget Variance Percentage

    Hello: I need some help with this if statement that would solve for all the combinations for actual vs. budget variance and variance percentages. For instance,

    Columns
    A B C D

    Actual Budget Variance $$ Variance %
    row:1 100 300 200 66.67%

    row: 2 300 200 -100 -50.00%

    row: 3 0 100 100 100.00%

    row: 4 100 0 -100 -100.00%

    row: 5 - - - -


    Here are my attempts:

    This formula is for the Variance % column D.
    =if(A1=0,"100.0",if(b1=0,"-100.0",if(A1<0,(B1-A1)/B1))

    I need the formula above to work with all these combinations above.

    Any insight would be great!

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,342

    Re: Actual vs. Budget Variance Percentage

    Welcome to the forum.

    I think you're over-complicating it a bit. Try this instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Logic:
    If both A1 and B1 are 0, return 0;
    if A1 is 0, return 1 (which is 100%);
    if A1 is not 0 and B1 is 0, return -1 (which is -100%);
    If neither A1 nor B1 are 0, divide B1-A1 by B1.

    Assuming that C1 is B1-A1, this would give the same result:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The first part of both formulae, which wasn't there in your original try, is needed because otherwise 0 and 0 would give 100% variance.

    Format column D as percentage with two decimal places to get the figures as per your example.

    Hope that helps.
    Regards,
    Aardigspook

    I've just moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    02-18-2019
    Location
    Boston, Massuchusetts
    MS-Off Ver
    2013
    Posts
    4

    Re: Actual vs. Budget Variance Percentage

    Oh thank you so much.

    Any advise on the (variance dollar) formula,

    Again, here is my attempt:

    =If(A1>B1,A1-B1,if(A1<B1,A1-B1)),

    Again, Thank you so much!

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,342

    Re: Actual vs. Budget Variance Percentage

    Your formula is essentially just doing 'A1-B1' as both IFs lead to the same.
    To get the results you have in your first post, just use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you want the signs reversed, use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In your PM*, you ask for -100 | 50 | -50. I'm confused as to why you want this, as the variance from -100 to 50 is 150, not -50. However, if you want it, this will do it:
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    * IMPORTANT Please take a moment to re-read the Forum Rules, specifically Rule 8: Do not private message forum moderators or members for Excel help. All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members. Thank you.

+ 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. Replies: 2
    Last Post: 03-15-2017, 03:14 AM
  2. Get variance between Forecast and Actual using Pivot Table
    By juan.doe in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-04-2016, 03:13 AM
  3. Need a formula to calcuate percentage difference between budget $ and actual $
    By meekinslfm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-06-2014, 09:54 AM
  4. Replies: 1
    Last Post: 04-16-2013, 03:58 PM
  5. Formula for budget variance
    By ordinateur in forum Excel General
    Replies: 2
    Last Post: 05-18-2009, 06:34 AM
  6. How To Calculate Variance B/W Actual and Forecast Data
    By Cherish2007 in forum Excel General
    Replies: 1
    Last Post: 03-28-2007, 05:01 PM
  7. Budget Variance funct??
    By doowop5000 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2007, 11:07 AM

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