Excel 2003 number display

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

In Excel 2003, if my column width is too narrow, a number e.g. 11.75 will
display as 11.8, instead of giving the # marks familiar from Excel 2000. I
WANT my users to see the # marks, otherwise they believe the 11.8. Is there
a way to force Excel to display the # marks - possibly a custom formatting
of the cell?
TIA,

Dan
 
Dan

Format the cell as Number with more decimal places.

Try a half dozen or so until you get the ####


Gord Dibben Excel MVP
 
Dan,

One thing to consider: There's an implied precision with the number of
decimals you show. If it's formatted for two places, than (e.g.) 11.5 would
show as 11.50, where the precision of the number may be only to 11.5. 11.5
isn't quite the same as 11.50, unless you're ignoring implied precision.
Or, another case, if it's formatted to show 11.750, it implies precision to
the third decimal place which may not actually be there. And the 11.5 would
show as 11.500, again implying yet another magnitude of precision. It
really depends partly on how the numbers are to be interpreted. If all the
numbers have precision to the same number of decimals, then fixed
zeroes-formatting will be fine. But if not, then you have the consideration
I mention. In that case, it might be better to make the column wide enough
to show the precision of the numbers as entered, and leave the number
formatting as General to let the precision float (sort of).

By the way, Excel97 does the same rounding as you mention 2003 does. So
does 2002. I'd think Excel 2000 does it too.
 
Thanks, Earl. Yeah, I'm familiar with precision issues - lecturing to
physics and engineering students in an Australian university gave me plenty
of practice in trying to get them to appreciate implied precision etc...
Great fun. Excel 2000 may do it, though I've never noticed it, and I used
2000 for years. Then (in 2003) I noticed a number that wasn't quite as it
should be, widened the column, and behold, I had 11.75, not the 11.8 which
was displayed when the column was too narrow. It's basically misleading,
which is why I'd prefer Excel to default to #'s if the number can't be
displayed. And of course sometimes real people write down numbers from the
screen or a print-out, and add them up....

Dan
 
Back
Top