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...o&userid=31070
View this thread: http://www.excelforum.com/showthread...hreadid=507477

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"
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...o&userid=31070
View this thread: http://www.excelforum.com/showthread...hreadid=507477

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

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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"
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...o&userid=31070
View this thread:
http://www.excelforum.com/showthread...hreadid=507477

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.


--
tinybunny_8
------------------------------------------------------------------------
tinybunny_8's Profile: http://www.excelforum.com/member.php...o&userid=31070
View this thread: http://www.excelforum.com/showthread...hreadid=507477

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

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

"tinybunny_8"
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...o&userid=31070
View this thread: http://www.excelforum.com/showthread...hreadid=507477

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.


--
tinybunny_8
------------------------------------------------------------------------
tinybunny_8's Profile: http://www.excelforum.com/member.php...o&userid=31070
View this thread: http://www.excelforum.com/showthread...hreadid=507477

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 .

Out of curiousity,

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


--
tinybunny_8
------------------------------------------------------------------------
tinybunny_8's Profile: http://www.excelforum.com/member.php...o&userid=31070
View this thread: http://www.excelforum.com/showthread...hreadid=507477

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.