How to combine more than one IF AND formula
Requirement:
Is it possible to combine three IF(AND formulas into one cell? But there is a catch which I will explain below. These are my three formulas;
=IF(AND($C$7<F23,$C$7>F24,$C$10<K23,L23>30,L23<75),"YES","NO")
=IF(AND($C$8<H23,$C$9<J23),"YES","NO")
=IF(AND($C$9<H24,$C$8<J24),"YES","NO")
Nothing special when they're all separate I know but here is the catch. I need them all in the same cell, however this could be the more tricky part.
- If the first formula results in a "NO", it should change the cell to "NO" regardless of the outcome of the other two formulas. However if it reads "YES", this is where I'm hitting a stumbling block.
- If the second formula results in a "NO" and the third formula results in a "YES" or vice versa, and the first formula is also reading "YES", I need the cell to read "YES".
- So basically, as long as it includes a "YES" from the top formula, two out of three "YES" results is enough to warrant "YES" in the cell.
Is this possible? I think I may need to use an OR statement, but I am not sure how to go about it.
Is it possible to combine three IF(AND formulas into one cell? But there is a catch which I will explain below. These are my three formulas;
=IF(AND($C$7<F23,$C$7>F24,$C$10<K23,L23>30,L23<75),"YES","NO")
=IF(AND($C$8<H23,$C$9<J23),"YES","NO")
=IF(AND($C$9<H24,$C$8<J24),"YES","NO")
Nothing special when they're all separate I know but here is the catch. I need them all in the same cell, however this could be the more tricky part.
- If the first formula results in a "NO", it should change the cell to "NO" regardless of the outcome of the other two formulas. However if it reads "YES", this is where I'm hitting a stumbling block.
- If the second formula results in a "NO" and the third formula results in a "YES" or vice versa, and the first formula is also reading "YES", I need the cell to read "YES".
- So basically, as long as it includes a "YES" from the top formula, two out of three "YES" results is enough to warrant "YES" in the cell.
Is this possible? I think I may need to use an OR statement, but I am not sure how to go about it.
Solution:
Code:
=IF(IF(AND($C$7<F23,$C$7>F24,$C$10<K23,L23>30,L23<75),"YES","NO")="NO","NO", IF(OR(IF(AND($C$8<H23,$C$9<J23),"YES","NO")="Yes",IF(AND($C$9<H24,$C$8<J24),"YES","NO")="Yes"),"YES","NO"))
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to check if two columns match or not |
| How to do a formula to compute IF A1 (number) is <1000 B1 = Bad |
| How to use IF formula with multiple criteria |
| How to use SUMIF |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions
Gallery
