How accurate is Excel (2000)

[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

 

[Harlan Grove]

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

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.

 

[Peo Sjoblom]

Excel has 15 digits precision, everything after that will be truncated

http://www.cpearson.com/excel/rounding.htm

 

[Gord Dibben]

MH

Excel will round off at 15 significant digits. That's the limit.

For more reading on this subject.......

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

If you want/need more than 15 digits try.....

http://precisioncalc.com/

Gord Dibben XL2002

 

[Dana DeLouis]

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

 

[Mike Hyndman]

Many thanks to all for the information.
MH

remove _bats_ to reply