Bug in Excel - TRUE/FALSE function

[Mikee]

I stumbled upon a bug in Excel97, Excel2000...and maybe others:

First, let me say, I'm aware about problem of representing say, 2/10
in binary. The set of numbers I below, however, gives inconsistent
result when a number is rounded to two places and than compared to two
digit precision number.

SUM(D1379)=(415,216.230000001000) (note the rounding error - I
think)
SUM(D1378)=(414,469.830000000000)

ROUND(SUM(D1378),2)=414469.83 is FALSE (it should be true)

yet

2/ROUND(D378,2)+414469.83 gives DIV/0! as is should.

cheers,
Mikee
:tt

Here is the set of numbers (numbers in parenthesis are negative):

Column D |

757.800000000000 <---- This is line 1
1,799.000000000000
992.500000000000
2,330.000000000000
(2,768.000000000000)
(2,150.000000000000)
(5,242.500000000000)
(9,197.500000000000)

(6,228.000000000000)
692.000000000000
582.500000000000
(5,242.500000000000)

(9,925.000000000000)
7,196.000000000000
992.500000000000
(992.500000000000)
(992.500000000000)
(2,093.000000000000)
(1,799.000000000000)
(992.500000000000)

(18.720000000000)
(2,000.000000000000)
(992.500000000000)

(4.000000000000)
582.500000000000
582.500000000000
410.000000000000
757.800000000000
582.500000000000
992.500000000000
757.800000000000
1,484.000000000000
992.500000000000
(16,191.000000000000)
(582.500000000000)
(15.000000000000)
(288.010000000000)
(796.100000000000)

(58.000000000000)
4,100.000000000000
775.300000000000
1,499.000000000000
(5,242.500000000000)
(582.500000000000)
(6,977.700000000000)
305.000000000000
(17,378.900000000000)

5,825.000000000000
9,925.000000000000
3,203.000000000000
7,107.540000000000
1,333.500000000000
(5,242.500000000000)
(6,363.000000000000)
(6,977.250000000000)
(3,500.000000000000)
(582.500000000000)
(775.250000000000)
(775.250000000000)
(3,500.000000000000)
(769.800000000000)
28,827.000000000000
6,228.000000000000
5,951.460000000000
11,371.500000000000
1,529.000000000000

(8,932.500000000000)
582.500000000000
595.000000000000
(9,157.500000000000)
(6,977.700000000000)
(6,977.250000000000)
(13,761.000000000000)
(595.000000000000)
(1,484.000000000000)
(1,529.000000000000)
(3,876.250000000000)
(1,529.000000000000)
(1,298.500000000000)
(8,932.500000000000)

(4.000000000000)
(11.610000000000)
(4.000000000000)
5,242.500000000000
6,492.500000000000
(6,820.200000000000)
(6,007.000000000000)
(5,950.000000000000)
(5,950.000000000000)
(9,275.000000000000)
(5,950.000000000000)
(10,189.150000000000)
(707.000000000000)
(1,859.000000000000)

(5,825.000000000000)
(5,825.000000000000)
(15,290.000000000000)
(6,977.700000000000)
775.300000000000
707.000000000000
(5,825.000000000000)
(5,937.500000000000)
(6,977.700000000000)
(5,950.000000000000)
(5,950.000000000000)
(7,070.000000000000)
(595.000000000000)
(7,134.750000000000)
(15,740.000000000000)
290.000000000000

(7,753.000000000000)
(1,041.810000000000)
(12,985.000000000000)
(9,425.000000000000)
(5.360000000000)
(9,382.500000000000)
(15,780.020000000000)
(9,157.500000000000)
1,017.500000000000
1,017.500000000000
(5,950.000000000000)
(6,075.000000000000)
992.500000000000
3,090.000000000000
(39.120000000000)
(224.530000000000)
(1,007.250000000000)
(707.000000000000)

(7,070.000000000000)
(32,030.000000000000)
(7,578.000000000000)
(12,985.000000000000)
(10,175.000000000000)
775.300000000000
948.500000000000
1,126.200000000000
3,490.000000000000
1,919.000000000000
1,042.500000000000
(47.520000000000)
899.400000000000
(34.740000000000)
(48.700000000000)
3,381.970000000000
(4,915.000000000000)
(3,600.000000000000)
(5,467.500000000000)
(5,360.000000000000)
(310.000000000000)
(607.500000000000)
(10,425.000000000000)
(5,775.000000000000)
(12,001.500000000000)
(8,725.000000000000)
(4,950.000000000000)
(595.000000000000)
(1,042.500000000000)
(792.750000000000)
(792.750000000000)
(792.750000000000)
(1,042.500000000000)
(1,042.500000000000)
(249.700000000000)
(1,042.500000000000)
(291.000000000000)
(1,042.500000000000)
(1,042.500000000000)
4,062.320000000000


(5,825.000000000000)
(19,190.000000000000)
5,242.500000000000
7,134.750000000000
11,929.250000000000
17,271.000000000000
9,382.500000000000
7,134.750000000000
9,719.100000000000
9,362.920000000000
5,467.500000000000
14,166.000000000000
13,839.000000000000
3,425.030000000000
(6,382.320000000000)
1,995.000000000000
595.000000000000
1,017.500000000000
722.000000000000
607.500000000000
792.800000000000
4,950.000000000000
607.500000000000
1,042.500000000000
792.750000000000
792.750000000000
792.750000000000
1,042.500000000000
1,042.500000000000
249.700000000000
1,042.500000000000
291.000000000000
1,042.500000000000
1,042.500000000000
820.400000000000
10,009.500000000000
1,383.900000000000
10,009.500000000000
10,009.500000000000
820.400000000000
628.700000000000
628.700000000000
1,079.900000000000
4,659.000000000000
1,079.900000000000
8,204.000000000000
1,383.900000000000
628.700000000000
1,637.400000000000
1,637.400000000000
628.700000000000
820.400000000000
1,372.500000000000
1,042.500000000000
628.700000000000
628.700000000000
2,001.900000000000
628.700000000000
1,637.400000000000
746.400000000000
1,511.400000000000
1,511.400000000000
1,079.900000000000
74.000000000000
628.700000000000
8,187.000000000000
820.400000000000
1,637.400000000000
1,383.900000000000
275.400000000000
820.400000000000
400.000000000000
1,637.400000000000
628.700000000000
505.750000000000
1,383.900000000000
820.400000000000
5,399.500000000000
2,001.900000000000
628.700000000000
1,079.900000000000
3,143.500000000000
746.400000000000
820.400000000000
820.400000000000
820.400000000000
820.400000000000
820.400000000000
820.400000000000
820.400000000000
746.400000000000
820.400000000000
746.400000000000
820.400000000000
820.400000000000
820.400000000000
(6,820.200000000000)
(6,630.000000000000)
(5,545.000000000000)
(6,807.000000000000)
(280.000000000000)
(757.800000000000)
(7,134.750000000000)
(792.750000000000)
(9,382.500000000000)
(9,382.500000000000)
(7,134.750000000000)
(9,719.100000000000)
(13,839.000000000000)
(1,042.500000000000)
(3,490.000000000000)
(445.000000000000)
(540.750000000000)
(435.000000000000)
10,189.150000000000
2.840000000000

(7,464.000000000000)
(5,825.000000000000)
5,825.000000000000
5,825.000000000000
(227.240000000000)
595.000000000000
1,333.500000000000
540.750000000000
435.000000000000
1,079.900000000000
1,079.900000000000
1,079.900000000000
746.400000000000
2,001.900000000000
(9,157.500000000000)
(3,143.500000000000)
(3,143.500000000000)
(1,499.000000000000)
(2,001.900000000000)
(1,017.500000000000)
(14,736.600000000000)
(1,637.400000000000)
12,001.500000000000
6,717.600000000000
(11,929.250000000000)
(4.000000000000)
(142.660000000000)
(10,197.500000000000)
(540.000000000000)
(1,017.500000000000)
(607.500000000000)
(792.800000000000)
(607.500000000000)
(10,009.500000000000)
(1,383.900000000000)
(10,009.500000000000)
(10,009.500000000000)
(820.400000000000)
(628.700000000000)
(628.700000000000)
(1,079.900000000000)
(4,659.000000000000)
(1,079.900000000000)
(8,204.000000000000)
(1,383.900000000000)
(628.700000000000)
(1,637.400000000000)
(1,995.000000000000)
(1,637.400000000000)
(628.700000000000)
(1,372.500000000000)
(1,042.500000000000)
(628.700000000000)
(628.700000000000)
(2,001.900000000000)
(628.700000000000)
(1,637.400000000000)
(746.400000000000)
(1,511.400000000000)
(1,511.400000000000)
(1,079.900000000000)
(74.000000000000)
(628.700000000000)
(8,187.000000000000)
(820.400000000000)
(1,637.400000000000)
(1,383.900000000000)
(275.400000000000)
(820.400000000000)
(400.000000000000)
(1,637.400000000000)
(628.700000000000)
(1,383.900000000000)
(820.400000000000)
(5,399.500000000000)
(2,001.900000000000)
(628.700000000000)
(1,079.900000000000)
(3,143.500000000000)
(746.400000000000) <--- This is line 379

 

[J.E. McGimpsey]

The rounding that occurs in the math functions is sensitive to order
of operation. You can often find a workaround for a particular
rounding error by adding or removing parentheses. It's not a bug -
the operations are occuring to different numbers depending on which
calculations occur in which order. Of course, that particular order
may not work for different inputs.

Unless you're dealing with integers, the only way to be sure that a
numeric comparison is correct is to, instead of

If(f(a)=g(b),TRUE,FALSE)

use something like

=IF(ABS(f(a)-g(b))<epsilon, TRUE,FALSE)

where epsilon is sufficiently small enough to prevent an incorrect
TRUE value, but sufficiently large enough to be insensitive to
internal rounding errors.

For instance, if your range of numbers was between 0.01 and
10,000,000.00, you might choose an epsilon of 0.00001, which allows
for comparisons to 12 decimal digits of precision - more than the 10
digits in your range and less than the upper limit of 15 digits due
to the limits of IEEE floating point double math.

A formulaic way to do the same thing is to use ROUND():

=IF(ROUND(f(a)-g(b), 5)=0, TRUE,FALSE)

 

[Ken Wright]

Why would you think it should be true?

Does -x = x ?? (unless of course x = 0)

=ROUND(SUM(D1378),2)= -414469.83 works for me (Note the addn'l -ve sign)

Not sure if your line 1 starts on line 2, but the final number of your list ends on 378 for me if
I start on line 1. Even adjusting for that, your formula:-

2/ROUND(D378,2)+414469.83

is incorrect, as this could not possibly give #/DIV0 unless the cell D378 was blank or 0 itself.
You likely meant:-

=2/(ROUND(SUM(D1378,2)+414469.83) which does give DIV/0

 

[Jerry W. Lewis]

There are no Excel bugs, but several careless errors here.

The posted data when pasted gives D1378, not D1379, but the missing
cell seems to be blank, so there is no harm done.

2/ROUND(D378,2)+414469.83 gives 414469.829363767 not DIV/0!

2/(ROUND(SUM(D1378),2)+414469.83) gives DIV/0! because

ROUND(SUM(D1378),2)=-414469.83 is TRUE therefore

ROUND(SUM(D1378),2)=414469.83 is FALSE as it should be

Note that you have to round, because SUM(D1378) differs from 414469.83
in the 10th decimal place (16th figure)

SUM(D1379) differs from 415,216.23 in the 9th decimal place (15th
figure), which is not bad when adding so many mixed sign numbers that
cannot be represented exactly in binary.

What you are seeing is correct math with inputs that can only be
approximated.

Jerry