In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258 or
-0.000000000000000055511151231258 (number format with 30 decimal places)

Any ideas why?

That is more of a computer thing than a specific XL thing. Any time you deal
with decimal values you can get very small artifacts showing up in the
numbers. To deal with the artifacts use a rounding function to remove the
anomolies.
--
HTH...

Jim Thomlinson


"Christian Weller" wrote:

In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258 or
-0.000000000000000055511151231258 (number format with 30 decimal places)

Any ideas why?

In short: yes!
Welcome to the world of computers where everything is 0 or 1 (binary or base
2)

Excel (and most computer apps other than COBOL) use what is called the IEEE
convention for converting decimal numbers (base 10 --- because we have 10
fingers) to binary (base 2). This uses a finite number of bytes so we get
what is called round off errors since some decimal numbers (like 0.1) have
no exact binary representation (just as the fraction 1/3 has no exact
decimal representation but is 0.33333333333.....)

The net result is that sometimes when 0 is expected we actually get a number
that is just a tiny bit different. We say that Excel has a 'precision of 15
decimal places'. This means that any two numbers that differ only in the
15th decimal, place are to be considered the same

How to avoid? Replace =A1-B1 by =ROUND(A1,B1,12) and your problem will go
away. I am sure you are not being so precise that this rounding will affect
your results adversely unless you do quantum mechanics.

Further reading:
IEEE 754
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

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

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

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/default...NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Christian Weller" <Christian wrote in
message ...
In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on
formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258 or
-0.000000000000000055511151231258 (number format with 30 decimal places)

Any ideas why?

"Bernard Liengme" wrote
We say that Excel has a 'precision of 15 decimal places'.

Actually, "we" say that Excel formats (displays) only up to the first 15
significant digits.

This is different from "15 decimal places" (e.g.
0.000000000000000123456789012345).

And it is different from "precision" of the internal representation of any
number, which can have as many as 1074 decimal places.


This means that any two numbers that differ only in the 15th decimal,
place are to be considered the same

Well, it might mean that any two numbers that differ only after the 15th
significant digit are considered equal.

But that is incorrect. Moreover, "considered equal" and "resulting in zero
when subtracted" are sometimes two different things(!) in Excel. The OP is
interested in the latter.

For example, consider 1E-7 in A1 and =1E-7+8*2^-76 in A2.

Both display as 0.000000100000000000000. (I prefer to use the Scientific
format with 14 dp. That avoids counting errors.)

And IF(A1=A2,TRUE) does indeed result in TRUE.

But IF(A1-A2=0,TRUE) results in FALSE(!). =A1-A2 also does not result in
zero.

Even more interesting: when A2 is =1E-7+7*2^-76, =A1-A2 does result in
exactly zero. But IF(A1-A2=0,TRUE) still results in FALSE(!!).

By the way, =A1-A2-0 does not result in zero(!!); nor does =(A1-A2).

Of course, as I believe you know, these anomalies arise due to Excel's
poorly-defined heuristics that try to hide the abberations caused by
internal binary arithmetic and representation.


How to avoid? Replace =A1-B1 by =ROUND(A1,B1,12)
and your problem will go away.

Of course, you mean ROUND(A1-B1,12).

And while that might be true when zero is the expected result, for rounding
time generally, I prefer to use --TEXT(A1-B1,"h:mm:ss")
or --TEXT(A1-B1,"h:mm:ss.000"), depending on the cell format.

That ensures equality with all h:mm:ss or h:mm:ss.000 constants and the
equivalent using TIME().

For example, if A1 contains 23:59:59, A2 contains 23:59:58 and A3 contains
00:00:01, then IF(ROUND(A1-A2,12)=A3,TRUE) returns FALSE(!), whereas
IF(--TEXT(A1-A2,"h:mm:ss")=A3,TRUE) returns true.

You might retort that IF(ROUND(A1-A2,12)=ROUND(A3,12),TRUE) would return
TRUE. But I suspect the need for the second ROUND is counter-intuitive to
many.

My point is: the use of --TEXT() does exactly what I would want, namely:
it ensures that the internal representation of the actual result exactly
matches the internal representation of the displayed result if we enter it
as a constant or the equivalent using TIME(). WYSIWYG.


----- original message -----

"Bernard Liengme" wrote in message
...
In short: yes!
Welcome to the world of computers where everything is 0 or 1 (binary or
base 2)

Excel (and most computer apps other than COBOL) use what is called the
IEEE convention for converting decimal numbers (base 10 --- because we
have 10 fingers) to binary (base 2). This uses a finite number of bytes so
we get what is called round off errors since some decimal numbers (like
0.1) have no exact binary representation (just as the fraction 1/3 has no
exact decimal representation but is 0.33333333333.....)

The net result is that sometimes when 0 is expected we actually get a
number that is just a tiny bit different. We say that Excel has a
'precision of 15 decimal places'. This means that any two numbers that
differ only in the 15th decimal, place are to be considered the same

How to avoid? Replace =A1-B1 by =ROUND(A1,B1,12) and your problem will go
away. I am sure you are not being so precise that this rounding will
affect your results adversely unless you do quantum mechanics.

Further reading:
IEEE 754
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

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

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

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/default...NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Christian Weller" <Christian wrote in
message ...
In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on
formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258 or
-0.000000000000000055511151231258 (number format with 30 decimal places)

Any ideas why?

but often get =0.000000000000000055511151231258

Another way to say the same thing as the others...

=POWER(2,-54)
0.000000000000000055511151231258

= = = = =
Dana DeLouis



Christian Weller wrote:
In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258 or
-0.000000000000000055511151231258 (number format with 30 decimal places)

Any ideas why?

"Dana DeLouis" wrote:
Another way to say the same thing as the others...
=POWER(2,-54)
0.000000000000000055511151231258

Or simply 2^-54.

But we have no way of knowing whether the OP is encountering only a
single-bit difference, especially since the OP posted only 14 significant
digits.

The OP's number might be anything between the equivalent of 2^-54 - 2^-102
and 2^-54 + 2^-101 inclusive, all of which display as
0.000000000000000055511151231258.

In fact, if we enter 0.000000000000000055511151231258, the internal value is
the same as 2^-54 + 2^-102 - 2^-105, which also can be written as 2^-54 +
2^-103 + 2^-104 + 2^-105. The exact representation is a number with 106
digits in the decimal fraction, about
0.0000000000000000555111512312579,9958. (The comma demarcates the first 15
significant digits.)

In contrast, 2^-54 is "only"
0.0000000000000000555111512312578,2702118158340454 1015625.


---- original message -----

"Dana DeLouis" wrote in message
...
but often get =0.000000000000000055511151231258

Another way to say the same thing as the others...

=POWER(2,-54)
0.000000000000000055511151231258

= = = = =
Dana DeLouis



Christian Weller wrote:
In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on
formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258
r -0.000000000000000055511151231258 (number format with 30 decimal
places)

Any ideas why?

For purposes like this, I always think in scientific notation
Sorry if I confused you
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JoeU2004" wrote in message
...
"Bernard Liengme" wrote
We say that Excel has a 'precision of 15 decimal places'.

Actually, "we" say that Excel formats (displays) only up to the first 15
significant digits.

This is different from "15 decimal places" (e.g.
0.000000000000000123456789012345).

And it is different from "precision" of the internal representation of any
number, which can have as many as 1074 decimal places.


This means that any two numbers that differ only in the 15th decimal,
place are to be considered the same

Well, it might mean that any two numbers that differ only after the 15th
significant digit are considered equal.

But that is incorrect. Moreover, "considered equal" and "resulting in
zero when subtracted" are sometimes two different things(!) in Excel. The
OP is interested in the latter.

For example, consider 1E-7 in A1 and =1E-7+8*2^-76 in A2.

Both display as 0.000000100000000000000. (I prefer to use the Scientific
format with 14 dp. That avoids counting errors.)

And IF(A1=A2,TRUE) does indeed result in TRUE.

But IF(A1-A2=0,TRUE) results in FALSE(!). =A1-A2 also does not result in
zero.

Even more interesting: when A2 is =1E-7+7*2^-76, =A1-A2 does result in
exactly zero. But IF(A1-A2=0,TRUE) still results in FALSE(!!).

By the way, =A1-A2-0 does not result in zero(!!); nor does =(A1-A2).

Of course, as I believe you know, these anomalies arise due to Excel's
poorly-defined heuristics that try to hide the abberations caused by
internal binary arithmetic and representation.


How to avoid? Replace =A1-B1 by =ROUND(A1,B1,12)
and your problem will go away.

Of course, you mean ROUND(A1-B1,12).

And while that might be true when zero is the expected result, for
rounding time generally, I prefer to use --TEXT(A1-B1,"h:mm:ss")
or --TEXT(A1-B1,"h:mm:ss.000"), depending on the cell format.

That ensures equality with all h:mm:ss or h:mm:ss.000 constants and the
equivalent using TIME().

For example, if A1 contains 23:59:59, A2 contains 23:59:58 and A3 contains
00:00:01, then IF(ROUND(A1-A2,12)=A3,TRUE) returns FALSE(!), whereas
IF(--TEXT(A1-A2,"h:mm:ss")=A3,TRUE) returns true.

You might retort that IF(ROUND(A1-A2,12)=ROUND(A3,12),TRUE) would return
TRUE. But I suspect the need for the second ROUND is counter-intuitive to
many.

My point is: the use of --TEXT() does exactly what I would want, namely:
it ensures that the internal representation of the actual result exactly
matches the internal representation of the displayed result if we enter it
as a constant or the equivalent using TIME(). WYSIWYG.


----- original message -----

"Bernard Liengme" wrote in message
...
In short: yes!
Welcome to the world of computers where everything is 0 or 1 (binary or
base 2)

Excel (and most computer apps other than COBOL) use what is called the
IEEE convention for converting decimal numbers (base 10 --- because we
have 10 fingers) to binary (base 2). This uses a finite number of bytes
so
we get what is called round off errors since some decimal numbers (like
0.1) have no exact binary representation (just as the fraction 1/3 has no
exact decimal representation but is 0.33333333333.....)

The net result is that sometimes when 0 is expected we actually get a
number that is just a tiny bit different. We say that Excel has a
'precision of 15 decimal places'. This means that any two numbers that
differ only in the 15th decimal, place are to be considered the same

How to avoid? Replace =A1-B1 by =ROUND(A1,B1,12) and your problem will go
away. I am sure you are not being so precise that this rounding will
affect your results adversely unless you do quantum mechanics.

Further reading:
IEEE 754
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

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

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

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/default...NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Christian Weller" <Christian wrote in
message ...
In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on
formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258 or
-0.000000000000000055511151231258 (number format with 30 decimal
places)

Any ideas why?

Errata....

I wrote:
The OP's number might be anything between the equivalent of
2^-54 - 2^-102 and 2^-54 + 2^-101 inclusive

That was over-simplified. Actually, the OP's number might be any value of
the form 2^-54 + n*2^-106, where n is -30 to 50 inclusive. That might be
clearer, as well.


In fact, if we enter 0.000000000000000055511151231258, the internal
value is the same as 2^-54 + 2^-102 - 2^-105, which also can be written
as 2^-54 + 2^-103 + 2^-104 + 2^-105.

Also written as 2^-54 + 14*2^-106.


----- original message -----

"JoeU2004" wrote in message
...
"Dana DeLouis" wrote:
Another way to say the same thing as the others...
=POWER(2,-54)
0.000000000000000055511151231258

Or simply 2^-54.

But we have no way of knowing whether the OP is encountering only a
single-bit difference, especially since the OP posted only 14 significant
digits.

The OP's number might be anything between the equivalent of 2^-54 - 2^-102
and 2^-54 + 2^-101 inclusive, all of which display as
0.000000000000000055511151231258.

In fact, if we enter 0.000000000000000055511151231258, the internal value
is the same as 2^-54 + 2^-102 - 2^-105, which also can be written as 2^-54
+ 2^-103 + 2^-104 + 2^-105. The exact representation is a number with 106
digits in the decimal fraction, about
0.0000000000000000555111512312579,9958. (The comma demarcates the first
15 significant digits.)

In contrast, 2^-54 is "only"
0.0000000000000000555111512312578,2702118158340454 1015625.


---- original message -----

"Dana DeLouis" wrote in message
...
but often get =0.000000000000000055511151231258

Another way to say the same thing as the others...

=POWER(2,-54)
0.000000000000000055511151231258

= = = = =
Dana DeLouis



Christian Weller wrote:
In excel I am calculating the time difference between two times
I.e. 6:17:36 AM - 6:17:36 AM = 00:00:00 (12:00:00 am depending on
formatting)
Sometimes I get 0 , but often get =0.000000000000000055511151231258
-0.000000000000000055511151231258 (number format with 30 decimal
places)

Any ideas why?