Odd outcome with Formula

If you increase your decimal for Total Hours worked, you will see the number is actually 37.21667
37.21667 * 10 is very close to 372.17

Originally Posted by SpartanCollege

Thank you but still confused. any number times 10 ends in 0. SO why is the formula giving me an odd number when total hours are multiplied by 10? See what I am saying?

Yes. But you do not seem to see what everyone else is saying.

The value in H33 only appears to be 37.22 because you format it to show 2 decimal places. The actual value is about 37.2166666666667.

Ostensibly (over-simplified), 37.2166666666667 times 10 is about 372.166666666667, which Excel rounds to 372.17 because you format it to display 2 decimal places. "See what I am saying"?

If you want H33 to actually be 37.22, the formula in H33 should be: =ROUND(SUM(H13:H32),2).

For technical reasons, the formula in H34 should also be: =ROUND(H33*H34,2).

Originally Posted by joeu2004

Yes. But you do not seem to see what everyone else is saying. The value in H33 only appears to be 37.22 because you format it to show 2 decimal places. The actual value is about 37.2166666666667.

What has not yet been explained yet is: why is H33 about 37.2166666666667 instead of 37.21 = 12 + 3 + 9 + 4 + 3.63 + 5.58, in the first place?

That is more difficult to understand.

As you seem to know, Excel time is stored as a decimal fraction. That is why you multiply the time difference by 24 in H13, for example.

But most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel uses to represent numbers and perform arithmetic.

So the actual values in H13:H18 are:

Please Login or Register  to view this content.

(I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats.)

Note that 3, 9, 3.63 and 5.58 are not represented exactly.

You might think that 3 and 9 should be, because 3/24 and 9/24 can be represented exactly as 0.125 and 0.375. But the problem is: the decimal representation of 8:00, 10:00, 13:00 and 17:00 cannot be represented exactly.

(FYI, 12 and 4 are exact only by coincidence.)

PS.... The formulas in H13 should be of the form: =IF((F13-D13)>0,ROUND((F13-D13)*24,2),""). Then the sum in H33 will appear to be 37.21, as you might expect.

[EDIT] It would be prudent to also write =IF(ROUND((F13-D13)*24,2)>0,....).