Windows 7 Interesting Difference in Math Calculations in different programs

[SteveH]

I noticed a difference in the results of a math problem I did in 4 separate programs, and they split 2 to 2 on the result. I multiplied the following 2 numbers in Windows Calculator, a Firefox add-on calculator, Libre Office Calc and MS Excel with interesting resuts :

82.19777664*33.20088775

Results
Windows Calculator = 2,729.039155524212
Firefox Add-on Calculator = 2729.039155524212
Libre Office Calc = 2,729.039155524210
Microsoft Excel 2010 = 2,729.039155524210

As you can see, the calculators agree on the numbers and the spreadsheet programs agree on the numbers, but as you can also see the 2 different types of programs agreed on different numbers - it appears the spreadsheets rounded the last number down to zero.

I allowed for 12 decimal places in the spreadsheet programs, I even added decimal places to 14 or 15 and I got the same results - with additional trailing zero's in the additional decimal places in the spreadsheet programs.

Anyone have any idea how and why these programs are handling the math this way? Thanks for any insight anyone can shed on this riddle

 

[cliffordcooley]

The answer you are looking for deals with numeric precision.

Numeric precision in Microsoft Excel

As with other spreadsheets, Microsoft Excel works only to limited accuracy because it retains only a certain number of figures to describe numbers (it has limited precision). Excel nominally works with 8-byte numbers by default, a modified 1985 version of the IEEE 754 specification[1] (Besides numbers, Excel uses a few other data types.[2]) Although Excel can display 30 decimal points, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to three issues: round off,[3] truncation, and binary storage.

Link Removed

Maximum/Minimum Limitations
All computers have a maximum and a minimum number that can be handled. Because the number of bits of memory in which the number is stored is finite, it follows that the maximum or minimum number that can be stored is also finite. For Excel, the maximum number that can be stored is 1.79769313486232E+308 and the minimum positive number that can be stored is 2.2250738585072E-308.

IEEE floating point - (look closely at the number of digits for each level of precision.)

 

[SteveH]

Thanks cliffordcooley, that's an interesting explanation, a great answer to my question

 

[patcooke]

An interesting bit of trivia arising out of this question - it is not many years since a pc asked to calculate 1/7 would give the output as 0.142857142857142 . . . . . (recurring ad inf). If the answer were then multplied by 7 it responded with 0.9999999 . . . . which is understandable. Combining the two stages into one however, 1/7*7 gave the same result 0.99999. . .!

 

[SteveH]

That is a very interesting piece of trivia patcooke, I've already learned a couple of interesting facts from this post, thanks for that one

 

[Joe S]

I'm retired and used to run a CNC machine and in a few instances the rounding caused some real frustration. The machine did it's thing and the prgram they used to create the machine's program did it's job and occasionally there was a puzzling fault that stopped the program. That and the machine's probe used 8 places and displayed values rounded back to 4 but calculated with the 8. My day man and I eventually things figured out and shared the info freely. The programers were pretty clueless!
Joe