MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Showing Negative Numbers As Zero's

Posted by Laurie on July 26, 1999 12:57 PM

I am looking for a way in a column of calculations to
show the values as Zero's if the calculation is negative. Can anyone help?


Posted by Ben Prescott on July 27, 1999 4:02 AM

Assuming your calculation is represented by
%sum%, try this:

---a--- b --c--

a - checks if the figure is negative
b - the value to be displayed if it is negative
c - what to do if it isn't - the sum

Posted by AJ on July 27, 1999 1:31 PM

Fill in A-1 throught 7 with each number
Now in cell B-1 type in =IF((A1*7)>0,(A1*7),"0")
Then you just fill down from B-1 to B-7
Example cell A-1 is 1, so 1 times 7 = 7 and
7 is greater than zero so its going to let it stay.
Example 2 cell A-3 is -5, so -5 times 7 = -5 and
-5 is less than zero so its going to put zero in there.

Posted by AJ on July 27, 1999 1:33 PM

Posted by Jaime on February 29, 2000 7:23 AM

Actually instead of an array function u can just pop this format into the custom number format. That is under format cells, select the format number option than goto custom format and place this in the type box: