Microsoft's 'Get out of jail free' card for Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Microsoft has a standard response to any maths anomalies in Excel – KB214118 – we have a look at what it says and doesn’t say.

Microsoft has a ‘get out of jail free’ card when it comes to floating point / binary math errors in Excel – it’s called Knowledge Base article 214118 and it’s been around for some time.

We’ve copied the current text of KB 214118 below this article so you can see the wording as at May 2008 for yourself.

Some KB articles are written to conceal as much as it reveals and so it is with this Knowledge Base article.

The example Microsoft gives uses small integers but in a fairly complex formula. Any reasonable person would be hard pressed to understand that single example might also apply to the basic adding up of amounts of money.

Microsoft’s proposed workarounds have their own problems:

‘Precision as displayed’ will reduce the overall accuracy of your worksheets. Microsoft even warns you about that when you turn this option on.   In Excel 2007, choosing ‘Precision as displayed’ will get a pop-up warning ‘Data will permanently lose accuracy’ – a warning you don’t see on the KB article. KB214118 instead gives a different warning “can have cumulative calculation effects that can make your data increasingly inaccurate over time”

The Round() function has to be applied by the user in each and every occasion it’s needed. This affects the performance of the worksheet and adds work for the user. Round() isn’t always the appropriate choice either, though other options aren’t even mentioned in KB214118 – we have an article “What you see isn’t what Excel knows” which talks about the other options.

Some of the language used the article is interesting – it refers to a non-compulsory standard IEEE 754 but then says that the standard ‘requires’ the use of binary storage. In any event binary storage of numbers is not a reason for bad arithmetic – Microsoft is responsible for the way Excel uses those binary stored numbers in calculations and how they are presented to users.


How to correct rounding errors in floating-point arithmetic

Last Review

:

February 28, 2008

Revision

:

6.1

This article was previously published under Q214118

SYMPTOMS

Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation

=1*(.5-.4-.1)

may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0.

CAUSE

This behavior is not a problem in or a limitation of Excel or Works; this behavior occurs because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format.

WORKAROUND

Method 1

To minimize any effects of floating point arithmetic storage inaccuracy, use the Round() function to round numbers to the number of decimal places that is required by your calculation. For example, if you are working with currency, you would likely round to 2 decimal places:

=ROUND(1*(0.5-0.4-0.1),2)

Method 2

You can frequently prevent floating point rounding errors from affecting your work by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the precision that is displayed on the worksheet.

Note Use of the Precision as Displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed percision will maintain the accuracy of your data.

To use the Precision as displayed option, follow these steps:

Excel 2000, Excel 2002, and Excel 2003

1.

On the Tools menu, click Options.

2.

On the Calculation tab, under Workbook options, click to select the Precision as displayed check box.

3.

Click OK.

Excel 2007

1.

Click the Microsoft Office Button, click Excel Options, and then click Advanced category.

2.

In the When calculating this workbook section, select the workbook that you want, click to select the Set precision as displayed check box, and then click OK.

If you use the Precision as displayed option, you must format your numbers by using a specific number format.

To format cells to a specific number precision, follow these steps:

1.

Right-click the cells that you want to format, and then click Format Cells.

2.

On the Number tab, under Category, click Number.

3.

In the Decimal places box, select the precision (number of decimal places) that you want.

4.

Click OK.

MORE INFORMATION

The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

For example, the number 1/10 can be represented in a decimal number system with a simple decimal:

.1

However, the same number in binary format becomes the repeating binary decimal:

.0001100011000111000111 (and so on)

This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored.

If several arithmetic operations are performed to obtain a given result, these rounding errors may be cumulative.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

78113 (http://support.microsoft.com/kb/78113/) Floating-point arithmetic may give inaccurate results

Excel 2007

For more information about how to change the precision of calculation, click Microsoft Office Excel Help on the toolbar, type change when and how formulas are calculated in Excel Help, and then click Search to view the topic.

Excel 2002 and Excel 2003

For more information about changing the precision of calculation, click Microsoft Excel Help on the Help menu, type change when and how formulas are calculated in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Excel 2000

For more information about changing the precision of calculation, click Microsoft Excel Help on the Help menu, type change the way Microsoft Excel calculates formulas in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

APPLIES TO

Microsoft Excel 2000 Standard Edition

Microsoft Excel 2002 Standard Edition

Microsoft Office Excel 2003

Microsoft Office Excel 2007

subs profile e1563205311409 - Microsoft's 'Get out of jail free' card for Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address