Another rounding issue

Hi Folks!

Here's another rounding issue I just discovered:

A2 = 9:00 PM (not calculated, manually entered)
B2 = 10:00 PM (not calculated, manually entered)
C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
D2 formula: =IF(C2<1/24,1/24-C2,0)

Without preformatting cell D2 it defaults to GENERAL and unexpectedly
returns 3.46945E-17 which is the value_if_true argument of the IF function.

When formatted as h:mm it returns 0:00.

If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!

Here's how it breaks down:

=IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)

=(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
to 15 decimal places returns 0.999999999999999. So, if that formula was
tested to return = 1 it would also fail.

But........ change:

A2 = 11:00 AM
B2 = 12:00 PM

(not drag filled either!) D2 returns 0 as expected and when tested for = 0
passes.

Hmmmm!

Just be aware!

Biff

Biff wrote:
> Hi Folks!
>
> Here's another rounding issue I just discovered:
>
> A2 = 9:00 PM (not calculated, manually entered)
> B2 = 10:00 PM (not calculated, manually entered)
> C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
> D2 formula: =IF(C2<1/24,1/24-C2,0)
>
> Without preformatting cell D2 it defaults to GENERAL and unexpectedly
> returns 3.46945E-17 which is the value_if_true argument of the IF function.
>
> When formatted as h:mm it returns 0:00.
>
> If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!
>
> Here's how it breaks down:
>
> =IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)
>
> =(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
> to 15 decimal places returns 0.999999999999999. So, if that formula was
> tested to return = 1 it would also fail.
>
> But........ change:
>
> A2 = 11:00 AM
> B2 = 12:00 PM
>
> (not drag filled either!) D2 returns 0 as expected and when tested for = 0
> passes.
>
> Hmmmm!
>
> Just be aware!
>
> Biff
>
>
>
>

Not an "anomaly" or "bug" though. Rather a mental (cognitive) trap we
easily fall into with relational tests (comparisons) regarding
fractions. ROUND(X,n) < ROUND(Y,n) is the remedy with added costs of
making two function calls.


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

The reason that you need to ROUND for this operation (as suggested by
Aladin) is that computers do not have infinite precision, and some
fractions cannot be exactly represented without it.

For example, consider a hypothetical computer that carries only 4
significant figures. Then 4/3 would evaluate to 1.333 and 1/3 would
evaluate to 0.3333. Because of finite precision, 4/3-1 would then
evaluate to 0.333 which is not exactly the same as what 1/3 evaluates to.

1/24 = 1/8*1/3, so you should expect finite precision effects. The same
thing can happen where you might not expect it since computers do math
in binary rather than decimal, so numbers involving 1/5 (and hence 1/10)
will be non-terminating binary fractions (that must be approximated)
even though they are terminating decimal fractions.

If you want to learn more about this, there are several Knowledge Base
articles on the subject

http://support.microsoft.com/kb/48606
Comparison of Values Does Not Return Correct Result

http://support.microsoft.com/kb/42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors

http://support.microsoft.com/kb/165373
OFF97: Rounding Errors in Visual Basic For Applications

http://support.microsoft.com/kb/69333
How To Work Around Floating-Point Accuracy/Comparison Problems

http://support.microsoft.com/kb/214118
How to correct rounding errors in floating-point arithmetic

http://support.microsoft.com/kb/78113
Floating-point arithmetic may give inaccurate results in Excel

Jerry

Biff wrote:

> Hi Folks!
>
> Here's another rounding issue I just discovered:
>
> A2 = 9:00 PM (not calculated, manually entered)
> B2 = 10:00 PM (not calculated, manually entered)
> C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
> D2 formula: =IF(C2<1/24,1/24-C2,0)
>
> Without preformatting cell D2 it defaults to GENERAL and unexpectedly
> returns 3.46945E-17 which is the value_if_true argument of the IF function.
>
> When formatted as h:mm it returns 0:00.
>
> If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!
>
> Here's how it breaks down:
>
> =IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)
>
> =(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
> to 15 decimal places returns 0.999999999999999. So, if that formula was
> tested to return = 1 it would also fail.
>
> But........ change:
>
> A2 = 11:00 AM
> B2 = 12:00 PM
>
> (not drag filled either!) D2 returns 0 as expected and when tested for = 0
> passes.
>
> Hmmmm!
>
> Just be aware!
>
> Biff
>
>
>
>
>