Bug using series

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
This gives a strange result :
Type in
Cell A1 : -5
Cell B1 : -4.9
Select both cells and increment the serie -> -4.8, -4.7, ...
Below, you get suddenly the following result :
-3.7000000000000000000000
-3.6000000000000000000000
-3.5000000000000100000000
-3.4000000000000100000000
-3.3000000000000100000000
and so on...
 
Hello:

This is as old as computing, and it is not a bug. See

http://www.cpearson.com/excel/rounding.htm

for an explanation.

Pieter Vandenberg

: Hello,
: This gives a strange result :
: Type in
: Cell A1 : -5
: Cell B1 : -4.9
: Select both cells and increment the serie -> -4.8, -4.7, ...
: Below, you get suddenly the following result :
: -3.7000000000000000000000
: -3.6000000000000000000000
: -3.5000000000000100000000
: -3.4000000000000100000000
: -3.3000000000000100000000
: and so on...
 
As Pieter Vandenberg has pointed out, this is a known and natural consequence
of computers doing math in binary. The binary approximation to 4.9
(4.90000000000000035527...) is slightly larger than the nominal value, hence
the increment used in your series is slightly smaller than you intended.

You cannot directly see this approximation in 4.9, because Excel (as
documented) will display no more than 15 figures, but you can see the full
values of binary approximations using the D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

There are several ways to get your intended result using formulas, such as
=ROUND(A1+0.1)
in B1, and copied across or
=(-51+column())/10
in A1 and copied across.

The subject of this group is things that cause Excel to crash. Your post
would have been more on topic in the general group.

Jerry
 
Back
Top