Format numbers to millions

T

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
 
N

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]...
 
N

Niek Otten

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

--
Kind regards,

Niek Otten

Niek Otten said:
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
Click to expand...
Click to expand...
 
T

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.
 
T

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.
 
M

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 .
 
T

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
 
M

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.
 
T

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!!!
 
Top