How accurate is Excel (2000)

  • Thread starter Thread starter Mike Hyndman
  • Start date Start date
M

Mike Hyndman

I tried to use Excel 2000 (to "amaze" a class of ten year olds)to show
that 111,111,111 squared is 12,345,678,987,654,321 but the calculation
rounded itself down to 12,345,678,987,654,300. Is this a bug in Excel or
is it due to the limitation of "rounding". If so, are there any other
calculations that may only be "approximations"? The Windows calculator
applet has no problem with this calc, so why should something as
powerful as Excel struggle with it?
Just a thought
MH
remove _bats_ to reply
 
I tried to use Excel 2000 (to "amaze" a class of ten year olds)to show
that 111,111,111 squared is 12,345,678,987,654,321 but the calculation
rounded itself down to 12,345,678,987,654,300. Is this a bug in Excel or
is it due to the limitation of "rounding". If so, are there any other
calculations that may only be "approximations"? The Windows calculator
applet has no problem with this calc, so why should something as
powerful as Excel struggle with it?
Just a thought
Click to expand...

Per online help, Excel is limited to 15 decimal digits of precision. This is
only slightly less than IEEE double precision, which itself provides fewer than
a full 16 decimal digits of precision. You should have used Mathematica.

With respect to the Calculator applet, it uses various forms of extended
precision numeric types. In short, while the Calculator applet does a lot less
than Excel, what it does do is more numerically sophisticated than the same
operations in Excel. However, you should be aware that these more sophisticated
numerics in the Calculator applet come at the cost of much, much slower
calculation. Since the Calculator applet would be used interactively, it's I/O
bound, meaning the calculation time is still insignificant compared to the time
it takes to enter numbers and operations with a keyboard. On the other hand,
Excel could (in theory) be called upon to perform several million calculations
in a single recalc cycle that involve no additional user interaction, so Excel
is processor bound, so calculation speed is far more important, so the trade-off
between speed and precision tilts the other way for Excel.
 
Excel can only do this directly with VBA:

Sub Demo()
'// Dana DeLouis
Dim n
n = 111111111

Debug.Print FormatNumber(n, 0, , , vbTrue) & " Squared is: " &
FormatNumber(CDec(n) * n, 0, , , vbTrue)
End Sub

Returns...

111,111,111 Squared is: 12,345,678,987,654,321

HTH
 
Back
Top