Rounding bug with calculated cells

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

Guest

To see this for yourself:
write "0.05" in cell A1
write "=5-4.95" in cell A2

We call all agree that both values are equal to 0.05.

Now take both cells and format them to display 1 digit. Even though they
are the same exact number, the former rounds up and the latter rounds down.

(The same is true of some, but not all other number combinations, e.g 4-3.95
and 3-2.95 are rounded incorrectly, but 2-1.95 and 1-0.95 are rounded
appropriately)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f2d37c0&dg=microsoft.public.excel.crashesgpfs
 
Hi:

The answer lies in widening the field to say 30 digits.

You will see in A1:

0.050000000000000000000000000000

You will see in A2:

0.049999999999999800000000000000

A1 is equal to .5 or greater and A2 is not. So the display
is correct.

This question is as old as computing. See the following for
a fuller explanation:

http://cpearson.com/excel/rounding.htm
http://www.mcgimpsey.com/excel/pennyoff.html


Pieter Vandenberg

: To see this for yourself:
: write "0.05" in cell A1
: write "=5-4.95" in cell A2

: We call all agree that both values are equal to 0.05.

: Now take both cells and format them to display 1 digit. Even though they
: are the same exact number, the former rounds up and the latter rounds down.

: (The same is true of some, but not all other number combinations, e.g 4-3.95
: and 3-2.95 are rounded incorrectly, but 2-1.95 and 1-0.95 are rounded
: appropriately)

: ----------------
: This post is a suggestion for Microsoft, and Microsoft responds to the
: suggestions with the most votes. To vote for this suggestion, click the "I
: Agree" button in the message pane. If you do not see the button, follow this
: link to open the suggestion in the Microsoft Web-based Newsreader and then
: click "I Agree" in the message pane.

: http://www.microsoft.com/office/com...f2d37c0&dg=microsoft.public.excel.crashesgpfs
 
Thank you. After I submitted it I read the other responses more closely, and
once they helped me to better understand the calculation methods I was able
to put it together. It seems as if there is no way around this unless I want
to use "Precision as Displayed."

I will have to validate my template with this exception as an improbable,
but possible, exception our internal rounding practices. Thank you for your
help.
 
mike124p said:
Thank you. After I submitted it I read the other responses more closely, and
once they helped me to better understand the calculation methods I was able
to put it together. It seems as if there is no way around this unless I want
to use "Precision as Displayed."
Click to expand...

Hi Mike,

Besides Precision as Displayed, there are other solutions:

1) Rounding:

=ROUND(5-4.95,2)
(Returns exactly 0.05.)


2) xlPrecision 2.0.1:

=xlpSUBTRACT(5,4.95)
(Returns exactly 0.05.)

See:
http://PrecisionCalc.com


Good Luck,

Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors
 
Back
Top