format cell to show zero if not a positive number

  • Thread starter Thread starter TechnoGram
  • Start date Start date
T

TechnoGram

How can I format a column of figures to show that if the result is
zero or negative amount it will just show a zero or be left blank.
I am attempting to show the difference between figures that have been
invoiced to figures that have been added and thus need to be invoiced.
 
Use a custom format like

#,##0.00_);;

or

#,##0.00_);"0";"0"

HTH,
Bernie
MS Excel MVP
 
Use a custom format like

#,##0.00_);;

or

#,##0.00_);"0";"0"

HTH,
Bernie
MS Excel MVP






- Show quoted text -
Click to expand...

Can I get a little more help on this. Not sure how to enter the
suggestion in my worksheet. Do I highlight the column and then have
to enter something?? Thanks
 
Select the cells that you want to format, then choose Format / Cells and select "Custom" under
category, and paste the string #,##0.00_);; into the edit box under Type:

HTH,
Bernie
MS Excel MVP


Use a custom format like

#,##0.00_);;

or

#,##0.00_);"0";"0"

HTH,
Bernie
MS Excel MVP






- Show quoted text -
Click to expand...

Can I get a little more help on this. Not sure how to enter the
suggestion in my worksheet. Do I highlight the column and then have
to enter something?? Thanks
 
Highlight the cells where you want this to apply, then click on Format
| Cells | Number tab and then click Custom (at the bottom of the
list). You will see a panel under Type:, and it is here that you need
to enter the codes that Bernie has given you, i.e.:

#,##0.00_);;

This will show a blank if the cell content is zero or negative, but
positive numbers will be shown with 2 decimal places, or:

#,##0.00_);"0";"0"

will show 0 if the number is zero or negative. You might like to
change the "0" to "0.00" in this case if you want the number of
decimals to be the same throughout. As Bernie has put _) at the end of
the positive format, positive numbers are slightly indented to the
left, so you might like to change this or the -ve/zero formats to be
the same, i.e.:

#,##0.00_);"0.00"_);"0.00"_)

Hope this helps.

Pete
 
Highlight the cells where you want this to apply, then click on Format
| Cells | Number tab and then click Custom (at the bottom of the
list). You will see a panel under Type:, and it is here that you need
to enter the codes that Bernie has given you, i.e.:

#,##0.00_);;

This will show a blank if the cell content is zero or negative, but
positive numbers will be shown with 2 decimal places, or:

#,##0.00_);"0";"0"

will show 0 if the number is zero or negative. You might like to
change the "0" to "0.00" in this case if you want the number of
decimals to be the same throughout. As Bernie has put _) at the end of
the positive format, positive numbers are slightly indented to the
left, so you might like to change this or the -ve/zero formats to be
the same, i.e.:

#,##0.00_);"0.00"_);"0.00"_)

Hope this helps.

Pete





- Show quoted text -
Click to expand...

Exactly what I was looking for and needed, with the addition of Pete
for future differences in how the numbers show. Thank you both,
excellent help. Must say however I had a bit of a heart stop when the
end result did not just magically appear after the custom format input
LOL silly me forgot to put the =sum() formula in to generate the
answer......
 
Back
Top