Why 18:10 do not equal 18:10?

[Kaytee]

I can't understand something. Please help.

Two columns D and E with manually written time.
Column F contains manually written D+E
Column H contains =D+E (the sum of D and E counted by excel)
There is a condition in column G to decide whether F and H equals,
sometimes they do equal, sometime don't. Why??

(Example: D 13:10, E 5:00, F 18:10, H 18:10, but F and H do not equa
!!)
I have enclosed the excel document excel.xls

Thanks for any help

 

[Bob Phillips]

It seems to be due to the precision of the manually entered against the
calculated. What problem does it present you, as I would assume that you
would use the calculated values.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

In Excel time is stored as a fraction of day. 24h=1
Format your range D:H as number with 15 decimal places (it's maximal number
of decimals for numbers in Excel - all above this is rounded). First 2 times
will be
0.548611111111111
0.208333333333333
but your 2 sums are
0.756944444444445
0.756944444444444
As you see, sums aren't equal, because in second sum you are adding rounded
values.

 

[Jerry W. Lewis]

Since times are stored as fractions of days (as was already noticed), 1
minute is 1/1440 (1440 = 24*60) and half a minute is 1/2880 =
0.000347222222222222.

You cannot count on a floating point numbers being exactly equal when
calculations are involved, because of finite precision. If you never
enter seconds, then two times are equal if their difference is less than
half a minute, therefore instead of testing whether F=G, test whether
ABS(F-G)<0.000347222222222222

In reality, the difference is unlikely to be more than
0.000000000000000002 unless you are doing extensive calculation, but it
is generally better to define the tolerance in terms of what you know
about the inputs particularly if you don't understand the decimal/binary
conversions that are going on behind the scenes.

Jerry

 

[Kaytee]

Thanks to all and thank you Jerry W. Lewis
it works great now :

 

[Jerry W. Lewis]

You're welcome, glad it helped.

Jerry