Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide two cells and if the number is negative display is as zero
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide two cells and if the number is negative display is as zero
Maybe something like this...
=MAX(0,ROUND(A1/A2,1)) -- Biff Microsoft Excel MVP "mike" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide two cells and if the number is negative display is as zero
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Negative Number Display -- need Brackets | Excel Discussion (Misc queries) | |||
Divide a range of cells by a number | Excel Discussion (Misc queries) | |||
display negative number as zero | Excel Discussion (Misc queries) | |||
how do you divide the average of a number into 3 different cells. | New Users to Excel | |||
How do I change the display of a negative number to just read 0? | Excel Discussion (Misc queries) |