Excel IF Functions
This lesson covers
methods for combining two formulas in one cell and having Excel automatically
pick the right one to use.
Instructions:
Read through the set of learning objectives. This is what you should
know when you finish the lesson.
Watch the YouTube videos before starting the lesson.
Complete every step in the assignment, in the order given. Do not skip
steps.
When you have done all of the steps, fill out the Check for
Understanding (CFU) form.
A completed CFU for every lesson, inclass and homework, is due to be
turned in during your next class.
The CFU forms are not to be submitted by email.
Where there are differences between software versions, the
instructions for Excel 2010 are in Black, for the Mac
are in green and for Excel 2013 are in blue.
Summary of Key Points:
Create a
Boolean formula that gives a result of either True or False.
Write an IF
Function to select between two formulas based on a variable.
Write a
nested IF Function to use multiple Booleans to compare the contents of a cell
to different target values.
Combine the
AND function with multiple IF functions. The value for the True result shows
when all of the Booleans are True.
Combine the
OR function with multiple IF functions. The value for the True result shows
when any one of the Booleans is True.
Use the
SUMIF, COUNTIF, AVERAGEIF and DATEDIF functions.
Sort data numerically and alphabetically.
Use filters.
Videos:
Use the SUMIFS Function Time = 0:59
https://www.youtube.com/watch?v=53KYJOD8Axs
Booleans
Excel has a special function, the IF
Function, that can actually make decisions for us. We would use the IF function
when we have a choice between two distinct possibilities. For example, only the
people who are speeding will be charged a fine, only the people who score 85%
of the points will pass the course, only people who meet their sales quotas
will get paid a commission, only people who are overweight need to diet, and so
forth.
To let Excel know which subset we are dealing
with, for example speeders or nonspeeders, we just have to tell Excel what to
test for, what to display if a condition is met (called the TRUE value), and
what to display if the condition is not met (called the UNTRUE value).
The test uses a special kind of formula
called a Boolean. A Boolean uses an equation to make a statement about two
numbers. The symbols used in a Boolean formula are >, <, =, >=, <=, or
<>.
When you enter a Boolean into Excel, for
example by typing =C4>7 into cell D4, you will see either TRUE or
FALSE. Cell D4 would display the
word TRUE when the value in cell C4 is greater than 7 and display the word
FALSE when it is not. Here are some
additional examples;
Examples
of Booleans Comparing Numbers 
Formula
in cell D4 
If
the value in C4 is
7, cell D4 shows 
If
the value in C4 is
8, cell D4 shows 
Is
Equal to 
=C4=7 
TRUE 
FALSE 
Is
Not Equal to 
=C4<>7 
FALSE 
TRUE 
Is
Greater Than 
=C4>7 
FALSE 
TRUE 
Is
Greater Than or Equal To 
=C4>=7 
TRUE 
TRUE 
Is
Less Than 
=C4<7 
FALSE 
FALSE 
Is
Less Than or Equal to 
=C4<=7 
TRUE 
FALSE 
You
can also use a Boolean to compare strings (groups of letter and number
characters.)
When comparing strings, we must include
quotation marks around the string. If you leave them out you will get the #NAME? message instead of seeing TRUE or FALSE.
Examples
of Booleans Comparing Text 
Formula
in cell D4 
If
the Value in C4 is STRING,
cell D4 shows 
Is
Equal to 
=C4=”STRING” 
TRUE 
Is
Equal to 
=C4=”ANOTHERSTRING” 
FALSE 
Is
Equal to 
=C4=STRING 
#NAME? 
IF
Functions
Going
back to our speeding example, we could test the driver’s speed to see if
it is over the speed limit. As a result of that test we would know if the
driver was speeding. Once we know that, we know how to treat the driver 
speeders pay a fine and non speeders don’t. We call the first part, the
driver’s speed, the independent variable. Changes to the independent
variable cause changes in the amount to fine the driver. We call the fine the
dependent variable. Changes to the amount of the fine don’t cause changes
to the speed, changes to the speed do cause changes to the fine, so the speed
is the independent variable.
How would we write the If Function?
Let’s assume the speed of the driver is found in cell D5. The IF Function
would be =IF(D5>65,200,0)
The first argument of the IF function is
always a Boolean formula comparing the value of the independent variable to the
value at which the outcome changes. This value is called the switching point.
In our story the independent variable is the speed of the driver. The switching
point is 65 MPH. The speed of the driver was entered into cell D5 so we used
D5>65 as the first argument.
The second argument is the value to display
if the Boolean formula in the first argument is true, i.e. we have the special
case. If the driver was going over 65 MPH the Boolean was true so we used 200
for the second argument. This is the amount of the fine.
The third argument is the value to display
for the standard case. If the driver was not going over 65 MPH the Boolean was
false, it was not a special case, and we would use the amount of 0.
Another
example
If the
sum of the items ordered by a Java Juice customer is $10 or more we charge them
whatever that amount is. But when the amount is under $10 we want to charge
them exactly $10.
The sum of the items ordered by the customer
is displayed in cell D26. This is the independent variable. We want to find the
amount to charge the customer.
D26
= Sum of the customer’s
purchases 
Amount
to charge customer 
Possible
Arguments 
$7 
$10 
Special
Case =10 
$8 
$10 

$9 
$10 

$10 
$10 

$11 
$11 
Standard
Case =D26 
$12 
$12 

$13 
$13 

$14 
$14 
To set up the IF function we have to know
five pieces of information. You can use this form with any story problem to set
up If Functions in Excel.
·
What
is the independent variable (in words)? ______________
·
Which
cell shows the independent variable?
______________
·
At
what value do we switch between the two different outcomes? _________
·
What
are the two possible values of the dependent variable? Usually these values are
expressed as a number or a formula.
____ and _____
·
Write
the IF function =IF(______________)
Let’s fill it out:
·
What
is the independent variable (in words)? The sum of the customer’s
purchases.
·
Which
cell shows the independent variable?
D26
·
At
what value do we switch between the two different outcomes? $10
·
What
are the two possible values of the dependent variable? D26 and
$10
·
Write
the IF function =IF
(D26<10,10,D26)
Notice that the terms in the IF Function were
found in the answers to our questions.
If you use text in an argument or in a
formula it must be inside quotation marks. i.e. “No fee”
Steps  (Do these in Word) the answers are given below
the set of sample problems.
A restaurant will deliver food to a
customer’s house. If the amount of the customer’s order is over
$50, there is no delivery fee (the fee is zero). If the value of the order is
$50 or less, we charge the customer a delivery fee. Assume we will find the fee
amount in cell A10. The amount of the order is found in cell B10. The IF
function goes in cell C10. Create the IF function to show the amount of the
delivery fee.
1. What is the independent variable (in words)? ______________ 
2.
Which cell shows the independent variable? ______________ 
3.
At what value do we switch between the two
outcomes? _________ 
4.
What are the two possible values of the dependent
variable? ______ and _______ 
5.
Write the IF function =IF(______________) 
Drivers
going over 65 MPH on the highway are fined $280. There is no fine if the driver
is traveling at 65 MPH or less. Assume the driver’s speed is found in
cell B20. The IF function goes in cell C20. Create the IF function to show the
amount of the fine.
6. What is the independent variable (in words)? ______________ 
7.
Which cell shows the independent variable? ______________ 
8.
At what value do we switch between the two
outcomes? _________ 
9.
What are the two possible values of the dependent
variable? ______ and _______ 
10.
Write the IF function =IF(______________) 
Students
scoring at least 85% on a challenge exam receive a grade of
“CREDIT” for the course. Students scoring less than 85% receive a
grade of “NO CREDIT”. Assume the student’s test score is
found in cell B30. The IF function goes in cell C30. Create the IF function to
show the grade.
11. What is the independent variable (in words)? ______________ 
12.
Which cell shows the independent variable? ______________ 
13.
At what value do we switch between the two
outcomes? _________ 
14.
What are the two possible values of the dependent
variable? ______ and _______ 
15.
Write the IF function =IF(______________) 
Salespeople
at XYZ Company whose monthly sales add up to more than $10,000 earn a
commission. If their monthly sales amount is 10,000 or less they do not. The commission is equal to three percent
times the value of their sales. Assume the amount sold is found in cell B40.
The IF function goes in cell C40. Create the IF function to show the commission
amount.
16. What is the independent variable (in words)? ______________ 
17.
Which cell shows the independent variable? ______________ 
18.
At what value do we switch between the two
outcomes? _________ 
19.
What are the two possible values of the dependent
variable? ______ and _______ 
20.
Write the IF function =IF(______________) 
The
manager of a grocery store knows that he has to place an order whenever his
stock of Twix bars drops to 360 or below. This is called the stockout point.
If he has more 360 on hand he will not order any more. The manager orders 2000 candy bars every
time he places an order. Assume the
amount of Twix candy bars in stock is found in cell B50. The IF function goes
in cell C50. Create the IF function to show how many candy bars should be
ordered.
21. What is the independent variable (in words)? ______________ 
22.
Which cell shows the independent variable? ______________ 
23.
At what value do we switch between the two
outcomes? _________ 
24.
What are the two possible values of the dependent
variable? ______ and _______ 
25.
Write the IF function =IF(______________) 
To
prepare paychecks you need to calculate the number of hours that will be paid
at the overtime rate. The first forty hours an employee works in a week is paid
at their regular rate. Any hours worked after the first forty are counted as
overtime. Assume the hours worked is found in cell B60. The IF function goes in
cell C60. Create the IF function to show how many hours are overtime.
26. What is the independent variable (in words)? ______________ 
27.
Which cell shows the independent variable? ______________ 
28.
At what value do we switch between the two
outcomes? _________ 
29.
What are the two possible values of the dependent
variable? ______ and _______ 
30.
Write the IF function =IF(______________) 
Answers to sample problems:
Delivery
Fee 
Order
Amount 
B10 
$50 
0
or A10 
=IF(B10>50,
0, A10) =IF(B10<=50,
A10, 0) 
Amount
of Fine 
Speed 
B20 
65
MPH 
280
or 0 
=IF(B20>65,
280, 0) =IF(B20<=65,
0, 280) 
Course
Grade 
Test
Score 
B30 
85% 
Credit
or No Credit 
=IF(B30>=0.85,
”Credit”, ”No Credit”) =IF(B30<0.85,
”No Credit”, ”Credit”) 
Sales
Commission 
Amount
Sold 
B40 
$10,000 
B40*3%
or 0 
=IF(B40>10000,
B40*0.03, 0) =IF(B40<=10000,
0, B40*0.03) 
Ordering
Candy 
Twix
bars in stock 
B50 
360 
0
or 2000 
=IF(B50>360,
0, 2000) =IF(B50<=360,
2000, 0) 
Overtime 
Hours
worked 
B60 
40 
B6040
or 0 
=IF(B60>40,
B6040, 0) =IF(B60<=40,
0, B6040) 
Note, When you reverse the sign of the inequality, the second and
third arguments switch positions. The = sign always go last: both >= and <= are correct. If you
put the equal sign first, => or =< you will get an error message 
Do
the rest of this assignment in Excel
Complex
Formulas using Nested IF, AND, and OR
If
we have more than two independent variables, with different values for each,
(was he speeding and was he drunk) we can combine them in an IF Function.
Combining IF function is called nesting. The IF function that is used as an
argument inside the first IF function is called a nested IF. I have underlined
each of the function listed in the steps.
There
is another way to combine IF functions with two or more independent variables,
by including the word
AND or the word OR at the beginning of the IF function.
Video:
IF and AND
When
you use the word AND as part of the IF function, if all statements about the
independent variables are true the IF function will show the second (true)
argument. If any of the statements about the independent variables are false,
the IF Function will show the third (false) argument.
When
you use the word OR as part of the IF function, if any one or more statements
about the independent variables are true the IF function will show the second
(true) argument. If all of the statements about the independent variables are
false, the IF Function will show the third (false) argument.
If
Variable 1 is 
And
Variable 2 is 
AND
shows 
OR
shows 
True 
True 
True 
True 
True 
False 
False 
True 
False 
True 
False 
True 
False 
False 
False 
False 
Create this Table in a new worksheet. The Scale is
110, with ten being the best. Note: Copying this table
and pasting it into Excel won’t work on many laptops or MACs.
This is cell A1

Savings

Strength

Safety

Buy?

Advil

4

10

8


Bayer

10

4

7


Excedrin

7

8

10







31. Enter this formula into cell E2: =IF( B2>6, “Buy it”, ”Do not buy
it”) Copy the function into cells E3 and E4. Only two cells should show the words “Buy it”.
Note:
If you are seeing the #NAME error message it is because Excel doesn’t
recognize the double quotes “ “ in
formulas that are copied and pasted from the web. Edit the formula to delete
them. Now type them back into the formula. It should work now.

Now we want to see if all three of our
requirements are met and reject the purchase if any of the three are
unsatisfactory.
32.
Enter this function into
cell E2: =IF(B2<7, ”Costs too much”,
IF(C2<7, ”Not strong enough”, IF(D2<7, ”Unsafe”,
”Buy it”))) 
33.
Copy the function into cells E3 and E4. 
This
nested IF function will first test for cost. It will reject the painkiller if
it is too expensive. If the cost is ok it will then test for strength. It will reject
the painkiller if it is too weak. Finally it will test for safety. It will
reject the painkiller if it is not safe. If all tests are passed, it will
approve the purchase of the painkiller. You will see the reason for the
rejection in column E.
Here’s
how it would look with an AND function
34.
Enter this function into
cell E2: =IF(AND(B2>6, C2>6,
D2>6), “ Buy it ”,”Do not buy it”) Copy the function into cells
E3 and E4. This will give you the
same outcome as the Nested IF above. All three of our statements about the
independent variables must be true to see the words “Buy it”. 
Now
we want to test to see if any of our requirements are met. We can also use a
nested IF.
35.
Enter this function into
cell E2: =IF(B2>6, ”Buy
it”, IF(C2>8, ”Buy it”, IF(D2>7, ”Buy
it”, “Do not buy it”))) Copy the function into cells E3 and E4.
If any value in the B2:D2 range fits a statement about an independent
variable will see the message “Buy it”. 
The OR Function
When you use OR to connect conditions, only one
requirement must be met for the Boolean to evaluate as True.
36.
Enter this function into
cell E2: =IF(OR(B2>6, C2>8, D2>7), “Buy
it”, ”Do not buy it”) Copy the function into cells E3 and
E4. If any value in the
B2:D2 range fits a statement about an independent variable you will see the
message “Buy it”. 
You can use the SUMIF, COUNTIF, and AVERAGEIF
functions to quickly summarize data from a table.
COUNTIF video COUNTIF
SUMIF AVERAGEIF
COUNTIF walkthrough an example (Long)
37. Create this Table in a new worksheet.
“Items Sold” should go into cell A1. Note: you can copy and
paste this table directly into Excel. Select the range A1:G8 before you
paste. You might have to right click and choose Paste special to get the
table to paste correctly. 
This is Cell A1 Item Sold

Sale Amount


Item

SUM

COUNT

AVERAGE

Sandwich

5.99


Dessert




Salad

3.95


Salad




Dessert

4.00


Sandwich




Dessert

3.50






Salad

3.95






Salad

3.95






Sandwich

7.99






In
columns E F and G we want to enter functions to calculate values of the data in
columns A and B.
38.
In cell E2 type =SUMIF(A:A, D2, B:B). A:A refers to
all of the cells in column A. B:B refers to all of the cells in column
B. 
The first argument is the range that contains
the labels in our table. In this case it is column A.
The second argument tells Excel what word we
are looking for in the range of labels. This argument could be either text:
“Desserts”, or a cell reference: D2.
The third argument is the range that contains
the values in our table. In this case it is column B.
This function looks in column A to find every
row where the value in cell D2 (Dessert) appears. This process is called
filtering. Then it shows the SUM of the values in column B for the filtered
(selected) rows, the ones that contain the key word Dessert.
39. Copy the formula from cell E2 into cells E3 and E4. 
40.
In cell F2 type =COUNTIF(A:A, D2), Copy the
formula into cells F3 and F4. 
41.
In cell G2 type =AVERAGEIF(A:A,
D2, B:B). Copy the formula into cells G3 and G4. Note, if you see the same number in all
three cells you probably used the AVERAGE function instead of the AVERAGEIF
function. 
42.
Test the formulas by adding rows of data. Enter the
word sandwich in cell A9 and Salad in cell A10. Enter the numbers 5.99 and
3.95 into cells B9 and B10. The numbers displayed in cells E2, E3, E4, F2 and
F3 should change. 
If you are seeing zeroes in any of
the cells the
most likely cause is the text in cells D2:D4 does not perfectly match the text
in the labels in column A. Maybe there is a space in one of them and not in the
other. To fix this I would copy and paste a label from Column A to replace the
equivalent text in column D.
Sorting
and Filtering
Open Excel. Download the file http://bus91L.altervista.org/Data/2013Data.xlsx.
How Table
are Sorted.
This part is concepts information: all you have
to do is read it. Step 43 is where you start actually working in Excel. To sort
data in a table (change the order in which the rows are listed), first click on
any cell in a column that contains data. Then click on the AZ icon [ALT A S A]. Data in the selected column will be
rearranged so it is sorted in alphabetical order. The other columns in the
table will be rearranged at the same time so each record (row of data) stays
together. You can also use the ZA icon [ALT A S D]
to sort in reverse order, and the sort icon [ALT A S S] to sort by more than one
category.
Trouble shooting.
 Be careful. If you select all of the cells in a
column, only the data in that column will change position. The rest of the data
will stay in its original rows and your table will be ruined. Make sure your
entire table is selected before sorting it.
 Sometimes the sort tool moves the titles of the
columns when it sorts the data. The Sort dialog box [ALT
A S S] (an unfortunate
keystroke combination) has an option in
the upper left called “my data has headers”. That may need to be
checked (or unchecked) to get the sorting to work correctly.
Ok, now let’s try it.
43.
Select
the Students worksheet (worksheet tabs are below the spreadsheet) in the file
you just downloaded. Click in the F2 cell in column F. Click on the
AZ icon [ALT A S A] to sort the table in alphabetical order
based on majors. Note: If you are
having trouble, make sure you are working with cell F2 in the STUDENTS worksheet.
Cell F1 will have the word Majors in it. 
44. Click in the G2 cell in column G. Click on the ZA icon [ALT A S D] to sort the table in
reverse alphabetical order based on class. 
45. Now we want to sort on more than one field at the same time. Click
in the B2 cell in column B. From the Data tab, click the Sort icon [ALT
A S S]. 
46. Set the “sort by” option to last name. 
47. Set the “order by” option to A to Z. 
48. Click on “add level.” (For
the Mac you would select the pulldown arrow next to the Sort icon, then
select “Custom Sort.” The addlevel button is a plus sign in the
lower left corner. ) 
49. Set the “then by” option to first name. If you are not seeing this option you probably
selected all of column B instead of clicking on a cell in column B, so Excel
is trying to sort just that single column. Cancel the sorting attempt.
Reselect the data range so all columns of the data are included, then start
over at step 45. 
50. Set the “order by” option to A to Z. 
51. Click OK. 
Create a
Filtered List
Watch this video
on Filters
Filters are used when you want to see only some
of the records, ones that share certain characteristics. For example, you have
a table of data that shows sales from 2015, but you want
Excel looks at a list of words and values in a
field, called criteria, that you want so see displayed. It displays only the
rows of data that have those words in the filtered field. Any record in the
table that is does not match one of the items in your list of criteria will be
hidden.
To create criteria, start by applying the Filters
feature [ALT A T] to a range of data. Click on
an individual field heading. Excel will show a list of the values in that
field. You then put a checkmark next to each word in the field that you want to
be displayed. Only rows containing those values will be displayed. The
remaining rows will stay hidden.
You can select criteria in more than one field at
the same time. For example, you might want to show only the sales that were
made in April in California and Nevada.
After the filtered list is created you can send
it directly to the printer. The hidden rows will not be printed.
Clicking the filter icon again [ALT
A T] will
remove the criteria selections from all of the fields and you will see all of
the records again.
Let’s try it.
52.
Select the Students worksheet (worksheet tabs are
below the spreadsheet) in the file you just downloaded. Click on any
cell in the data table. 
53.
On the
Data tab, click on the Filter icon [ALT A T] .Pulldown arrows will
appear next to the field names. 
54.
Click on
cell F1 and select the pulldown arrow. Click on all to deselect all options.
Select Marketing. 
55.
Click on
cell G1 and select the pulldown arrow. Click on all to deselect all options.
Select Senior. 
56.
Turn off
the AutoFilter. Click anywhere in the data table. From the Data tab click on
the Filter icon [ALT A T]. 
Use
Custom Filters
You can also use filters to look for numbers
within a range of values.
57.
Click on any cell in the range of data. 
58.
On the Data section of the ribbon click on the
Filter icon [ALT
A T]. 
59.
Click the
arrow next to the GPA field. 
60.
You’ll see a
menu. Select Number Filters and then Custom. On the left side of
the dialog box, click “is greater
than.” In the MAC click on the
“Choose One” option. Select “Greater Than” Go here for more info
https://support.office.com/enin/article/Filteralistofdata8ec38534e2f141d0b8bbe3f5fcad95a0 
61.
In the
box to the right of “is greater than” type in 3.8. Click OK. You
will see all of the students with a high GPA. 
62.
On the Data section of the ribbon click on
the Filter icon [ALT A T] to turn the filter off. 
Review – Fine Points
About the Insert Function
dialog box Time = 4:47
CountIF
counts Unique Entries Time = 1:08
Instructions for the
Check for Understanding (CFU) form.
A CFU form is
turned in for every assignment.
Starting with
the line for your name, copy and paste the CFU form into a Word document.
Fill in your name, date, section number
Circle (or indicate by highlighting) a Y or N response for
each statement. I do not subtract points for No answers.
Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see
something to prove that you did the assignment. I don’t need to see all
of the assignment. Resize the screenshot, if necessary, so both the form and
graphic can fit on one page.
A copy of the CFU is due to be
turned in during the next class session.
I do not accept CFU’s through email.
Name
_______________________________ Section _______
Check
for Understanding – Excel IF Functions
Y/N I can
create a Boolean formula that gives a result of either True or False.
Y/N I can
write an IF Function to select between two formulas based on a variable.
Y/N I can
write a nested IF Function to use multiple Booleans to compare the contents of
a cell to different target values.
Y/N I can
combine the AND function with multiple IF functions. The value for the True
result shows when all of the Booleans are True.
Y/N I can
combine the OR function with multiple IF functions. The value for the True
result shows when any one of the Booleans is True.
Y/N I can use
the SUMIF, COUNTIF, and AVERAGEIF functions.
Y/N I can sort data numerically and alphabetically, using either
single or multiple levels.
Y/N I can apply, modify, customize, and remove a filter.