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

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????

2. Originally Posted by katie620
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...)

3. 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...

4. Originally Posted by Denis
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

Originally Posted by Denis
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. Originally Posted by katie620
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 $a_1$ $a_2$ $a_1 + a_2$ Employee B Salary $b_1$ $b_2$ $b_1 + b_2$ % of above that is Employee A Salary $\dfrac{a_1}{a_1+b_1}$ $\dfrac{a_2}{a_2+b_2}$ $\dfrac{a_1+a_2}{a_1+a_2+b_1+b_2}$ Total Benefits (unrelated static number) $x_1$ $x_2$ $x_1+x_2$ Employee A Allocated Benefits $\dfrac{a_1}{a_1+b_1}\times x_1$ $\dfrac{a_2}{a_2+b_2}\times x_2$ $\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.

6. Thanks for stepping in Doc:
that problem gave me a headache

7. Originally Posted by Dr.Peterson
Let's put variables in there:

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•