Excel returns incorrect answer

[Raeneuf]

I am having an issue with excel returning the incorrect answer when formulating time worked into hrs. worked. An example of the formula used is =SUM(((D16-C16)*24)+((G16-F16)*24))&"hrs". In this instance the times entered are 23:45 to 2:45(entered as 26:45) and 22:15 to 1:15(entered as 25:15). The answer returned is 5.999999999hrs instead of 6hrs.

Any help that can be given is greatly appreciated.

Thank you

[#2]

@Raeneuf 

You can round your result without loss of accuracy.

=FIXED(SUM(((ROUND(D16-C16,15))*24)+((ROUND(G16-F16,15))*24)),2)&" hrs"

You won't get more than 15 decimal places displayed anyway. Nevertheless, your result is still correct.

[#3 · Accepted solution]

@Raeneuf 

If you want to round to integer hours, enter:

=ROUND((D16-C16+G16-F16)*24, 0)&"hrs"

Note the many simplifications:

1. SUM is unnecessary in this context.

2. Multiply by 24 (convert to hours) only once, after all time arithmetic instead of piecewise.

3. Remove unnecessary parentheses to improve readability.  No need for subexpressions in this context.

4. No need to round subexpressions to 15 decimal places.  It is also wrong, in general -- although it makes no difference in this context.

[#4]

This formula does fix the issue, and please forgive my ignorance. But why do I need to use these functions when the answer to this function would be a whole number and does not require rounding? Is there something wrong with the previous formula?

[#5]

@Raeneuf  wrote to @dscheikey :  ``why do I need to use these functions [....] Is there something wrong with the previous formula?``

Not sure what "previous formula" refers to:  yours or mine.  But to answer your question:  no, there is no need for all those functions.

There is a lot of misinformation and misunderstanding on the internet regarding non-integer arithmetic in Excel (and most applications), including MSFT documentation.  Confusion between "15 decimal places" versus "15 significant digits".  Confusion about the precision of non-integer arithmetic, in general.  And confusion about the precision that Excel "stores" versus "formats", in particular.

The details might be TMI.  But LMK if you are interested.

Rounding time and time arithmetic to 15 decimal places is wrong, in general.  It can result in the wrong results.

If you are rounding to integer hours, or even decimal hours to 2 decimal places, you probably will not see a difference.  But to demonstrate, consider the following example:

C16: 23:45

D16: 25:45

E16: (D16-C16)*24

F16: ROUND(D16-C16,15)*24

Format E16 and F16 as Number with 14 decimal places (15 significant digits, the most that Excel formats).  E16 displays 2.00000000000000 as expected, whereas F16 displays 1.99999999999999.

(But don't be misled:  E16 is not exactly 2, even though E16=2 returns TRUE -- an Excel trick that is misleading.  To make that point, note that E16-2=0 returns FALSE.  The two expressions are equivalent mathematically.)

The point is:  D16-C16 formatted as Number with 16 decimal places is 0.0833333333333333.  So we lose precision when rounding to 15 decimal places.

In general, if we choose to round, we should round to the precision that we expect or require.  But when to round is a judgment call.  Often, it depends on the context.

In the case of time arithmetic (resulting in Excel time, not decimal time), we do not know what precision to expect.  On the contrary, usually we want to retain the full precision that Excel allows -- which is more than 15 significant digits, not to mention 15 decimal places.

I hope that helps.  If not, I can certainly provide more details.

[#6]

No, none of this was TMI. I definitely helped me understand more. Thank you so much for your time, it is much appreciated.

[#7]

@Raeneuf  wrote:  ``none of this was TMI``

Oh, that was not the TMI explanation.  (smile)  But I'm glad it helped.