make excel 2000 result change from a negative number to zero 2

[malaize2]

My company has an Excel 2000 Speadsheet that subtracts one value from another and displays the results in another column. Sometimes the results from the subtraction produce a negative number. Is there any way to have this negative number automatically change to a 0?

Thanks,

 

[xlhelp]

A good way is Custom Format the cell(s). That way the number integrity is still there and displays as Zero.

One costom format choice might be

#,##0.00;0;0

Stupidity has no handicap

 

[xlhelp]

It's really bad when you prove your own signature.

Anyway,

#,##0.00;"0";0


or leave out the last ;0

Stupidity has no handicap

 

[Lilliabeth]

xlhelp, you are always proving your username, not your signature!

On the other side of the coin, if you want to really have a zero (and not preserve the number integrity), you can skip the formatting and use a formula instead:

=IF(A1-B1<0,0,A1-B1)

 

[malaize2]

Thanks for the replies xlhelp and Lilliabeth!

How can I make a Custom Format for the cells?

I tried just selecting the row and entering the formula from Lilliabeth but didn't know how to add it since there is already this in the function bar.

=SUM(-C6,E6)


thanks, malaize.

 

[Lilliabeth]

To use the format, Format>Cells and go to the Number tab and choose the category Custom. In the Type box, delete whatever is there and enter xlhelp's format: #,##0.00;"0";0

Alternatively, skip the format and replace the existing formula with =IF(E6-C6<0,0,E6-C6)

Before you do either, you should decide what you really want in the cell.

If you want it to do math as a zero, use the formula.
If you want it to display a zero but do math as if it were still the negative number, use the format.

If you don't care, use either.

Formats do not change the contents of a cell. They just change how it looks.

 

[malaize2]

Thanks so much for the clarification Lilliabeth.

I have the spreadsheet working just the way I want it now.


malaize