Baffeling IF statement
On May 10, 1:18*pm, "wabbleknee" wrote:
a1=9:00
[....]
c1= [...] =B1-A1
[....]
e1 formula =IF(c1=D1,"Yes","No")
[....]
b1=9:50, c1 showes 0:50
D1=0:50
e1 is now = NO *it should be YES
This is not as uncommon as you might think. For example,
=IF(10.1-10=0.1,TRUE) returns FALSE(!).
And sometimes =IF(A1=B1,TRUE) returns TRUE, but =IF(A1-B1=0,TRUE)
returns FALSE. Or =IF(A1+B1-C1=A1-C1+B1,TRUE) returns FALSE(!).
First, you need to understand that Excel time is stored as a fraction
of a day. So 1 hour is 1/24, 1 minute is 1/1440, and 1 second is
1/86400.
Second, most non-integer numbers cannot be represented exactly because
Excel (and most applications) usually uses binary floating-point to
represent numbers and perform arithmetic.
These little inaccuracies sometimes magnify noticable after performing
arithmetic.
One remedy is to always explicitly round any arithmetic that involes
non-integer numbers. Usually, we use ROUND (or some variant of it).
For time expressions, you might also use TEXT.
And it is best to round the cell that performs the computation. So
instead of fixing E1, fix C1 as follows:
=ROUND((B1-A1)*1440,0)/1440
=--TEXT(B1-A1,"hh:mm")
formatted as Time or a Custom format like [h]:mm.
The double-negative converts the text result to numeric.
|