Excel's IF() function
Note: These instructions are an excerpt of Luther Maddy’s Excel Database and Statistical Features. This full text is available in printed or eBook format from Amazon.com
Watch the Video: Using Excel's IF() function
Watch the Video: Creating Nested If and Or statements
Download the example TripData file to complete this lesson
The IF() Function
The If() function is a logical function; it returns one value if the condition is true and another if it is false. We will use the If function to calculate a discount if the total trip cost exceeds a certain amount.
The syntax of the If() function is:
=If(Condition to check, value if true, value if false
The “values” to be returned can be computations. In the next steps, you will use the If() function to compute a customer’s discount based on the cost of the trip.
1. In the TripData workbook, move to cell F4 in the January worksheet, type Discount and then press (Enter).
The No Fault Travel agency gives a 5% discount, if the total cost of the trip exceeds $2,500. Let’s frame the If() statement in our minds.
Arguments for IF() Mathematical expression
Logical test: Does trip exceed $2,500? Trip cost > 2500
Discount if True Trip cost x 0.05
Discount if False 0
2. In cell F5 type the following formula: =IF(E5>2500,E5*0.05,0)
3. Copy the formula from F5 through F14.
You may double-click the fill handle on cell F5 to quickly copy this discount formula. Add accounting formatting as shown.
Nested If Statements
Multiple If() functions (up to 64) can be used in the same formula to return different values in situations where there are more than two possible outcomes. For example, if you wanted higher trip costs to qualify for a higher percentage discount, the following formula could be used:
=IF(E5>5000,E5*0.08, IF(E5>2500,E5*.05,0))
Let’s try to evaluate our equation for this situation of three possible outcomes.
You've now seen how easy it is to use Excel's If() function. Exmpriment with your own data and put it to work.
