Two conditions with AND
Step 1 of 3
AND checks several tests at once and is TRUE only when all of them are. =AND(B2>=5, C2>=4.5) asks: at least 5 deals, and a rating of at least 4.5?
Click Continue when you're ready.
Already know it? Type the formula straight into D2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Seller | Deals | Rating | Bonus? | ||
| 2 | Mikkel | 7 | 4.6 | |||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Need help?
Why this works
AND(test1, test2) is TRUE only when every test is TRUE. 7 >= 5 and 4.6 >= 4.5 are both TRUE, so IF shows Yes. AND on its own returns TRUE or FALSE; IF turns that into words.
Common mistake: Writing the tests as one, like B2>=5 AND C2>=4.5. In Excel, AND is a function: AND(B2>=5, C2>=4.5).
The answer
- D2
=IF(1AND(B2>=5,C2>=4.5),2"Yes",3"No")
In plain words: 1If there are at least 5 deals in B2 and the rating in C2 is at least 4.5, 2show Yes. 3Otherwise show No.
+20 points