How do i........?

[R P McMurphey]

get excel to show negative figures as nil or zero?

thanks

Steve

 

[Harald Staff]

Hi Steve

Menu Format > Cells, choose Custom, enter
0,"0"
in the text field there. The cells will keep their negative values and use
them further calculations.

 

[Steve]

Format the cell, using menus Format --> Cell --> Number --> Category
and then select Custom and enter this


#,##0.00;;

With this only positive numbers will be displayed.

The first bit is how positive numbers and zeros will be displayed. The second
bit is for negative numbers and the last bit is for zero. Play around with that
until you get what you want.

Steve.

R P McMurphey wrote

 

[R P McMurphey]

just tried it...it is now displaying -110 so not working as hoped...am I
missing sommat?

Steve

 

[R P McMurphey]

that seems to work...but can I get it to display "nil" or "Zero" or "00.00"
instead?

bestest regards

Steve

 

[R P McMurphey]

I got it now....

#,##0.00;Nil;

did the trick! thanks all!

Steve

 

[Harald Staff]

Seems like it should be a semicolon and not a comma. Sorry.

 

[Steve]

That will make it display positive numbers with a comma separating the
thousands and with two decimal places. Negative numbers will be displayed
as Nil and zero values will not be displayed at all. If you want to display
zero values too then just add the formal for them after that final semicolon.

Steve.

R P McMurphey wrote

 

[R P McMurphey]

what do you mean by add the formal?

Steve

That will make it display positive numbers with a comma separating the
thousands and with two decimal places. Negative numbers will be displayed
as Nil and zero values will not be displayed at all. If you want to display
zero values too then just add the formal for them after that final semicolon.

Steve.

R P McMurphey wrote

 

[Steve]

I mean format - it was a typo.

#,##0.00;Nil;

Will display Nil for al negative numbers and nothing for all zeros. If you want
to display something for zeros then you add the format for that part after the
final semi colon.

Steve.


R P McMurphey wrote

 

[R P McMurphey]

You mean like

#,##0.00;Nil;Nil

?

steve

I mean format - it was a typo.

#,##0.00;Nil;

Will display Nil for al negative numbers and nothing for all zeros.
If you want to display something for zeros then you add the format
for that part after the final semi colon.

Steve.


R P McMurphey wrote

 

[Steve]

Yes.

Steve.


R P McMurphey wrote

You mean like

#,##0.00;Nil;Nil

?

steve

 

[wonderboy]

*get excel to show negative figures as nil or zero?

thanks

Steve *

Have you tried the conditional type of approaces?

Like:

=if(a1<0,0)

 

[Monica]

When doing a mail merge for address labels/form letters in Ms Word and
opening Excel for the data, many of the zipcodes come up as 0 rather
than what is in the Excel spreadsheet. Is there a way to prevent this
from happening?

 

[Peo Sjoblom]

Use text format for the zip codes..

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

--

Regards,

Peo Sjoblom

When doing a mail merge for address labels/form letters in Ms Word and
opening Excel for the data, many of the zipcodes come up as 0 rather
than what is in the Excel spreadsheet. Is there a way to prevent this
from happening?

wonderboy <[email protected]> wrote in message

creating financial statements