Leo Heuser said:
Use the custom numberformat:
#,;#,
A comma with no placeholders following
designates a "thousands group". E.g.
#,,;#,,
formats to whole millions.
--
Best Regards
Leo Heuser
MVP Excel
Followup to newsgroup only please.
Can I just add a couple of points please Leo.
1. The above just alters the display, not the underlying numbers. This means
that column totals may not be the same as the sum of the displayed values.
(For example, 10,500 displays as 10,000, similarly 20,500 as 20,000 but they
total to 31,000.)
2. You can use the option to use precision as displayed (Tools ... options
....). This makes sure everything adds up right. However, this alters the
underlying numbers to the same as the display, so it is a bit draconian, as
you can never get your original precision back again. I would only want to
apply this to a copy of my main spreadsheet.
3. You can use the round function. Round(123456789, -3) rounds to the
nearest thousand and Round (123456789,-6) rounds to the nearest million. If
instead you use Round(123456789, $Z$1), you can store the precision you want
in cell Z1 and change the roundings just by altering Z1 from -3 to -6 and
back.
4. Does anyone know if there is any simple way of linking a custom format to
a value in a cell - conditional formatting would do it, of course - but I
was wondering if you can set the custom format up as a string in a cell and
then refer to that?
Geoff
