Lesson 6 of 29, Logic & conditions

AND, OR & IFS

Combine conditions, and pick between more than two outcomes.

Free9 minutes, 3 exercisesANDORIFS

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

AND is TRUE only when every condition is true. OR is TRUE when at least one is. Put them inside IF's test.

IFS checks conditions in order and returns the answer for the first one that's true. It replaces stacks of nested IFs. End with TRUE as a catch-all.

Syntax

=IF(AND(B2>=4,C2>=2),"Yes","No")Both must be true

=IF(OR(B2>=50,C2="Yes"),0,7.99)Either can be true

=IFS(B2>=90,"A",B2>=80,"B",TRUE,"F")First match wins

Watch out for

IFS stops at the first true test, so check >=90 before >=80.
Without a TRUE catch-all, a score that matches nothing returns #N/A.

The exercises

Promotion eligibility. In D2, show Yes if the rating is at least 4 and tenure is at least 2 years, otherwise No. Fill down to D7.
Free shipping rule. Shipping is free (0) if the subtotal is $50 or more or the customer is a member; otherwise it's $7.99. Fill D2:D6.
Letter grades. In C2, use IFS to assign a grade: 90+ is A, 80–89 B, 70–79 C, below 70 F. Fill down to C7.

Promotion eligibility

In D2, show Yes if the rating is at least 4 and tenure is at least 2 years, otherwise No. Fill down to D7.