Example:
A1=11978370000
A2=12171257
A3=A1/A2
A3 is GENERAL format.
You can double the width even 90 or 160 but it doesn't display anything better than
984.1522531 (10 significant digits) (FYI 7 decimal places here, but the 10 is the operative number).
In other words, it doesn't display all 15 distinct significant digits, or even, let's say 14, because of the operation (division). They're "there"; if you manually increase the number of displayed digits (icon: the little left arrow and .0 over .00) it will work its way up to
984.1522531321130000... (the 15 sig digs I desired to see)(Yes, even though generally speaking only 14 are reliable here)
In fact, the first time you increase decimals on A3, General literally changes to "Number format displaying 8 decimal places".
984.15225313 (11 significant digits) (FYI 8 decimal places)
This is not a question about IEEE floating point precision. This is not a question about the 15. This is strictly about General deciding to show 10 significant digits when 15 distinct are available. And yes, I say distinct, because obviously General shows 1 significant digit if you divide 10 by 20. (By the way, that is theoretically treated as 2 significant digits, 0 and 5, in some applications. Don't get hung up on that technicality.)
It's as though General means "numeric, 10 digits; or less, if the number has less significant digits to display".
This seems to be the case since long ago. Can that "10 or less for General" be changed? Is it always "10(or less)"? I never found this in Excel Options; is there a back door for this?
It's disconcerting that I often will double the cell width to see if the the number is exactly {whatever is shown}, then undo, before using that number in a report or another workbook. For example I often use it as a "cheat" or crutch when I see something like 1.44 and I want to know if that number is really 1.44 "exactly" or is it 1.443. Now, that trick fails if there are 10 digits. You will see
123456789.4
when the actual number is
123456789.44
and it doesn't matter how wide you go. General just won't show that 44 cents. You'll have to change to Numeric and figure out how many decimal places to show before you decide "yes, it's 44 cents, not 44 and a half". That can be a tedious task. I'd like to see the max! Duh, right?
Bookmarks