+ Reply to Thread
Results 1 to 11 of 11

How do I obtain a variance percentage? Please check my calculation/formula

  1. #1
    Registered User
    Join Date
    04-20-2011
    Location
    Granite Bay, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    How do I obtain a variance percentage? Please check my calculation/formula

    My calculation/formulas are: Column D is C-B and Column E =D2/(B2+C2)

    Worksheet.jpg

    My boss said that there can't be a negative...I explained that the negative is really a positive reflecting improvement in the 3rd quarter.

    Please explain my error. I am not strong in math and according to my boss my calculations are incorrect.

    Thank you,
    Dixie
    Last edited by Dixiewest; 10-12-2013 at 11:06 AM. Reason: didn't show up like I intended

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    Hi and welcoem to the forum

    You can have a negative %, but I believe the logic in your formula is wrong. I think the % calc should be this in E. This will give you the variance of Q3 to Q2...
    =(C2-B2)/B2
    A
    B
    C
    D
    E
    1
    Q2
    Q3
    Diff
    %
    2
    Company a
    401
    425
    24
    5.99%
    3
    Company b
    1
    2
    1
    100.00%
    4
    Company C
    801
    725
    -76
    -9.49%
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    If I understand your question correctly, you are comparing Quarter 3 against Quarter 2 as either a gain in revenue or a drop in revenue as a percentage of Quarter 2.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    formatted as %

    Company C's revenue dropped by 76 therefore as compared to Quarter 2 the result is a negative. These would be my results

    **** 2nd Qtr 3rd Qtr Difference %
    a 401 425 24 5.99%
    b 1 2 1 100.00%
    c 801 725 -76 -9.49%
    Last edited by newdoverman; 10-12-2013 at 11:21 AM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    To FDibbins: How did you insert the chart in your response?

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    The OP never did not mention that this has anything to do with the revenue. There could be a reduction of some sort in which case the negatives are good.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    04-20-2011
    Location
    Granite Bay, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    Thank you!!! I see my error.

  7. #7
    Registered User
    Join Date
    04-20-2011
    Location
    Granite Bay, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    My brain is not wired for math...thank you so much!

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    @ AlKey, I never gave any indication of negative being good or bad, just that negatives were possible and that that was the variance

    @ nedoverman, take a look at this thread for that...
    http://www.excelforum.com/showthread...=1#post3387431

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    Quote Originally Posted by FDibbins View Post
    @ AlKey, I never gave any indication of negative being good or bad, just that negatives were possible and that that was the variance

    @ nedoverman, take a look at this thread for that...
    http://www.excelforum.com/excel-form...=1#post3387431
    @DB
    LOL! it's not you

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How do I obtain a variance percentage? Please check my calculation/formula

    Quote Originally Posted by newdoverman View Post
    To FDibbins: How did you insert the chart in your response?
    See the add-in at https://app.box.com/s/soezox25h3w0q5s4rcyl
    Entia non sunt multiplicanda sine necessitate

+ 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: 1
    Last Post: 04-16-2013, 03:58 PM
  2. new formula for percentage calculation
    By talonco in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2012, 05:04 PM
  3. Conditional Formatting using Percentage Variance
    By Skarnald in forum Excel General
    Replies: 3
    Last Post: 02-09-2012, 11:03 PM
  4. How to obtain a value above certain percentage?
    By trackfiend in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-02-2008, 02:22 PM
  5. calculate cells between two values, and obtain the percentage
    By caldera55 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-15-2008, 06:23 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