The Excel AND and OR functions allow you to test the conditions of certain cells and apply a result based on the result of those tests.
With the AND function, ALL criteria must be met in order for the test to come out as true. Let’s say we have a listing of our sales staff and their sales AND performance rating must both be at a certain level in order to earn a bonus. If both criteria are met the salesperson will receive a bonus of $2500. If only one condition is met or if no conditions are met they will not receive a bonus.
To accomplish this we must combine the IF and AND functions. Let’s look at the following data in our worksheet.
Let’s say that to earn a bonus, the salesperson must have sales at or above $150,000 and a performance rating at or above 80. In cell D2 we will type the following formula:
This is an AND function nested inside an IF function. If we break it down, we are basically saying IF b2 is greater than or equal to 150000 AND c2>80 then the bonus is 5000. If only one or none of the criteria are met then the bonus=”No Bonus”.
If we ook at our first salesperson, Steve, his sales are above the $150,000 threshold but his rating is not above 80. Therefore his bonus should be “No Bonus”.
Now we will copy this formula down so that it applies to the rest of the rows by clicking and dragging on the little “grabber” in the bottom right hand corner of the cell.
Since the other salespeople meet both the sales and performance criteria they receive the $5000 bonus.