How to use nested IF statements in Excel with AND, OR, NOT
Corporate finance, Excel function tutorials
Following up on last week’s introductory post on using the IF function, here’s one on how to use Excel’s IF statement when you have multiple conditions to meet.
The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. Let’s take a look at our example again. Note that I’ve added the maximum amount of $200K into cell B3:
Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.
Bonus | 12% | |
Minimum amount | 100000 | |
Maximum amount | 200000 | |
Sales | Bonus | |
Salesman A | 87925 | |
Salesman B | 100000 | |
Salesman C | 145000 | |
Salesman D | 200750 | |
Salesman E | 178650 | |
Salesman F | 99555 | |
Salesman G | 147000 | |
Salesman H | 213450 | |
Salesman I | 122680 | |
Salesman J | 92500 |
Ok, now back to the tutorial.
Now, let’s suppose sales have to be greater than or equal to $100K and less than $200K for a salesman to receive a 12% commission rather than just be greater than $100K, as in our introductory example. How would you write that in “Excel-speak”?