Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Incorrect Simple Math
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 |
#2
|
|||
|
|||
Incorrect Simple Math
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 |
#3
|
|||
|
|||
Incorrect Simple Math
So is there a fix? -- mklalli ------------------------------------------------------------------------ mklalli's Profile: http://www.excelforum.com/member.php...o&userid=12973 View this thread: http://www.excelforum.com/showthread...hreadid=483140 |
#4
|
|||
|
|||
Incorrect Simple Math
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. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=483140 |
#5
|
|||
|
|||
Incorrect Simple Math
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple if then function | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Help with what should be a simple formula | Excel Worksheet Functions | |||
Simple math calculation - 50/50? | Excel Worksheet Functions | |||
How do I convert an existing MS Excel worksheet tracking a simple. | Excel Discussion (Misc queries) |