software

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: www.excelforum.com/member.php...oamp;userid=31070
View this thread: www.excelforum.com/showthread...hreadid=507477Format Custom as

#,##0,,quot;,000,000quot;

(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?

quot;tinybunny_8quot; gt;
wrote in message
...
gt;
gt; Hi there,
gt;
gt; I saw an article in this web about formatting a number to million using
gt; the following format:
gt; #,##0,, ;[Red](#,##0,,);- ;
gt;
gt; So, if I have 1,777,777 I will get 2. However, what if I want to
gt; display 2,000,000 instead of only 2? How can I do that? Thank you.
gt;
gt;
gt; --
gt; tinybunny_8
gt; ------------------------------------------------------------------------
gt; tinybunny_8's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31070
gt; View this thread: www.excelforum.com/showthread...hreadid=507477
gt;
No good. This assumes the amount is actually gt; a million.

--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message
...
gt; Format Custom as
gt;
gt; #,##0,,quot;,000,000quot;
gt;
gt; (no provisions for negative amounts yet)
gt;
gt; But are you sure you need this? I don't like the idea of displaying
gt; something that is not the actual amount in the cell. What is your
gt; requirement, if I may ask?
gt;
gt; quot;tinybunny_8quot; gt;
gt; wrote in message
gt; ...
gt;gt;
gt;gt; Hi there,
gt;gt;
gt;gt; I saw an article in this web about formatting a number to million using
gt;gt; the following format:
gt;gt; #,##0,, ;[Red](#,##0,,);- ;
gt;gt;
gt;gt; So, if I have 1,777,777 I will get 2. However, what if I want to
gt;gt; display 2,000,000 instead of only 2? How can I do that? Thank you.
gt;gt;
gt;gt;
gt;gt; --
gt;gt; tinybunny_8
gt;gt; ------------------------------------------------------------------------
gt;gt; tinybunny_8's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=31070
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=507477
gt;gt;
gt;
gt;

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: www.excelforum.com/member.php...oamp;userid=31070
View this thread: www.excelforum.com/showthread...hreadid=507477Have you thought of =ROUND(your-formula,-6) ?

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

quot;tinybunny_8quot; gt;
wrote in message
...
gt;
gt; Hi there,
gt;
gt; I saw an article in this web about formatting a number to million using
gt; the following format:
gt; #,##0,, ;[Red](#,##0,,);- ;
gt;
gt; So, if I have 1,777,777 I will get 2. However, what if I want to
gt; display 2,000,000 instead of only 2? How can I do that? Thank you.
gt;
gt;
gt; --
gt; tinybunny_8
gt; ------------------------------------------------------------------------
gt; tinybunny_8's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31070
gt; View this thread: www.excelforum.com/showthread...hreadid=507477
gt;

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: www.excelforum.com/member.php...oamp;userid=31070
View this thread: www.excelforum.com/showthread...hreadid=507477You 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 quot;Dataquot; and a sheet quot;Report,quot; Cell B3
in quot;Reportquot; 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: www.excelforum.com/member.php...oamp;userid=31070
View this thread: www.excelforum.com/showthread...hreadid=507477As Niek Otten succinctly put it, quot;I don't like the idea of displaying
something that is not the actual amount in the cell.quot; 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 quot;2,000,000quot;), 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.
Mark,

Thnk you for your comment, now I see your concern. That's a really good
point! Got me to think. THANKS A LOT!!!--
tinybunny_8
------------------------------------------------------------------------
tinybunny_8's Profile: www.excelforum.com/member.php...oamp;userid=31070
View this thread: www.excelforum.com/showthread...hreadid=507477