Format numbers to millions

[tinybunny_8]

Hi there,

I saw an article in this web about formatting a number to million usin
the following format:
#,##0,, ;[Red](#,##0,,);- ;

So, if I have 1,777,777 I will get 2. However, what if I want t
display 2,000,000 instead of only 2? How can I do that? Thank you

 

[Niek Otten]

Format Custom as

#,##0,,",000,000"

(no provisions for negative amounts yet)

But are you sure you need this? I don't like the idea of displaying
something that is not the actual amount in the cell. What is your
requirement, if I may ask?

"tinybunny_8" <[email protected]>
wrote in message
news:[email protected]...

 

[Niek Otten]

No good. This assumes the amount is actually > a million.

--
Kind regards,

Niek Otten

Format Custom as

#,##0,,",000,000"

(no provisions for negative amounts yet)

But are you sure you need this? I don't like the idea of displaying
something that is not the actual amount in the cell. What is your
requirement, if I may ask?

"tinybunny_8" <[email protected]>
wrote in message

Hi there,

I saw an article in this web about formatting a number to million using
the following format:
#,##0,, ;[Red](#,##0,,);- ;

So, if I have 1,777,777 I will get 2. However, what if I want to
display 2,000,000 instead of only 2? How can I do that? Thank you.


--
tinybunny_8
------------------------------------------------------------------------
tinybunny_8's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=31070
View this thread:
http://www.excelforum.com/showthread.php?threadid=507477

 

[tinybunny_8]

Nick,

THANK YOU for replying to my question!!! Well, sometimes this kind of
formatting is required in real-world cases. For instance, if you have a
100 billion dollar business, you just want to see a rounding number in
the report, you don't care much about the thousands.

 

[Bernard Liengme]

Have you thought of =ROUND(your-formula,-6) ?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tinybunny_8" <[email protected]>
wrote in message
news:[email protected]...

 

[tinybunny_8]

Hi Bernard,

I can't use round function because I still want to retain the original
number (1,777,777) but just want to display it differently to the user.
With round, i have to put the new values somewhere else.

 

[Mark Lincoln]

You could set up a report worksheet that's set up the same way as the
original worksheet, with each cell referring to its corresponding
original sheet using whatever rounding formula you need.

For instance, if you have a sheet "Data" and a sheet "Report," Cell B3
in "Report" could have the following formula:

=Round(Data!B3,-6)

which would display your 1,777,777 as 2,000,000 .

 

[tinybunny_8]

Out of curiousity,

It seems to me that everyone is against formatting and more leanin
toward using round function, would anyone enlighten me about this

 

[Mark Lincoln]

As Niek Otten succinctly put it, "I don't like the idea of displaying
something that is not the actual amount in the cell." For one thing,
trying to audit such figures is impossible without selecting every cell
to see the underlying figures, and thus can't be done at all with a
printout of the figures.

If you see 2,000,000 in each of ten cells, you expect a total of
20,000,000. If each cell's actual value is 1.5 million (which would
*display* as "2,000,000"), then a displayed total for the ten cells
would be 15 million. Without seeing the actual figures, your
spreadsheet could appear off by 25%. Not good.

 

[tinybunny_8]

Mark,

Thnk you for your comment, now I see your concern. That's a really good
point! Got me to think. THANKS A LOT!!!