+ Reply to Thread
Results 1 to 10 of 10

Why is there a variance here for total year?

  1. #1
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Why is there a variance here for total year?

    Hi!

    I have a simple calc that looks at 12 months and then has a total column.

    Row 1: employees A salary by month (static)
    Row 2: employees B salary by month (static)
    Row 3: % that is employee A of the total of the first two rows (% that is employee A of total) (calculated)
    Row 4: Total benefits (static)
    Row 5: How much of total benefits should go to employee A based on row 3 (employee A % of total salary) (calculated)

    But the problem is that the sum of Jan - Dec for the last row does not equal the same as if I do the formula on the total year.

    See attached file.

    Is this a math problem? An excel problem? Shouldn't they exactly match?
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    ĶSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Why is there a variance here for total year?

    Your values are NOT rounded to the nearest cent. That, plus floating point arithmetic are the root causes.

    https://support.microsoft.com/en-gb/...sults-in-excel

    See sheet, where I have rounded your data
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Why is there a variance here for total year?

    Katie, you are expecting B4*B5+C4*C5+...+M4*M5 = N4*N5.

    I'm not sure, but I believe that the logic here is flawed.

    Glenn, I do not believe that rounding or floating point errors are the problem here. If you change N6 to =SUM(B6:M6), of course O6 will show as 0 since O6 =N6-SUM(B6:M6).

  4. #4
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Why is there a variance here for total year?

    Thanks for the responses everyone! I agree that rounding doesn't seem to fix the variance because cell N6 was changed from the formula I had, if you put back my original N6, the variance is still there. It still may be Floating-point arithmetic error somehow but I don't understand it. If it is some problem with storing the binary amounts isn't 146 a rather large amount of variance?

    If it's my logic, how is it flawed? Why doesn't B4*B5+C4*C5+...+M4*M5 = N4*N5 ?

    Thanks!
    Last edited by Katie620; 11-27-2017 at 03:14 PM.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Why is there a variance here for total year?

    I believe that the only way that B4*B5+C4*C5+...+M4*M5 would be equal to N4*N5 would be if all of the percentages (in row 4) were equal every month.

    The "TOTAL 2017" column is making the assumption that the "% Employee A" was the value of N4 (~71.77%) for the entire year.

    The actual total allocated benefits to employee A would be the sum of each month's allocated benefits to employee A.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,018

    Re: Why is there a variance here for total year?

    Try looking at this recent thread ... especially MrShorty's post #7 comments regarding the limitations of the ROUND function. It surprised me. A remedy is there, too.

    https://www.excelforum.com/excel-gen...ml#post4784715
    Last edited by FlameRetired; 11-27-2017 at 03:54 PM.
    Dave

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Why is there a variance here for total year?

    Take this shortened version (attached) as an example.

    Here, you can see that the %'s are the same for every month.

    In this case,
    90%(10)+90%(20)=90%(30)

    If we change C3 to 18, for example to make the %'s different, we then have
    90%(10)+50%(20)<>59%(30)

    The correct formula for D6 (in the attached workbook) would be:
    =SUM(B6:C6)
    or =B4*B5+C4*C5 which can be scaled easily using =SUMPRODUCT(B4:C4,B5:C5)
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Why is there a variance here for total year?

    Wow thanks for that. It seems unlikely that the floating point problem would be an issue here with such small (and round) numbers right?

    So this seems like solid proof that doing what I was trying to do in the total column will not produce the same exact result as summing each individual column.

    Does anyone know why, mathematically, this doesn't work?

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Why is there a variance here for total year?

    I went to school for mathematics but proofs were never my strong suit. I hate to admit it but I was terrible at them.

    I will continue to follow this thread looking forward to any responses that can mathematically prove me right or wrong.

  10. #10
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Why is there a variance here for total year?

    Haha thank you! I cross posted on a math forum using your example, I will update here if I get any results.

+ 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: 6
    Last Post: 04-09-2021, 01:59 AM
  2. [SOLVED] countifs to find the total count between this year and last year for series of data
    By maher2014 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2016, 02:39 AM
  3. [SOLVED] Need formula to calculate YTD variance of current and prior year
    By mvparker79 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-25-2014, 03:54 PM
  4. [SOLVED] VBA to Pivot by Month and Cumulative Year to Date, then include variance field
    By mike_vr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2013, 06:00 AM
  5. How to add Month-on-Month and Year-on-Year %Variance into a pivot table
    By emeritus1812 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-09-2013, 02:37 AM
  6. Replies: 5
    Last Post: 11-17-2010, 09:03 PM
  7. [SOLVED] Variance Total for less than complete year?
    By Enzo in forum Excel General
    Replies: 1
    Last Post: 09-01-2005, 05:05 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