Excel’s IF() function, in case you aren’t familiar with it, allows you to do one thing if a chosen condition is met, but do something else if that condition is not met. For example, when evaluating test scores you might want to display “Pass” if the score is 80% or better, but display “Fail” if the score is below 80%.
IF is an extremely useful function, but it is limited to evaluating one condition. What if you need to evaluate multiple conditions? That’s where the AND() function and the OR() function come in. AND and OR are each able to evaluate up to 255 conditions (Excel calls those conditions arguments). AND() will give the result “True” only if all of the conditions (arguments) are met, otherwise the result is “False”. But OR() will give the result “True” if any of the conditions are met.
By inserting an AND formula or an OR formula into the section of an IF formula where it would normally evaluate one condition, you can effectively evaluate multiple conditions.
As an example, let’s build a formula that will calculate a 10% commission only if an invoice has been paid and the margin is at least 20%.
| A | B | C | D | E | F | |
| 1 | Invoice # | Sales Amount | Cost | Margin | Paid? | Commission |
| 2 | 1001 | 332.00 | 299.00 | 9.9% | Yes | – |
| 3 | 1002 | 785.00 | 550.00 | 29.9% | Yes | 78.50 |
| 4 | 1003 | 6,644.00 | 4,186.00 | 37.0% | No | – |
| 5 | 1004 | 6,818.00 | 5,114.00 | 25.0% | Yes | 681.80 |
First let’s look at two simple examples that we’ll be able to combine to build the formula we need.
If there was only one condition that needed to be met, such as that the invoice must be paid, then we could use the following formula in cell F2:
=IF(E1=”Yes”,B2*0.1,0)
Remember that each “argument”, as Excel calls them, is separated by a comma. So, in plain English, the formula says IF cell E1 = Yes, then multiply the contents of cell B2 by 0.1, if not then the commission is 0.
Since we have more than one condition that needs to be met, we want to replace the criteria from the above formula, E1=”YES” , with a simple formula using the AND function.
If we were to enter the formula =AND(D2>=0.2,E2=”Yes”) into cell F2 the result would be FALSE. The invoice is paid (E2=”Yes”) but the margin is less than 20% (D2>=0.2). If we had used OR instead of AND with the same arguments the result would be TRUE since OR only requires one of the arguments to be met. With either AND or OR you can include up to 255 arguments. Just list all of the arguments (your conditions) between the parenthesis and separate them with commas.
To get the commission formula for this example, all that’s left to do is to take the first formula =IF(E1=”Yes”,B2*0.1,0) replace the single condition E1=”Yes” with =AND(D2>=0.2,E2=”Yes”). When you put them together looks like this:
=IF(AND(D1>=0.2,E1=”Yes”),B2*0.1,0).
In plain English that’s: If cell D1 is greater than or equal to 0.2 and cell E2 = Yes,then multiply cell B2 by 0.1. If not, then the result is 0.
Remember that you don’t have to write formulas from scratch. If you go to the Formulas tab and click the Insert Function button, you can choose a function and it will give you a fill-in-the-blank form to help you get everything entered in the right order.
