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:
April 9, 2024
Click here to learn more 💡
May 10, 2024
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:
Reported content has been submitted
Reported content has been submitted
2 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
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.”
http://support.microsoft.com/kb/42980
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.
http://support.microsoft.com/kb/214118
SymptomsMany 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.
CauseThis 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.
http://office-watch.com/t/n.aspx?a=1854
An Excel mistake caused a change in economic policy. A user mistake or Microsoft's?
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.)
**********************
The question of rounding introduces a whole other "religious war". Precisely what form of rounding is "right".
Here are some articles on rounding
http://chandoo.org/wp/2012/09/28/round-numbers-excel-formulas/
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.)
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.)
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.)
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.
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.
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?
Reported content has been submitted
5 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
88% of spreadsheets have errorshttp://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
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".
Reported content has been submitted
1 person found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Reported content has been submitted
1 person found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.