Custom number format

[Guest]

Hello:

I have Excel 2003 and can't figure out the custom number format to change a
number such as 1,234,567 to 1; or 123,567,890 to 124. This is for accounting
spreadsheets where we report "$ in millions" with no decimal places.

I currently have the following two formats, but they each convert the
example numbers above to thousands -- such as: 1,234,567 becomes 1,235; and
123,567,890 becomes 123,568:

format with dollar sign: _($* #,##0,_);_($* (#,##0,);_($* "-"_);_(@_)
format w/o dollar sign: _(* #,##0,_);_(* (#,##0,);_(* "-"_);_(@_)

How would I change the formats to convert to millions?

Thank you

 

[Guest]

format with dollar sign: _($* #,##0,,_);_($* (#,##0,,);_($* "-"_);_(@_)
format w/o dollar sign: _(* #,##0,,_);_(* (#,##0,,);_(* "-"_);_(@_)

 

[Guest]

Thank you so much! One other quick thing - how would I change the formats to
show a dash, rather than a zero for when it rounds, for instance, 499,999
down to zero?

 

[Guest]

Not easily in 2003, does this column contain a formula or a value? If it's a
value, you could use a helper column to display values the way you want them
and hide the originals or if it's a formula already, can modify the formula
to display as you'd like. But if you do that it will change the actual value
stored in the cell, not just the display, as your custom format does. You
could call it a benefit, with the dashes displaying only when you have an
actual zero and zero's displaying when it's only rounded to zero, you can
then tell at a glance which is which?
Let me know what's currently stored in that column and/or which way you want
to go and I'll be happy to help you come up with something!

 

[Guest]

Thanks for your reply, BoniM. Sorry for the delay on my end -- I was off for
a few days. I think that, at this point, I won't go any further with this
because I typically get files from others with both types of information --
that is, it could be a formula or it could be a value.

Thanks again and I apologize for any waste of your time. 'twas not intended.