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, in-class 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

IF and AND

COUNTIF

Filters

Logic Functions   

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 non-speeders, 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.

This is a picture of the home tab of the ribbon in Word 2010.

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 stock-out 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

B60-40 or 0

=IF(B60>40, B60-40, 0)

=IF(B60<=40, 0, B60-40)

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

This is a picture of the home tab of the ribbon in Excel 2010.

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.

IF with AND

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 1-10, 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

 

 

 

 

 

 

 

We want to see which product is in our price range by entering an IF function.

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”. 

SUMIF, COUNTIF, and AVERAGEIF

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 A-Z 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 Z-A 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 A-Z 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 Z-A 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 pull-down arrow next to the Sort icon, then select “Custom Sort.” The add-level 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] .Pull-down arrows will appear next to the field names.

54.   Click on cell F1 and select the pull-down arrow. Click on all to deselect all options. Select Marketing.

55.   Click on cell G1 and select the pull-down 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 Field 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/en-in/article/Filter-a-list-of-data-8ec38534-e2f1-41d0-b8bb-e3f5fcad95a0

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   4:47

 

 

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 _______________________________      Date___________       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, AVERAGEIF and DATEDIF 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.