mardi 2 février 2016

Nested IF / AND / OR statement

I have a formula that I can seem to get most of the way there, but can't make the final jump. Here is the scenario. If any cell in an array (B2:B4) is not blank, then cell B9 should populate a text value OR if any cell in a different array (D6:D9) has a text value, then again cell B9 should populate with a different text value. Finally, if both arrays meet the specific criteria, then cell B9 should populate with a third text value.

Here is what I have that works. This formula returns a value of "Correct" id B2:B4 is not blank OR if D7:D9 contains the text "Plus" it returns a value of "Plus". Otherwise it leaves cell B9 blank.

=IF(OR(B2<>"",B3<>"",B4<>""),"Correct",IF(OR(D7="Plus",D8="Plus",D6="Plus"),"Plus","Blank"))

This AND formula returns "Correct+Plus" if both of the criteria above are met.

=IF(AND(OR(B2<>"",B3<>"",B4<>""),(OR(D7="Plus",D8="Plus",D6="Plus”))),”Correct+Plus”,”Correct or Plus or Nothing")

I can't seem to get the formula that combines both. If array 1 is not blank, return "Correct". If array 2 is "Plus" return "Plus". If array 1 is not blank AND array 2 is "Plus", return "Correct+Plus".

Aucun commentaire:

Enregistrer un commentaire