# Why is there a variance here for total year?

1. ## 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?  Register To Reply

2. ## 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  Register To Reply

3. ## 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).  Register To Reply

4. ## 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!  Register To Reply

5. ## 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.  Register To Reply

6. ## 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  Register To Reply

7. ## 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)  Register To Reply

8. ## 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?  Register To Reply

9. ## 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.  Register To Reply

10. ## 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.  Register To Reply