Results 1 to 7 of 7

Thread: Why does this simple example produce variance? (employee salaries)

  1. #1

    Why does this simple example produce variance? (employee salaries)

    See the attached image files of an excel workbook. The second file shows the formulas in each cell and the first shows the results in each cell. Basically my question is, if I change cell C3 to anything but 1 (so that it matches B3), there will always be variance in cell E6. Why?

    Or to put it another way:

    Lets say
    Month 1 Month 2 Total
    Employee A Salary 9 9 sum of the left two values (18)
    Employee B Salary 1 2 sum of the left two values (3)
    % of above that is Employee A Salary 90% or 9/(9+1) 81.82% or 9/(9+2) 86% or 18/(18+3)
    Total Benefits (unrelated static number) 10 20 sum of left two values (30)
    Employee A Allocated Benefits 9.00 or 90%*10 16.36 or 81.82% * 20 25.71 or 86% * 30

    So if we go to the last row and add up Employee A + B individually we get 9 + 16.36 = 25.36.

    But if we look at the total column only on the last row, the result is 25.71.

    So why don't they equal each other mathematically????
    Attached Images Attached Images
    • File Type: png 1.png (6.8 KB, 0 views)
    • File Type: jpg 2.jpg (13.3 KB, 2 views)

  2. #2
    Elite Member
    Join Date
    Feb 2004
    Location
    Ottawa, Ontario
    Posts
    16,651
    Quote Originally Posted by katie620 View Post
    So if we go to the last row and add up Employee A + B individually we get 9 + 16.36 = 25.36.

    But if we look at the total column only on the last row, the result is 25.71.

    So why don't they equal each other mathematically????
    Seems to be some formula is used to obtain 25.71

    However, why do you not use "sum of left two values"?
    (as you do in all other cases...)
    I'm just an imagination of your figment !

  3. #3
    Elite Member
    Join Date
    Feb 2004
    Location
    Ottawa, Ontario
    Posts
    16,651
    Look at it this way:
    $21 is paid in salaries:
    A earns $18, 85.7%
    B earns $ 3, 14.3%

    $30 is paid in benefits:
    A gets 30*.857 = $25.71 ***** your mysterious amount!!
    B gets 30*.143 = $ 4.29

    Quite simple...you must have something
    not kosher in your Excel steps...
    I'm just an imagination of your figment !

  4. #4
    Quote Originally Posted by Denis View Post
    Seems to be some formula is used to obtain 25.71

    However, why do you not use "sum of left two values"?
    (as you do in all other cases...)
    This is a simple example created to isolate what I don't understand. I could just sum to the left but I want to understand why mathematically it doesn't produce the same result.

    Cross posted from here: https://www.excelforum.com/excel-gen...ml#post4793052


    Quote Originally Posted by Denis View Post
    Look at it this way:
    $21 is paid in salaries:
    A earns $18, 85.7%
    B earns $ 3, 14.3%

    $30 is paid in benefits:
    A gets 30*.857 = $25.71 ***** your mysterious amount!!
    B gets 30*.143 = $ 4.29

    Quite simple...you must have something
    not kosher in your Excel steps...
    I still don't get it. The way you broke it down is essentially just what column D (total column) is doing. The issue is when you subtract 25.71 - (16.36+9.00) = 0.35, where I would expect the result to be zero.

    If you go to the link above you can download the excel file in question (the one posted by 63falcondude)

    Thanks!

  5. #5
    Junior Member
    Join Date
    Nov 2017
    Location
    Rochester, NY
    Posts
    166
    Quote Originally Posted by katie620 View Post
    Lets say
    Month 1 Month 2 Total
    Employee A Salary 9 9 sum of the left two values (18)
    Employee B Salary 1 2 sum of the left two values (3)
    % of above that is Employee A Salary 90% or 9/(9+1) 81.82% or 9/(9+2) 86% or 18/(18+3)
    Total Benefits (unrelated static number) 10 20 sum of left two values (30)
    Employee A Allocated Benefits 9.00 or 90%*10 16.36 or 81.82% * 20 25.71 or 86% * 30

    So if we go to the last row and add up Employee A + B individually we get 9 + 16.36 = 25.36.

    But if we look at the total column only on the last row, the result is 25.71.

    So why don't they equal each other mathematically????
    Let's put variables in there:

    Month 1 Month 2 Total
    Employee A Salary [tex]a_1[/tex] [tex]a_2[/tex] [tex]a_1 + a_2[/tex]
    Employee B Salary [tex]b_1[/tex] [tex]b_2[/tex] [tex]b_1 + b_2[/tex]
    % of above that is Employee A Salary [tex]\dfrac{a_1}{a_1+b_1}[/tex] [tex]\dfrac{a_2}{a_2+b_2}[/tex] [tex]\dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}[/tex]
    Total Benefits (unrelated static number) [tex]x_1[/tex] [tex]x_2[/tex] [tex]x_1+x_2[/tex]
    Employee A Allocated Benefits [tex]\dfrac{a_1}{a_1+b_1}\times x_1[/tex] [tex]\dfrac{a_2}{a_2+b_2}\times x_2[/tex] [tex]\dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}\times (x_1+x_2)[/tex]
    You expect the last row to add up.

    But just look at the expressions. They don't!

    The reason is essentially the same as the reasons fractions don't add up as a/b + c/d = (a+c)/(b+d): addition doesn't "play well" with division. When you add things, their ratios change. You just have a wrong expectation.

  6. #6
    Elite Member
    Join Date
    Feb 2004
    Location
    Ottawa, Ontario
    Posts
    16,651
    Thanks for stepping in Doc:
    that problem gave me a headache
    I'm just an imagination of your figment !

  7. #7
    Quote Originally Posted by Dr.Peterson View Post
    Let's put variables in there:

    Month 1 Month 2 Total
    Employee A Salary [tex]a_1[/tex] [tex]a_2[/tex] [tex]a_1 + a_2[/tex]
    Employee B Salary [tex]b_1[/tex] [tex]b_2[/tex] [tex]b_1 + b_2[/tex]
    % of above that is Employee A Salary [tex]\dfrac{a_1}{a_1+b_1}[/tex] [tex]\dfrac{a_2}{a_2+b_2}[/tex] [tex]\dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}[/tex]
    Total Benefits (unrelated static number) [tex]x_1[/tex] [tex]x_2[/tex] [tex]x_1+x_2[/tex]
    Employee A Allocated Benefits [tex]\dfrac{a_1}{a_1+b_1}\times x_1[/tex] [tex]\dfrac{a_2}{a_2+b_2}\times x_2[/tex] [tex]\dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}\times (x_1+x_2)[/tex]
    You expect the last row to add up.

    But just look at the expressions. They don't!

    The reason is essentially the same as the reasons fractions don't add up as a/b + c/d = (a+c)/(b+d): addition doesn't "play well" with division. When you add things, their ratios change. You just have a wrong expectation.

    Thank you!!! And thank you Denis

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
  •