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

  • Follow


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.
0
Reply Utf 1/15/2010 3:07:01 AM

Maybe something like this...

=MAX(0,ROUND(A1/A2,1))

-- 
Biff
Microsoft Excel MVP


"mike" <mike@discussions.microsoft.com> wrote in message 
news:74196BD1-586C-47B9-BAFE-C0D895666B9A@microsoft.com...
>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. 


0
Reply T 1/15/2010 3:13:58 AM

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))


-- 
Regards,

OssieMac


"mike" wrote:

> 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.
0
Reply Utf 1/15/2010 3:33:01 AM

2 Replies
964 Views

(page loaded in 0.056 seconds)


Reply: