A handy use for Excel is knowing how to apply logical operations, being able to differentiate your outputs based on the inputs provided.


In this article, we will go through a handful of useful logical operations. This will be the dataset we are using for our examples, the quantity a fruit stand sells of each of their products each day for a week:




IF FUNCTION


The IF Function follows the simple logic of "If this, then that." You give the function a certain criterion to test, and it will return one of two values whether or not your input meets the criterion, true or false. The following is the syntax for the IF Function:


=IF(logical_test, [value_if_true], [value_if_false])


Answer the following question: Did the fruit stand sell more watermelons than apples on day 1?


Let's take the function step-by-step:


  • Logical Test: This is what we want to test our question against. Since we want to test whether one product sold more than another on day 1, we would do a simple inequality as our logical test, {day 1 watermelons} > {day 1 apples}. We'll use the cell references to denote this in the formula.
    • =IF(C4>C2,
  • Value If True/Value If False: This is where we give what we want our function to output. Our question is a simple yes/no question, so we can simply just set our value if true to Yes and our value if false to No. Note: When you want a function to return text as your value, the desired text string must be put in quotation marks.
    • =IF(C4>C2,"Yes","No")

We can see that our function returns "Yes" in this case. The below screenshot also did the same logical test for days 2-5.


AND, OR, NOT


The AND and OR functions apply a logical test on multiple variables. In an AND function, the test will return true only if the test is true on all variables. In an OR function, the test will return true if the test is true on any variable. 


The NOT function simply inverts the output of any logical function, returning false if true and true if false. 


The syntax for these three are as follows:


=AND(logical1, [logical2], [logical3], ...)


=OR(logical1, [logical2], [logical3], ...)


=NOT(logical)


When used alone, these functions don't do much for us. It is when we use them in tandem with an IF function that they truly assist us.


First let's test the AND function with the following question: Did the fruit stand sell more than 20 of each product on day 1?


Let's take the formula step by step.


  • First, start our IF function
    • =IF(
  • Now we want to test all products on day 1 and whether they are greater or less than/equal to 20. We will do this with our AND function.
    • =IF(AND(C2>20,C3>20,C4>20),
  • Now we answer our yes/no question in the IF function
    • =IF(AND(C2>20,C3>20,C4>20),"Yes","No")


This formula will return a "Yes" for us. The below screenshot also does the same logical test for days 2-5.



The OR function works similarly with a different output. It will return a true result if any of the conditions tested are true.


Consider the question: Did the fruit stand sell at least 50 of a single product day 1?


Take it step-by-step:

  • Start our IF Function
    • =IF(
  • Now we want to test to see if at least 50 of one product was sold on day one, which we will use our OR function for, and give the answers for our logical test.
    • =IF(OR(C2>=50,C3>=50,C4>=50),"Yes","No")

Again, this will return "Yes" for our day 1 test. The below screenshot also does the same logical test for days 2-5.


The NOT function is the simplest out of all of them, just inverting the true/false result of the logical test.


Consider our first example when discussing the IF function. We determined that the following formula returned "Yes" for its answer. If we now put the logical test from that function within a NOT function, it will return "No".


SUMIF/AVERAGEIF


The SUMIF and AVERAGEIF functions allow you to perform sum and average calculations on portions of a set given certain conditions. Though they are different functions, they use the same syntax to accomplish their goal. The following is the formula setup for the SUMIF and AVERAGEIF functions:


=SUMIF(range, criteria, [sum_range])

=AVERAGEIF(range, criteria, [average_range])


Now consider the following questions from our practice dataset:

  • How many of each product did the stand sell over the week?
  • What was the average of each product sold each day?

These questions can be answered with the SUMIF and AVERAGEIF functions respectively. Let's take it step-by-step, using the cell references in the screenshot below.


We'll start with the SUMIF function:

  • Range: This is the area that we want our function to read from and perform it's logical reasoning from the supplied criteria. In this case, we want the range to be the column that lists the products, B1:B16. Additionally, since we are building three functions that will use this same range for reference, we don't want the reference area to move when we drag the formula down through the other two selections. We want to make the cell reference absolute, which we can do with the alteration on the range $B$1:$B$16. (Pro tip: You can automatically add the $'s to the range by pressing F4 immediately after selecting the range)
    • =SUMIF($B$1:$B$16,
  • Criteria: This is what we want our function to read within the criteria to know what values to sum. In our first case, we want it to sum "Apples". We can put "Apples" into our formula, or we can reference a cell that contains that text, like E27. We don't want to make the cell reference absolute here, as we want our criteria to change.
    • =SUMIF($B$1:$B$16,E27,
  • Sum Range: This is the range that we want the sum to be performed in. In this case, we want the column with the quantities sold to be our sum range, $C$1:$C$16. We want this range reference to be absolute so it doesn't change. (Note that the sum range is an optional variable. If you do include this variable, the size of the sum range must be the same size as the reference range, otherwise our formula will return an error.)
    • =SUMIF($B$1:$B$16,E27,$C$1:$C$16)

Completing this formula will produce a result of 305 apples total sold throughout the week, see screenshot below:


Now we want to do this for the other two products, oranges and watermelons. We could recreate these formulas manually for the other two products, but there is an easier way to achieve this. Note that the cell you have selected has a small box on the bottom right corner. 



When you put your cursor over this box, it will turn into a small black cross. 



Click, hold, and drag this down through cells F28 and F29.



After you release, values will appear in cells F28 and F29. Reviewing the formulas contained within these cells shows us that the same SUMIF function is present in all of them with both reference range and and sum range remaining the same while the criteria changed in the pattern that Excel recognized we were trying to achieve. This is an easy way to turn one function into multiple of the same function with different criteria.


Now we do the AVERAGEIF calculations. Remember that the SUMIF and AVERAGEIF functions use the same syntax, so we are able to copy the variables of the SUMIF functions, change the criteria cell reference, and put it into an AVERAGEIF function to obtain our desired output.