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.