Probably a dumb question, but why does Excel do this?

[John Bawls]

Try this:

1. Change the format of cell A1 to 'number' with no decimal places.
2. Enter 15 ones in cell A1 and hit enter: "111111111111111"

Excel will display "111111111111111" in the cell.

3. Now, enter 16 ones and hit enter: "1111111111111111"

Excel will display "1111111111111110" in the cell.

Why does Excel omit the last digit entered and put in a zero? This
works for any number with digits greater than 15. Rounding error? Is
the default value in a cell stored as a single precision variable
instead of a double, like in VBA? Is there a way around this?


John

 

[J.E. McGimpsey]

If you look in XL Help under "Specifications", you'll find that XL
only keeps approximately 15 decimal digits of precision.. it's a
limitation inherent in using IEEE Double Precision floating point
math, which almost all spreadsheets do.

A 16 digit number most usually refers to a credit card number, which
doesn't need to have math done on it. So it should be entered as
Text - either by preformatting the cell or by prefixing the entry
with an apostrophe.

If you're really doing math with 16 digit precision, while there are
a few packages around to extend XL's precision, you're probably
better off using a different application.

 

[J.E. McGimpsey]

If you're interested in the nuts and bolts, see

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