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

    • Upon successful completion of this unit, you will be able to:

      • freeze panes to lock rows and columns in place;
      • construct a logical test to evaluate the contents of a cell; and
      • create the IF, OR, AND, NOT, COUNTIF, AVERAGEIF(S), and SUMIF(S) functions.

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

      • Freezing Panes Page

        Read this section which explains how the Freeze Panes command lets you scroll across a spreadsheet while keeping specific rows or columns locked in place. This feature is useful if you need to keep track of certain rows or columns while working on other parts of the spreadsheet.

      • Logical Functions Page

        The most commonly-used logical functions are IF, AND, OR, and NOT.

        • We use the IF function to determine if a given condition is met. A course with pass/fail grading is an example of using the IF function.
        • The AND function tells you if more than one set of conditions are met or not. If everything in the set of conditions is met, you get the output TRUE. If anything in the set of conditions is not met, you get the output FALSE. For example, you can use the AND function to determine if a student passed both the midterm and final exam in a course.
        • The OR function tells you if at least one of the input conditions are met. If one or more of the conditions are met, you get the output TRUE. If all conditions are not met, you get the output FALSE. For example, you can use the OR function to determine if a student passed at least one of the two courses they took.
        • The NOT function changes TRUE to FALSE or FALSE to TRUE. Each of these functions has unique and has more complex input syntax than the other functions we have studied.

        As you watch this tutorial, note the proper input syntax for each type of function.

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