Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel - display zeros for negative values

Nov 24, 2005 10:15AM PST

I am using formulae to generate values in Excel . How can I have the cell show zeros when it's a negative value ?

Discussion is locked

- Collapse -
Rather simple, actually;
Nov 24, 2005 11:04AM PST

just include an IF in your formula. For example, if , F4 contains Formula1

change it to =IF(Formula1<0, 0, Formula1)

This will check if the result is less than zero; if so, it will insert a zero, if not, it will calculate the actual result.

If your original formula is quite lengthy, this new formula will, of course, be twice as lengthy. An alternative would be to store the raw result in another location, perhaps a hiden one, then test that result in the desired location (F4 in my example).

Hope this helps

Frank

- Collapse -
Got it , thanks !
Nov 24, 2005 12:36PM PST

Thanks .

- Collapse -
(NT) (NT) Glad I could help
Nov 24, 2005 11:35PM PST