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(OR(O2=0%,(P2-O2)/O2200%),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?

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(OR(O2=0%,(P2-O2)/O2200%),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?

Hi KidMW14

Try this formula instead
=IF(A1=0,200,IF((B1-A1)/A1200,200,(B1-A1)/A1))


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=484615