Incorrect Simple Math

[mklalli]

61.48-61.42=0.0599999999999952

Why?

This happens frequently in various formulas.

66555.54-66081.35=474.189999999988

What is going on? Surely I'm not the only one that has noticed this?

[Gary''s Student]

This is simply round-off error. The result of the calculation is not exact
and corresponds to the nearest value that can be represented by the IEEE
number format that Excel uses.
--
Gary's Student


"mklalli" wrote:

>
> 61.48-61.42=0.0599999999999952
>
> Why?
>
> This happens frequently in various formulas.
>
> 66555.54-66081.35=474.189999999988
>
> What is going on? Surely I'm not the only one that has noticed this?
>
>
> --
> mklalli
>
>
> ------------------------------------------------------------------------
> mklalli's Profile: http://www.excelforum.com/member.php...o&userid=12973
> View this thread: http://www.excelforum.com/showthread...hreadid=483140
>
>

[mklalli]

So is there a fix?

[MrShorty]

What kind of "fix" do you require?

Decimal fractions (even finite ones) usually have infinitely long representations when converted to binary. It just isn't possible to represent an infinitely long number within the finite confines of the computer's memory.

Roundoff error has been around for a long time and will likely always be a part of using a computer for calculations. So the real question is how to best deal with roundoff error (recognizing that it can often only be minimized, not eliminated), which will depend on what you need. Options to consider:

ROUND functions or number formats
Precision as displayed
Convert to integer (Assuming your numbers are EXACT to two decimals and not themselves rounded, an operation like (61.48*100-61.42*100)/100 can yield 0.0600000000000000)

Consider reviewing http://www.cpearson.com/excel/rounding.htm or any other article or text that discusses such roundoff errors. This is an issue that is fairly well documented.

[Jerry W. Lewis]

Almost all general purpose software (including Excel) does binary math.
In binary, most decimal factions have no exact representation (just as
1/3 has no exact decimal representation) and must be approximated. When
you do math with approximate inputs, it should be no surprise that the
output is only approximate.

The binary approximations to your input numbers are
61.47999999999999687361196265555918216705322265625
-61.4200000000000017053025658242404460906982421875
---------------------------------------------------
0.05999999999999516830939683131873607635498046875

66555.539999999993597157299518585205078125
-66081.35000000000582076609134674072265625
-------------------------------------------
474.189999999987776391208171844482421875

Do the math, the answers are correct, given the input numbers, and Excel
correctly reports these answers to its documented limit of 15 decimal
digits.

You can easily construct similar examples involving finite decimal
precision representation of numbers that are non-terminating decimals in
base 10. It is an unavoidable fact of life that some numbers cannot be
exactly represented with a finite number of decimals (or a finite number
of binary bits).

Your options are to either not use such numbers (for instance do integer
math) or structure your calculations such that the inherrent limitations
of finite precision are not a problem (for instance round results before
comparisons).

Earlier this year, I posted VBA code to display 28 decimal figures of
the binary representation of floating point numbers
http://groups.google.com/group/micro...fb95785d1eaff5

But it is not necessary to go that deep to predict the level of rounding
that you may need to do. Simply follow through on Excel's documented
limit of 15 decimal digits. Thus you can think of your equations as
61.4800000000000???
-61.4200000000000???
0.0600000000000???
which is consistent with
0.0599999999999952

Similarly
66555.5400000000??
-66081.3500000000??
---------
474-1900000000??
which is consistent with
474.189999999988

Jerry

mklalli wrote:

> 61.48-61.42=0.0599999999999952
>
> Why?
>
> This happens frequently in various formulas.
>
> 66555.54-66081.35=474.189999999988
>
> What is going on? Surely I'm not the only one that has noticed this?