I have data in 2 columns that I want to work with. In one column (O) is a
percentage that sometimes equals 0. In the other (P) is a percentage that
never equals 0. Now in the third column (Q), I have the function =(P2-O2)/O2,
but sometimes, when O=0%, the function doesn't work. So I changed it to say
=IF((O2=0%),200%,(P2-O2)/O2). I never want the Q column to be more than 200%.
It works for numbers that quotient to more than 200%, but for functions where
O=0%, it says #DIV/0!, where it should just say 200%, as I've instructed it.
What should I do?

Sorry, I meant that the equation I made was
=IF(OR(O2=0%,(P2-O2)/O2200%),200%,(P2-O2)/O2)

"KidMW14" wrote:

I have data in 2 columns that I want to work with. In one column (O) is a
percentage that sometimes equals 0. In the other (P) is a percentage that
never equals 0. Now in the third column (Q), I have the function =(P2-O2)/O2,
but sometimes, when O=0%, the function doesn't work. So I changed it to say
=IF((O2=0%),200%,(P2-O2)/O2). I never want the Q column to be more than 200%.
It works for numbers that quotient to more than 200%, but for functions where
O=0%, it says #DIV/0!, where it should just say 200%, as I've instructed it.
What should I do?

On Sat, 12 Nov 2005 20:06:02 -0800, "KidMW14"
wrote:

Sorry, I meant that the equation I made was
=IF(OR(O2=0%,(P2-O2)/O2200%),200%,(P2-O2)/O2)

"KidMW14" wrote:

I have data in 2 columns that I want to work with. In one column (O) is a
percentage that sometimes equals 0. In the other (P) is a percentage that
never equals 0. Now in the third column (Q), I have the function =(P2-O2)/O2,
but sometimes, when O=0%, the function doesn't work. So I changed it to say
=IF((O2=0%),200%,(P2-O2)/O2). I never want the Q column to be more than 200%.
It works for numbers that quotient to more than 200%, but for functions where
O=0%, it says #DIV/0!, where it should just say 200%, as I've instructed it.
What should I do?


The problem is that within your initial OR function, which gets executed every
time, you are dividing by 0, hence the error.

Try something like:

=if(o2=0,200%,min(200%,(p2-o2)/o2))


--ron

Hi!

Try this:

=IF(ISERROR((P2-O2)/O2),200%,IF((P2-O2)/O2200%,200%,(P2-O2)/O2))

Biff

"KidMW14" wrote in message
...
I have data in 2 columns that I want to work with. In one column (O) is a
percentage that sometimes equals 0. In the other (P) is a percentage that
never equals 0. Now in the third column (Q), I have the function
=(P2-O2)/O2,
but sometimes, when O=0%, the function doesn't work. So I changed it to
say
=IF((O2=0%),200%,(P2-O2)/O2). I never want the Q column to be more than
200%.
It works for numbers that quotient to more than 200%, but for functions
where
O=0%, it says #DIV/0!, where it should just say 200%, as I've instructed
it.
What should I do?