![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Sounds like the old decimal to binary to decimal conversion issue.
Sorry, I can't see all of the digits in your third column.
Understanding Apparent Math Errors
http://excelribbon.tips.net/T006176_Understanding_Apparent_Math_Errors.html
Summary: When you subtract two numbers from each other, you have a certain expectation of what Excel should deliver. What if you get something that is not quite what you expected, however?
Errors when Subtracting
http://excelribbon.tips.net/T010609\_Errors\_when\_Subtracting.html
Summary: When you subtract two numbers from each other, you have a certain expectation of what Excel should deliver. What if you get something that is not quite what you expected, however? (This tip works with MS Excel 2007, and Excel 2010. You can find a version of this tip for the older menu interface of Excel here: Errors When Subtracting.)
Fred expressed some confusion and concern about the results he received when subtracting certain numbers. For instance, when he subtracts 3809717.98 from 3799904.94, he should get -9813.04, but instead gets -9813.04000000003.
What Fred is witnessing is an artifact of Excel's limitations. The problem is ultimately related to how Excel works with floating-point numbers. Computers must store numbers internally as binary values, not as the decimal values we see displayed on the screen. Whole numbers can be stored as binary values relatively easily. When you throw a decimal point into the mix, then storing very large or very small numbers becomes more problematic—Excel just isn't able to store them with absolute precision. Instead, Excel provides an "approximate" result, out to 15 digits (the limits of its precision). Thus, you end up with something like -9813.04000000003, which contain the full 15 digits of precision possible in Excel.
Free Add-in Xnumbers can extend precision to 32760 digits - http://www.thetropicalevents.com/Xnumbers60.htm
A full discussion of how floating-point numbers are maintained in Excel and computers in general can very quickly get extremely technical. For those who want more information on the topic, here are two places you can start your research: http://support.microsoft.com/?kbid=78113
http://download.oracle.com/docs/cd/E19957-01/806-3568/ncg_goldberg.html
The bottom line is that the only way to get the "exact" results you want, to the number of decimal places you want, is to use the ROUND function in your formulas, as shown here:
=ROUND(3799904.94 - 3809717.98, 2)
You can also, if desired, change the precision used to within all formulas by following these steps:
- Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and then click Options.)
- At the left of the dialog box click Advanced.
- Scroll through the available options until you get to the section entitled "When Calculating this Workbook." (Click here to see a related figure.)
- Ensure that the Set Precision As Displayed check box is selected.
- Click OK.
Now, Excel uses the precision shown on the screen in all of its calculations, instead of doing calculations at the full 15-digit precision it normally maintains.
Comment
As with much Microsoft documentation, it is incorrect in detail. VBA relies on the same industry standard, yet it has no problem converting 1234567890123456 correctly, a 16-digit number.
The decision to truncate data entry after 15 significant digits is an arbitrary decision by the Excel developers.
Try the following experiment (see the vbvalue macro below)....
A1: type 1234567890123456
A2: =vbvalue("1234567890123456")
B1: =A1-1234567890123450
B2: =A2-1234567890123450
Format all cells as Number with 0 decimal places.
You will notice that B1 is exactly zero, but B2 is 6.
Explanation.... The IEEE-754 standard does not limit data to 15 digits of precision. However, only the first 15 significants digits are reliable across the entire range of numbers that can be represented with 64-bit floating-point. A great many numbers can be represented with greater decimal precision. In particular, all integers from 0 to +/- 9007199254740992 (2^53) can be represented exactly.
Example of Precision Error and discussion
If your an excel junkie, I found a mathematical error, actually an entire class of them. Try this..
As an experiment try adding the following two numbers in Excel (-9927.504+10606.68) to see what number ya get. Set the precision to 16 bits. Execel gets 679.1759999999990000, the correct answer is 679.176
The error resides in the 12 bit of precision. I'm guessing that Excel is using Double Precision for it's calculations, which has a 52 bit mantissa. The 12th bit of precision, where the error occurs, is nowhere close to the LSB of the mantissa. If it was, then the result could be slightly different depending on the rounding mode (Truncate, Round to Even, Round to plus infinity or Round to minus infinity). Since this isn't the case, then there is an error in Excel or perhaps the Intel FP unit if Excel uses that for it's calculation.
IEEE 754 FP junkie
Per the help topic "Excel specifications and limits":
Feature Maximum limit
Number precision 15 digits
Consequently, Excel will never be able to display 16 digits of precision.
Microsoft has this website on "How to correct rounding errors in floating-point arithmetic":
http://support.microsoft.com/kb/214118
These explanations are very technical and you can read them if you're inclined to:
Tutorial to Understand IEEE Floating-Point Errors:
http://support.microsoft.com/kb/42980
Floating-point arithmetic may give inaccurate results in Excel:
http://support.microsoft.com/kb/78113/en-us
Regards,
Ron Coderre
Microsoft MVP - Excel
*******************
I suggest to read the explanation and to follow the links of my Excel Don't #8:
http://sulprobil.com/html/excel_don_ts.html
Regards,
Bernd
http://www.sulprobil.com/html/excel_forums.html
**********************
Yes, you are correct. Enter the formula =-9927.504+10606.68 into A1, and format with 12 or more decimal place. We will see 679.175999999999.
(Note: Excel only formats up to the first 15 significant digits. So zeros will be appended to the right if the precision is more than 12 decimal places in this case.)
Let me say at the outset, the remedy is to use the formula =ROUND(-9927.504+10606.68,3) if you expect accuracy to 3 decimal places.
This should not come as any surprise.
As I explained previously (more than 2 years ago), the problem is that Excel relies on the computer's native 64-bit binary floating-point to represent numbers and to perform arithmetic. For some details, see http://support.microsoft.com/kb/78113. (**[EDIT]**But read KB 78113 with a grain of salt. There a number of mistakes in the details.)
Consequently, most non-integers (and integers greater than 2^53) cannot be represented exactly. So infinitesimal anomalies creep into most Excel arithmetic. For example, IF(10.1-10=0.1,TRUE) returns FALSE(!).
The work-around is to explicitly round non-integer expressions (and integer division) when we expect the result to be accurate to a specific number of decimal places. [1] For example, IF(ROUND(10.1-10,2)=0.1,TRUE) returns TRUE as expected.
To explain your particular example....
-9927.504 is represented internally exactly as:
-9927.50400000000,0814907252788543701171875
10606.68 is:
10606.6800000000,002910383045673370361328125
=-9927.504+10606.68 is:
679.175999999999,4761310517787933349609375
In contrast, the constant 679.176 and ROUND(-9927.504+10606.68,3) are:
679.176000000000,0445652403868734836578369140625
[PS] For an "IEEE 754 FP junkie" like yourself, I might add that perhaps even a pencil-and-paper calculation with the exact numbers above will not result in 679.175999999999,4761310517787933349609375.
Frankly, I don't know; I did not try it myself. ([EDIT] Actually, that is exactly what we get with pencil and paper. Nevertheless....) But if that is the case, the reason is that Intel-compatible CPUs perform arithmetic using 80-bit binary floating-point. Excel rounds pairwise operations to 64-bit binary floating-point. So there might be some rounding "error" in the LSBs as well.
BTW, in contrast, VBA tries to use the intermediate 80-bit FP results when calculating complex expressions. Consequenty, VBA and Excel arithmetic sometimes have different results. It makes no difference in your example of only two operands.
[1] Alternatively to explicit rounding, we could set the Precision As Displayed calculation option (PAD). However, I do not recommend that for a number of reasons. Also, even setting PAD does not fix all such problems, for example the IF() example above.
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
Floating-point arithmetic may give inaccurate results in Excel- kb78113
http://support.microsoft.com/kb/78113/en-us
This article discusses how Microsoft Excel stores and calculates floating-point numbers. This may affect the results of some numbers or formulas due to rounding and/or data truncation.
How to correct rounding errors in floating-point arithmetic - KB214118
http://support.microsoft.com/kb/214118
Excel addition problems - Microsoft’s response
Here's Microsoft's response to the anomalies found in Excel's adding up.
by Office Watch
We asked Microsoft for a comment on the Excel addition problems that our readers have been discovered. Office Watch pointed out the anomalies in results when the same numbers are added in a different order or grouped differently – something that should make no difference to the result.
The company response was:
“ This behavior is not a bug in Excel. Excel was designed in accordance to the IEEE Standard for Binary Floating-Point Arithmetic (*IEEE 754*). The standard defines how floating-point numbers are stored and calculated, and requires that numbers be stored in binary format. The IEEE 754 standard is widely used because by specifying numbers be stored in binary format it reduces storage requirements and allows the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion.
For more information on Excel’s use of the IEEE 754 standard and workarounds available, please see KB article 214118,*“How to correct rounding errors in floating-point arithmetic”*.
To some of our readers that will seem like a reasonable response, to others (the ‘arithmetic is arithmetic’ camp) it will sound like a brush off.
The IEEE 754 standard that Microsoft uses to justify the maths errors in Excel is a long and complicated document – Wikipedia has a good summary.
Article posted: Monday, 12 May 2008
MS Fixes Excel (a) Calculation Bug – 2007 10 11
http://www.eweek.com/c/a/Enterprise-Applications/Microsoft-Fixes-Excel-Calculation-Bug/
Workarounds for Excel's addition problems
http://news.office-watch.com/t/n.aspx?a=628
Now you know NOT to trust the precision of Excel’s results even for simple additions, what can you do about it?
As we've shown in recent articles (Excel SUM anomaly, More Excel addition strangeness and Excel addition problems - bug or not? among others) , Excel's arithmetic isn't as reliable as Microsoft would like us to believe. In this article we'll look at what you can do about it and the limitations of the fixes.
The examples that Microsoft gives in a Knowledge Base article don't give any proper idea about the scale of the problem - from their single example you'd have no idea that adding up a few amounts of money might not work out correctly.
The Microsoft 'workarounds' are summarised briefly with no clue about the limitations that the fixes have.
As usual, the Knowledge Base is about 'damage minimisation' more than presenting the full situation to Microsoft's customers - which is where Office Watch comes in.
In very simple calculations the stray floating point errors might not affect you. Take this simple example:
| -$1.23 |
|---|
| $1.12 |
| $0.11 |
| $0.00000000000000012490009027033000000 |
The real answer is zero (ie $0.00000000000000000000000000000000000 ).
As Excel is currently released by Microsoft it has a problem with addition/subtraction especially when it involves a mix of negative and positive numbers as the result approaches zero.
Sadly you have to assume that any calculation, however simple, might contain floating point errors. Since results near zero are quite common (ie 'balance sheet' like situations) the Excel bug isn't as rare a customer experience as Microsoft likes to make out.
There are two broad areas where the floating point error becomes a real problem for Excel users (we'd be interested in hearing about others):
- Comparison. Any time you compare one cell to another you need to allow for Excel's inaccuracies. This doesn't just apply to the common IF function but also conditional formatting.
- Scale. If you're multiplying large numbers the 'irrelevant' error in Excel will start encroaching on your results.