excel rounding error?

  • Thread starter Thread starter brzak
  • Start date Start date
B

brzak

I have what I think is a simple question, which may not have a very
simple answer...

why is the value of the resulf of:

00:09:00 - 00:01:00 (that is 9mins - 1 min, i.e. 8 minutes)

not equal to:

9/24/60 - 1/24/60 (as above, since 1 = 24 hours, so 1 minute =
1/24/60)

whereas the value of 00:5:00 - 00:01:00 *is equal to* the result of
5/24/60 - 1/24/60

The text below is a sample to demonstrate the problem (select the
text, starting from google groups and to the end of my post, paste
into cell A1 in a new spreadsheet, it's semicolon delimited - do 'Text
to Columns' and select the semicolon delimiter option in the wizard
that comes up). Thanks in advance for any insight into this one.

google groups demo;9 minutes;1 minute;9 mins - 1 min;value
time function;=TIME(0,9,0);=TIME(0,1,0);=B2-C2;=VALUE(D2)
fraction;=9/24/60;=1/24/60;=B3-C3;=VALUE(D3)
comparison;=B2=B3;=C2=C3;=D2=D3;=E2=E3
;10 minutes;1 minute;10 mins - 1 min;value
time function;=TIME(0,5,0);=TIME(0,1,0);=B6-C6;=VALUE(D6)
fraction;=5/24/60;=1/24/60;=B7-C7;=VALUE(D7)
comparison;=B6=B7;=C6=C7;=D6=D7;=E6=E7
 
I have what I think is a simple question, which may not have a very
simple answer...

why is the value of the resulf of:

00:09:00 - 00:01:00 (that is 9mins - 1 min, i.e. 8 minutes)

not equal to:

9/24/60 - 1/24/60 (as above, since 1 = 24 hours, so 1 minute =
1/24/60)

whereas the value of 00:5:00 - 00:01:00 *is equal to* the result of
5/24/60 - 1/24/60

The text below is a sample to demonstrate the problem (select the
text, starting from google groups and to the end of my post, paste
into cell A1 in a new spreadsheet, it's semicolon delimited - do 'Text
to Columns' and select the semicolon delimiter option in the wizard
that comes up). Thanks in advance for any insight into this one.

google groups demo;9 minutes;1 minute;9 mins - 1 min;value
time function;=TIME(0,9,0);=TIME(0,1,0);=B2-C2;=VALUE(D2)
fraction;=9/24/60;=1/24/60;=B3-C3;=VALUE(D3)
comparison;=B2=B3;=C2=C3;=D2=D3;=E2=E3
;10 minutes;1 minute;10 mins - 1 min;value
time function;=TIME(0,5,0);=TIME(0,1,0);=B6-C6;=VALUE(D6)
fraction;=5/24/60;=1/24/60;=B7-C7;=VALUE(D7)
comparison;=B6=B7;=C6=C7;=D6=D7;=E6=E7
Click to expand...


The issue of rounding errors, IEEE related limits to double-precision
arithmetic, and binary representation of decimal numbers has been extensively
discussed in these newsgroups. A search of the groups should reveal these
discussions as well as how to deal with these issues.

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

Because of rounding issues, the difference between those two values is
approximately 4.4E-19 -- close to zero but not exactly; so the Boolean
comparison returns FALSE.
--ron
 
brzak said:
why is the value of the resulf of:
00:09:00 - 00:01:00 (that is 9mins - 1 min, i.e. 8 minutes)
not equal to:
9/24/60 - 1/24/60 (as above, since 1 = 24 hours, so 1 minute =
1/24/60)
whereas the value of 00:5:00 - 00:01:00 *is equal to* the result of
5/24/60 - 1/24/60
Click to expand...

Because it appears that Excel computes 9/60/24 and 1/60/24 instead of
9/24/60 and 1/24/60.

The difference is "bit", specifically the "least-significant bit". It is an
anomaly of binary floating-point arithmetic. It is not apparent in Excel
even when formatted to display 15 significant digits.

For the more-general TIME(h,m,s), I don't know whether time is converted by
(h + m/60 + s/3600)/24 or by h/24 + m/60/24 + s/3600/24. I have not yet
stumbled across an example where it makes a difference.

Note: Moreover, we might see the difference only when using VBA for the
evaluation, which is closer to the internal implementation of TIME and
time-spec conversion. There is a subtle difference between Excel and VBA
floating-point evaluation. I am using VBA first, then trying the same with
Excel.


----- original message -----
 
Thank you both for your replies.

Joe - I've noticed the difference between Excel and VBA as well.
I tried to reproduce an error present in some VBA code (subtracting
times)
but was unable to - there didn't seem to be any correlation between
the two
calculation methods (i.e. i couldn't discern a pattern between the
numbers
being subtracted and whether the rounding error was above or below, or
if
it was exact).
 
brzak said:
Joe - I've noticed the difference between Excel
and VBA as well. I tried to reproduce an error
present in some VBA code (subtracting times)
but was unable to - there didn't seem to be any
correlation between the two calculation methods
Click to expand...

I can explain the difference, but it can get very technical.

I could probably explain things better if you provided the VBA example. But
generally....

Both Excel and VBA (for type Double) store numbers using the IEEE 64-bit
floating-point form. However, Intel-compatible CPUs use 80-bit
floating-point registers to perform computations. The 80-bit FP form
extends both the precision and the range of values.

Excel stores the 80-bit result of each pairwise arithmetic operation into
64-bit variables, which causes a loss of precision.

VBA tries to keep intermediate results in the 80-bit form.

That does not always lead to differences in the result. Usually, that
depends on the order and magnitude of the pairwise arithmetic operations.

In summary, the expression Min/60/24 is evaluated by both Excel and VBA as x
= Min/60, then x/24. Likewise, Min/24/60 is evaluated as x = Min/24, then
x/60.

Mathematically, there is no difference.

But when the results of the pairwise operations cannot be represented
exactly in a finite number of bits, the order of operations can make a small
difference in the least-significant bits. Moreover, a difference can arise
when Min/24 or Min/60 is converted from 80-bit FP to 64-bit FP, as Excel
does. The operative word is "can"; sometimes there is no difference.

I hope that helps.


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


Thank you both for your replies.

Joe - I've noticed the difference between Excel and VBA as well.
I tried to reproduce an error present in some VBA code (subtracting
times)
but was unable to - there didn't seem to be any correlation between
the two
calculation methods (i.e. i couldn't discern a pattern between the
numbers
being subtracted and whether the rounding error was above or below, or
if
it was exact).
 
Back
Top