Unit 3: Logical and Lookup Functions
In this unit, we explore logical functions, which are more advanced Microsoft Excel functions. Logical functions in Microsoft Excel allow you to assess the text or numerical content within cells and produce custom outputs.
For example, imagine you are an instructor with a class of 250 students. If a student's final grade is 60 percent or higher, they pass. If their final grade is below 60 percent, they fail. We can use the logical function IF to automatically determine if each student passed or failed based on their final grades. Logical functions can make these types of determinations for large data sets.
Completing this unit should take you approximately 1 hour.
3.1: Logical Functions
Logical functions in Microsoft Excel let you assess the text or numerical content within cells and produce custom outputs. To direct the software to treat it as text, insert quotation marks before and after the text. This will indicate that you do not want to perform a mathematical computation. Logical formulas are quite detailed – a misplaced comma, character, or number will generate an error instead of performing a calculation. Therefore, it is important to know the proper syntax for the different types of formulas.
3.2: Statistical IF Functions
This section introduces the COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMIF, and SUMIFS functions. These functions have more complex input, so be sure to read the examples thoroughly to understand how to format these functions properly.
- Use the COUNTIF function to count cells based on a single given criterion. For example, if you have a clothing store, you may want to count how many sales you had over $100. You could use COUNTIF to measure only sales from a given day that were over $100. The COUNTIFS function allows you to count based on more than one option. For example, you could have a spreadsheet of people's favorite colors. You can use COUNTIFS to count the total number of people who responded blue or green.
- Use the AVERAGEIF function to calculate the average of a list of numbers that meet a certain criterion. For example, you can average the scores of students' exam grades that are above 60 percent. The AVERAGEIFS function allows you to add additional criteria to your average.
- You can use the SUMIF function to add up values that meet a specific criterion. Go back to the example of a list of people's favorite colors; you can use SUMIF to add up the number of people who chose a specific color. You can use SUMIFS to add additional criteria, such as both blue and green.