Divide two cells and if the number is negative display is as zero

M

mike

I have an excell spread sheet and i am dividing two cells. Most of the
numbers are positve but some of the numbers are negative. I want to put a
formula in to all the cells that will divide the two cells and give me the
answer to one digit but if the number is negative i want it to display as 0.
 
O

OssieMac

Hi Mike,

Following assumes that A1 is divided by B1

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

However, if you have any zeros in column B then you will get #DIV/0! error
so you might want to expand the formula and put "" in lieu of the error.
=IF(ISERROR(A1/B1),"",IF(A1/B1<0,0,A1/B1))

If you want zero instead of "" if dividing by zero then replace the "" with
zero.
=IF(ISERROR(A1/B1),0,IF(A1/B1<0,0,A1/B1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count the maximum number of consecutives? 9
if negative figure 8
Need formula to change cells from negative numbers to a zero. 2
Rounding Negative Numbers to Zero 4
Divide by the minimum non-zero number in a range of cells 21
Excell - two conditions count if factors are true when negative nu 2
formula(excel) to skip cells having - number and add + one 1
formula to a minimum value of zero if a negative number 2

Top