• Unit 3: Logical 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 for a class of 250 students. Your students pass if their final grade is 60 percent or higher. They fail if their final grade is below 60 percent. 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 2 hours.

    • 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 make it easier to assess cell data and produce custom outputs in Microsoft Excel. The data can be text or numerical. You can insert quotation marks before and after the text to direct Excel to treat data as text. Be aware that logical formulas must be exact – a misplaced comma, character, parenthesis, or number will generate an error instead of performing a calculation.

      • Freezing Panes Page

        Before we go any further, here is a tip for viewing large spreadsheets that can be difficult to manage because the column or row headings scroll off the screen. Use the Freeze Panes tool to lock rows or columns in place. For example, you can use Freeze Panes to lock columns A and 1–3 in place so the headings in your spreadsheet are always visible at the top or left of the screen as you scroll through the data that follows.

      • Using the IF Function Page

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

        Use the IF function to determine whether a given condition is met. For example, you can use the IF function to determine if a student passed a course based on their final grade. Watch this tutorial for another example of using the IF function.

      • The And Function Page

        The AND function tells you if more than one set of conditions are met. If all conditions in a set are met, you get the output TRUE. If any condition 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. Watch this tutorial for another example using the AND function.

      • IF with AND and OR Page

        The OR function tells you if at least one of the input conditions is met. If one or more 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 their two courses.

        The NOT function changes TRUE to FALSE or FALSE to TRUE. This is useful when you want to see when a condition is not met, or you want to perform an additional calculation. For example, you can use the NOT function to determine whether a student did not pass the final exam.

        The AND, OR, and NOT functions are often used with the IF function to assess multiple conditions. We accomplish this by nesting functions. Nesting occurs when one function is placed as an argument in another function. For example, if a student's midterm grade is in C2 and the final grade is in F2, the following formula would determine whether the student passed both the midterm and final exams:
        =IF(AND(C2>=60%,F2>=60%),"passed both midterm and final","did not pass both midterm and final")

        In this example, we say the AND function is nested inside the IF function. Nesting adds another layer of complexity to formulas. Simple mistakes can generate errors or incorrect results. As you watch this tutorial, pay close attention to the proper syntax for each type of function.

    • 3.2: Statistical IF Functions

      In this section, we study the functions COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMIF, and SUMIFS. Pay close attention to the examples to understand how to format these functions properly.

      Use the COUNTIF function to count cells based on one criterion. For example, a store manager may want to count how many sales they had over $100. They can use COUNTIF to only measure sales from a given day over $100. They can use the COUNTIFS function to count based on more than one option. For example, let's say a meeting planner has created a spreadsheet to track a group's food allergies. They can use COUNTIFS to count the number of people who responded "dairy foods" or "gluten".

      Use the AVERAGEIF function to calculate the average of a list of numbers that meet a certain criterion. For example, an instructor might average the scores of students who earned grades above 60 percent. The AVERAGEIFS function allows them to add additional criteria to their average.

      Use the SUMIF function to add values that meet a specific criterion. For example, the meeting planner can use SUMIF to add the number of people who have food allergies or other preferences. They can use SUMIFS to add additional criteria, such as peanuts or requests for Kosher meals.

      • Statistical IF Functions Book

        Read these examples of how to use these different spreadsheet functions in the workplace. The learning objectives review how to use each type of function; the tables describe the syntax for each command type in detail.

      • COUNTIF, SUMIF, and AVERAGEIF in Microsoft Excel Page

        Watch this tutorial for some examples of using these functions and the proper input syntax.

      • IF, COUNTIF, and SUMIF in Google Sheets Page

        Logical functions in Google Sheets work similarly to Excel. Watch this video to see how to input statistical logical functions in Google Sheets.