Using 'AND' and 'OR' function alongwith 'IF' function

[patamey]

I am unable to give correct command in excel for a certain condition. My condition is - If a Company is Listed Company then OR even if Its a Public Company but fulfills certain criteria - the result should be "Applicable" else "Not Applicable"

The Formula I am trying is -
=IF(OR(AND($B$3="Yes"),AND($B$2="Public",$B$10>=100000000,$B$14>=1000000000,$B$20>=500000000)),"Applicable","Not Applicable")

In the above case, the Company is not Listed - but its a Public Limited Company satisfying the criteria. But it still gives output as "Not Applicable". Please help me fix the formula.

---

 

[jtakw]

Hi,

I'm almost certain that you just got your AND & OR mixed up in your formula, but I'm unable to help because I'm uncertain how B3, B2, B10, B14, B20 relates to your formula/criteria.
Please explain.

This is just a guess:

=IF(OR($B$3="Yes",AND($B$2="Public",$B$10>=100000000,$B$14>=1000000000,$B$20>=500000000)),"Applicable","Not Applicable")

 

[patamey]

How do I share screenshot of my excel file?

 

[dave3009]

Hi and welcome,

I'd be inclined to think this is what you're looking for

=IF(AND(OR($B$3="Yes", $B$2="Public"),$B$10>=100000000,$B$14>=1000000000,$B$20>=500000000)),"Applicable","Not Applicable")

So if B3 = Yes (Listed) OR B2 = Public AND B10, B14, B20 are all greater than or equal to their respective values.

Regards

Dave

 

[patamey]

Dear Dave,

Thanks for quick response. Your assumption is 90% correct. However, I don't want ALL of B10, B14, B20 to be greater, instead, what I want is-

So if B3 = Yes (Listed) OR B2 = Public AND either of B10, B14 or B20 is greater than or equal to their respective values then the answer should be 'Applicable' else 'Not Applicable'

 

[dave3009]

Just wrap those in an OR in that case

=IF(AND(OR($B$3="Yes", $B$2="Public"),OR($B$10>=100000000,$B$14>=1000000000,$B$20>=500000000)),"Applicable","Not Applicable")

 

[patamey]

Thats Great Dave!

I guess that's sorted! Thanks a lot! You are genius.. The Forum is awesome! Hats off Sir!

 

[jtakw]

I think this will give more accurate results.

=IF(OR($B$3="Yes",AND($B$2="Public",OR($B$10>=100000000,$B$14>=1000000000,$B$20>=500000000))),"Applicable","Not Applicable")

 

[patamey]

Thanks a lot jtakw,

I guess this is more fullproof! Thank you so much!

 

[jtakw]

Thanks a lot jtakw,

I guess this is more fullproof! Thank you so much!

You're welcome, welcome to the forum.