Excel calculation error ???

Question

Can someone explain this to me. My kid came back from college saying there is a bug in Excel. Although a simple formula, Excel brings ghost values in the calculation (See content of Cell C3.

Here is the simple table and cell content:

Answer 1

Welcome to the floating point rounding error in Excel. If you re-examine your formulas with the addition of ROUND(), you will get a correct answer.

Floating-point arithmetic may give inaccurate results in Excel

How to correct rounding errors in floating-point arithmetic

You can use Evaluate Formula command from the Formula tab's Formula Auditing group to show how Excel is dealing with numbers.

Evaluate a nested formula one step at a time

Answer 2

88% of spreadsheets have errors

http://www.marketwatch.com/story/88-of-spreadsheets-have-errors-2013-04-17

Research: Bad math rampant in family budgets and Harvard studies

April 17, 2013 By Jeremy Olshan

Microsoft Excel makes it easy for anyone to do the kind of number crunching once reserved for accountants and statisticians. But the world’s best-selling spreadsheet software has also contributed to the proliferation of bad math.

Close to 90% of spreadsheet documents contain errors, a 2008 analysis of multiple studies suggests. “Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” writes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”

***************

The 'error' is not limited to Excel. I is inherent in the way computers handle numbers as powers of 2 rather than 10. The conversion introduces errors. As jeeped pointed out, the fix is to push the precision far enough so that rounding does not become an issue.

(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

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.

***********************

You may want to reply with these links about "excel errors" in the real world

Excel’s effect on economic policy - Errors in Spreadsheet

http://office-watch.com/t/n.aspx?a=1854

An Excel mistake caused a change in economic policy. A user mistake or Microsoft's?

Errors When Subtracting

http://excel.tips.net/T003354_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 Excel 97 | 2000 | 2002 | 2003 | 2007.)

Comment

http://answers.microsoft.com/en-us/office/forum/office\_2010-excel/i-have-excel-for-students-and-when-i-enter-a-16/c815fbf1-197c-46fd-a119-c2be132d84f7

**********************

The question of rounding introduces a whole other "religious war".  Precisely what form of rounding is "right".

Here are some articles on rounding

18 Tips on Rounding numbers using Excel Formulas – Rounding summary

http://chandoo.org/wp/2012/09/28/round-numbers-excel-formulas/

Rounding Religious Wars

http://excel.tips.net/T002829_Rounding_Religious_Wars.html

Summary: How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. This tip discusses the brouhaha and looks at different takes on the issues involved. (This tip works with Excel 97 | 2000 | 2002 | 2003 | 2007.)

Rounding Religious Wars, Take Two

http://excel.tips.net/T002835_Rounding_Religious_Wars_Take_Two.html

Summary: How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. This tip is the second part of a continuing discussion on this issue. (This tip works with Excel 97 | 2000 | 2002 | 2003 | 2007.)

Rounding in Results

http://excel.tips.net/T002328_Rounding_in_Results.html

Summary: Sometimes the results shown by various calculations can seem downright odd. This tip shows a couple of those calculations and explains why the results aren't really odd, if you know what Excel is doing. (This tip works with Excel 97 | 2000 | 2002 | 2003.)

Rounding to Two Significant Digits

http://excel.tips.net/T010396_Rounding_to_Two_Significant_Digits.html

This tip (10396) applies to Microsoft Excel versions: 97 | 2000 | 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Rounding to Two Significant Digits.

Finding the Number of Significant Digits

http://excel.tips.net/T010975_Finding_the_Number_of_Significant_Digits.html

This tip (10975) applies to Excel 97 | 2000 | 2002 | 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Finding the Number of Significant Digits.

****************************

Understanding Negative Zero Amounts in Excel

http://excelribbon.tips.net/T006198_Understanding_Negative_Zero_Amounts_in_Excel.html

Summary: There are negative numbers, positive numbers, and zero—which is neither positive or negative. So why does Excel sometimes show a negative sign in front of a zero value?

Answer 3

Rohn007 wrote:

88% of spreadsheets have errors

http://www.marketwatch.com/story/88-of-spreadsheets-have-errors-2013-04-17

Research: Bad math rampant in family budgets and Harvard studies

April 17, 2013 By Jeremy Olshan

That has nothing to do with the systemic binary arithmetic problem that Simon asks about and Jeeped answers correctly, succinctly and completely.

Olshan's article refers to spreadsheet design errors created by the programmer (user).

Olshan writes:  "By failing to include certain spreadsheet cells in its calculations, the study by Harvard economists Carmen Reinhart and Kenneth Rogoff may have overstated the impact that debt burdens have on a nation’s economic growth".

Following the link in Olshan's article, the blogs.marketwatch.com article states:  "That’s a conclusion reached by Carmen Reinhart and Kenneth Rogoff in this landmark 2010 paper, and it’s cited nearly everywhere.  Except … it might be wrong — in part due to an Excel spreadsheet error.  A blog by the Roosevelt Institute details a new research paper critiquing Reinhart and Rogoff.  According to the blog, the Excel error excluded Australia, Austria, Belgium, Canada, and Denmark from their analysis, which causes the average GDP, once debt-to-GDP exceeds 90%, to be 0.3 [sic] percentage points worse than it should be".

Answer 4

We just came across this error also, but with fairly small numbers.  My son had to do a compounding interest calculation for 20 years with a starting balance of 10,000 at 2.1% interest.  There were several calculation errors in the spreadsheet.  We did set the cells to currency with 2 decimal places before putting in the initial values.