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

katie620

New member
Joined
Nov 28, 2017
Messages
3
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 1Month 2Total
Employee A Salary99sum of the left two values (18)
Employee B Salary12sum of the left two values (3)
% of above that is Employee A Salary90% or 9/(9+1)81.82% or 9/(9+2)86% or 18/(18+3)
Total Benefits (unrelated static number)1020sum of left two values (30)
Employee A Allocated Benefits9.00 or 90%*1016.36 or 81.82% * 2025.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????
 

Attachments

  • 1.png
    1.png
    6.8 KB · Views: 0
  • 2.jpg
    2.jpg
    13.3 KB · Views: 2
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-ge...variance-here-for-total-year.html#post4793052


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!
 
Lets say
Month 1Month 2Total
Employee A Salary99sum of the left two values (18)
Employee B Salary12sum of the left two values (3)
% of above that is Employee A Salary90% or 9/(9+1)81.82% or 9/(9+2)86% or 18/(18+3)
Total Benefits (unrelated static number)1020sum of left two values (30)
Employee A Allocated Benefits9.00 or 90%*1016.36 or 81.82% * 2025.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 1Month 2Total
Employee A Salary\(\displaystyle a_1\)\(\displaystyle a_2\)\(\displaystyle a_1 + a_2\)
Employee B Salary\(\displaystyle b_1\)\(\displaystyle b_2\)\(\displaystyle b_1 + b_2\)
% of above that is Employee A Salary\(\displaystyle \dfrac{a_1}{a_1+b_1}\)\(\displaystyle \dfrac{a_2}{a_2+b_2}\)\(\displaystyle \dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}\)
Total Benefits (unrelated static number)\(\displaystyle x_1\)\(\displaystyle x_2\)\(\displaystyle x_1+x_2\)
Employee A Allocated Benefits\(\displaystyle \dfrac{a_1}{a_1+b_1}\times x_1\)\(\displaystyle \dfrac{a_2}{a_2+b_2}\times x_2\)\(\displaystyle \dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}\times (x_1+x_2)\)
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.
 
Let's put variables in there:

Month 1Month 2Total
Employee A Salary\(\displaystyle a_1\)\(\displaystyle a_2\)\(\displaystyle a_1 + a_2\)
Employee B Salary\(\displaystyle b_1\)\(\displaystyle b_2\)\(\displaystyle b_1 + b_2\)
% of above that is Employee A Salary\(\displaystyle \dfrac{a_1}{a_1+b_1}\)\(\displaystyle \dfrac{a_2}{a_2+b_2}\)\(\displaystyle \dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}\)
Total Benefits (unrelated static number)\(\displaystyle x_1\)\(\displaystyle x_2\)\(\displaystyle x_1+x_2\)
Employee A Allocated Benefits\(\displaystyle \dfrac{a_1}{a_1+b_1}\times x_1\)\(\displaystyle \dfrac{a_2}{a_2+b_2}\times x_2\)\(\displaystyle \dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}\times (x_1+x_2)\)
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 :)
 
Top