Excel - multiple if and cell range contains specific text

[dtrom1]

Hi

I have the following formula and would like to include in the if statement a criteria that looks for the value "cat" in cell range P2:P500.

=IF(OR((((N4-N3)/N3)*100)>50,N4<5),1,0)

Therefore, the revised formula should return a value of 1 if any of the following statements are met:

• ((N4-N3)/N3)*100 is greater than 50
• N4 is less than 5
• A text value of "cat" is present in any of the cells ranging P2:P500

Grateful if anyone can help me finish this formula to include the last point about.

Thank you

 

[jtakw]

Hi,

Is it possible for N3 to be Blank ?

 

[dtrom1]

Hi,

Is it possible for N3 to be Blank ?

Yes it is

 

[jtakw]

Yes it is

Try this, assume N3 can be blank, but Not 0:

=OR(IF(N3="",0,(N4-N3)/N3*100>50),N4<5,COUNTIF(P2:P500,"cat"))+0

If N3 Can be Blank or 0, then use this:

=OR(IF(N(N3)=0,0,(N4-N3)/N3*100>50),N4<5,COUNTIF(P2:P500,"cat"))+0