Strange rounding issue in formatted number field

[pattera5]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a cell formatted as a number with 13 decimal places, I have a formula in there which returns a bizarre result.

It can be easily replicated by putting the following in the cell
=4935.48-4904.68

The result I get is 30.7999999999993. This is simple subtraction, where is this rounding issue coming from? any ideas?

 

[jpdphd]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a cell formatted as a number with 13 decimal places, I have a formula in there which returns a bizarre result.

It can be easily replicated by putting the following in the cell
=4935.48-4904.68

The result I get is 30.7999999999993. This is simple subtraction, where isthis rounding issue coming from? any ideas?

From Excel help:
"Excel stores and calculates with 15 significant digits of precision"

You are asking for 17 (4 digits to the left of the decimal point and
13 to the right).
Okay, you say, but any 2nd grader can do this problem and get 30.8.
Ah, but 2nd graders (and you and I) calculate differently from
computers. We don't convert our problems to base 16 (or whatever Excel
uses) before performing the calculation.

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a cell formatted as a number with 13 decimal places, I have a formula
in there which returns a bizarre result.

It can be easily replicated by putting the following in the cell
=4935.48-4904.68

The result I get is 30.7999999999993. This is simple subtraction, where is
this rounding issue coming from? any ideas?

This is an artifact of the conversion of binary to decimal and vice versa in
accordance with industry accepted standards. This is just the way Excel (and
almost every other program) works. Here is a tutorial to give you much more
information than you ever wanted to know:
http://support.microsoft.com/kb/42980/en-us

 

[pattera5]

Thank you, this is great information, it's not often I work with numbers at this precision. My problem is not quite as simple as I presented it, so it looks like I will have to put a bit more effort into it. You have saved me some serious hair pulling!