Excel calculation glitches

Question

Why am I getting glitches in my calculations?

Glitch1: Two calculated values which supposed to equal the same value results in a difference of -6.245E-17 when subtracted.

Glitch2:

Z9 = 12.8 and AA9 = 13.2 (Plain values)

Z9-AA9 supposed to equal -0.4 and shows up as such in the answer cell. But when the answer is copied and pasted as a value in another cell, the value in the display bar above the Excel sheet shows -0.39999'.

Answer 1

From Chip Pearson (rip)...

[....]
These are 8-byte variables that can store numbers accurate to approximately 15 decimal places. You may have only two decimal places displayed on the worksheet, but the underlying value has the full 15 decimal places.

Pretty good description, except for that detail. Common sense: if stored numbers were "accurate to 15 decimal places", we shouldn't have any problem with numbers like 12.8 and 13.2.

Okay, Chip did say "approximately" 15 dp, which is better than most descriptions. But the point is: Excel __never__ stores numbers (in memory) with 15 significant digits (*), much less 15 "decimal places" (sic), approximate or otherwise.

Chip should have left it at: The first is that the numbers are not displayed to their full value, and The second problem arises from the fact that [the type Double binary form] cannot [represent] most fractional numbers with total accuracy.

For example, the exact decimal representations of the binary approximations of 12.8 and 13.2 are:

12.8000000000000,00710542735760100185871124267578125
13.1999999999999,99289457264239899814128875732421875

(I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats, rounded.)

---

(*) In fact, when Excel saves to an "xlsx" or "xlsm" file (any XML file), numbers are represented in text by an approximation with up to 17 significant digits. 17 sig dig is sufficient to convert between decimal and binary representations with no loss of precision, as the IEEE standard specifies. But that is still just an __approximation__. FYI, the IEEE standard __never__ mentions 15 (sic) significant digits, contrary to most descriptions.

Aside.... IMHO, 12.8 and 13.2 should be stored as "12.800000000000001" and "13.199999999999999", which are the correct 17 sig dig representations, rounded. But apparently, when the binary approximation of the number rounded to 15 sig dig and 17 sig dig is the same, Excel sometimes stores the 15 sig dig representation in the XML file, which is "12.8" and "13.2" in this case.

(EDIT) On second thought, I suspect -- wild speculation! -- that the deciding condition is whether the binary approximations are the same for the number in the General format, not rounded to 15 sig dig. For example, the 15 sig dig constant -0.399999999999999 is stored with the 17 sig dig string "-0.39999999999999902", even though they have the same binary approximation. The exact decimal representation is -0.399999999999999,023003738329862244427204132080078125.

Answer 2

Thanks for replying!

Answer 3

Thank you!

Answer 4

Hi, Computers work in binary, we work in decimals which results in approximations by Excel and any computer. Here is everything you need to know about this issue (and more): http://support.microsoft.com/kb/78113/en-us http://support.microsoft.com/kb/42980 http://support.microsoft.com/kb/214118 Chip's clear explanation: http://www.cpearson.com/excel/rounding.htm What Every Computer Scientist Should Know About Floating Point http://docs.sun.com/source/806-3568/ncg_goldberg.html http://www.mcgimpsey.com/excel/pennyoff.html Good reading from T Valko: http://blogs.msdn.com/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx (Complete) Tutorial to Understand IEEE Floating-Point Errorshttp://support.microsoft.com/kb/42980 Cheers, Shane Devenshire

Answer 5

re: maff

From Chip Pearson (rip)... http://www.cpearson.com/Excel/rounding.htm

There may be times that the value that you see on an Excel worksheet does not equal the value that you believe that it should be. There are generally two possible causes of this problem.

The first is that the numbers are not displayed to their full values. The second is a computer design issue. Neither of the two are "bugs" or problems with the design of Microsoft Excel or Windows.

Excel stores numbers differently than what you may have displayed on the worksheet. Under normal circumstances, Excel stores numeric values as "Double Precision Floating Point" numbers, or "Doubles" for short. These are 8-byte variables that can store numbers accurate to approximately 15 decimal places. You may have only two decimal places displayed on the worksheet, but the underlying value has the full 15 decimal places.

The second problem arises from the fact that a computer, any computer, cannot store most fractional numbers with total accuracy. Computers, in general, use the IEEE (Institute Of Electrical And Electronic Engineers) standard for floating point numbers. This standard provides a way to store fractional numbers in the limited space of an 8-byte number. Of course, for most numbers, some approximation must be made...

(article continues)

'---

NLtL https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (free excel programs)