Curious general format rounding

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

I am running some VBA in Excel 2003.
I have a couple of cells. Each has General format and is empty.
I write "1.5" to both.
In one case the cell is bold and the value is rendered as "2".
If I remove bold, the value is rendered as "1.5".
In the other, the cell is not bold and the value is rendered as 1.5.

Ah ha. It is a question of room to fit the value.

Let me see if I can construct a simple macro to show the behavior.

In a new workbook, step through the following code:
Columns("A:A").ColumnWidth = 1 ' Make [A1] narrow
ActiveCell = "1.5" ' [A1] is rendered as "2"
Columns("A:A").ColumnWidth = 3 ' [A1] is rendered as "1.5"

I would appreciate a pointer to appropriate documentation.

Later!
It is documented in <http://support.microsoft.com/kb/182197>
Now I understand what is happening, I can adjust to it.
 
Walter Briscoe said:
I have a couple of cells. Each has General format and is empty.
I write "1.5" to both. In one case the cell is bold and the
value is rendered as "2". If I remove bold, the value is
rendered as "1.5". [....]
Ah ha. It is a question of room to fit the value. [....]
It is documented in <http://support.microsoft.com/kb/182197>
Click to expand...

You seem to have answered your own question. As you note, with the General
format, the format of the number (and its rounded appearance) depends only
on the width of the column, not the font style (e.g. bold).

The article neglects to mention that under some conditions, the value will
be displayed in Scientific form, e.g. 1.23E-12. In part, that depends on
cell width. But even if a General-formatted cell is wide enough, Excel will
format only up to 10 or 11 significant digits in Number form (e.g. 1.23),
depending on whether or not the value is an integer.

Some other heuristics might also apply, especially when the value is close
to zero. I have never seen any documentation on these details.

Also, the article is incomplete in its description of the Precision As
Displayed calculation option (PAD).

Unlike other numeric formats, it is true that PAD does __not__ round
General-formatted values to their displayed precision, as the article
explains.

However, PAD __does__ round General-formatted values to 15 significant
digits.

Consider the difference in the MATCH result in the following, with and
without setting PAD and with all cells formatted as General:

A1: 1.15
B1: =3*A1
C1: 3.45
D1: =MATCH(B1,C1,0)
 
In message said:
Walter Briscoe said:
I have a couple of cells. Each has General format and is empty.
I write "1.5" to both. In one case the cell is bold and the
value is rendered as "2". If I remove bold, the value is
rendered as "1.5". [....]
Ah ha. It is a question of room to fit the value. [....]
It is documented in <http://support.microsoft.com/kb/182197>
Click to expand...

You seem to have answered your own question. As you note, with the
Click to expand...

I intended to do so. I failed to find an answer in microsoft.public.exce
l.programming and decided to post both question and answer.
General format, the format of the number (and its rounded appearance)
depends only on the width of the column, not the font style (e.g.
bold).
Click to expand...

I either don't understand what you mean or disagree. For a given width
of the column, the width of the data depends on the font style. (e.g.
bold). If the default width of the data is greater than that of the
column, rounding is applied.
The article neglects to mention that under some conditions, the value
will be displayed in Scientific form, e.g. 1.23E-12. In part, that
depends on cell width. But even if a General-formatted cell is wide
enough, Excel will format only up to 10 or 11 significant digits in
Number form (e.g. 1.23), depending on whether or not the value is an
integer.
Click to expand...

I will take your word for that. I rarely have numbers with more than 2
decimal digits.
Some other heuristics might also apply, especially when the value is
close to zero. I have never seen any documentation on these details.
Click to expand...

I infer you have not interpolated documentation from observed behavior.
Also, the article is incomplete in its description of the Precision As
Displayed calculation option (PAD).

Unlike other numeric formats, it is true that PAD does __not__ round
General-formatted values to their displayed precision, as the article
explains.

However, PAD __does__ round General-formatted values to 15 significant
digits.

Consider the difference in the MATCH result in the following, with and
without setting PAD and with all cells formatted as General:

A1: 1.15
B1: =3*A1
C1: 3.45
D1: =MATCH(B1,C1,0)
Click to expand...

I did not know what you expected me to see.
I saw D1 as 1 when Tools/Options/Precision as displayed was checked.
I saw N/A when it was unchecked.

Let me try to deduce if that is what was intended.
I take it that 3.45 is held as a floating point number (cf. <http://en.w
ikipedia.org/wiki/Floating_point_number>) and can't be held precisely. I
also take it that 3*1.15 is not held in the same way as 3.45. i.e. MATCH
should fail. The help says "If MATCH is unsuccessful in finding a match,
it returns the #N/A error value." I infer I saw what you intended.

Thank you for the help.
I now understand some of the dangers of using general format for cells.
I am glad Tools/Options/Precision as displayed is unchecked by default.
 
Back
Top