Claus Busch said:

Am Tue, 28 Jan 2014 08:55:31 -0800 schrieb joeu2004:

that is correct. But if I calculate the time with your formula in C1 and

with mine in B1 and then try C1-B1 I get 0. If I change the format to 30

digits behind the decimal separator all digits are 0

Yet if you format B1 and C1 as Number with 16 decimal places, we see a

difference in the last decimal place. (Generally, it is better to format as

Scientific with 14 decimal places.)

But that is not always the case.

Yes, =C1-B1 is exactly zero. But =C1-B1-0 formatted as Scientific is about

3.47E-17.

The difference is due the dubious "close to zero" heuristic that is vaguely

and poorly described in

http://support.microsoft.com/kb/78113.

Excel arbitrarily replaces the arithmetic result with exactly zero when the

arithmetic result is "close to zero".

But even the title "close to zero" is incorrect. For example,

=1E30+1E14-1E30 results in exactly zero, even though 1E14 is not "close to

zero".

Moreover, the application and implementation of the heuristic is

inconsistent. For example, =1E30+1E14-1E30+0, just adding zero, and

=SUM(1E30,1E14,-1E30) result in about 1.40737E+14.

For similar reasons, sometimes =C1=B1 returns TRUE, but =C1-B1=0 returns

FALSE(!).

That is true in your case; even =C1=B1 returns FALSE.

But consider =MOD("6:45"-"6:15",1) in B1 and

=--TEXT(MOD("6:45"-"6:15",1),"hh:mm") in C1.

In that case, =C1=B1 is TRUE, but =C1-B1=0 is FALSE(!). Also, =C1-B1 is

exactly zero, but =C1-B1+0 is about 1.73E-17.