Introduction

What are logical functions in Excel?

Logical functions in Excel help perform a diverse set of logical operations, from setting most basic conditions and linking them with different outcomes to performing complicated scenario analysis. Logical functions significantly expand the functionality of other functions in Excel, helping a user to build sophisticated models and leverage massive sets of input data.

Which logical functions in Excel are most widely used?

While all logical functions are extremely helpful in different situations, the following 4 are the most frequently used:

  • IF – to set different outcomes depending on whether a given condition is true or false
  • AND – to check whether several conditions are true at the same time
  • OR – to check whether at least one of several conditions is true
  • NOT – to return the value opposite to the value of a given condition (return FALSE if condition if TRUE and vice versa)

IF function is clearly the major one in this category while most others are usually used in conjunction with IF.

Quick examples
logical functions in excel example

Figure 1. Logical functions in Excel: example

Sample task

Let’s say you’re looking for a new apartment. You got a long list of potential options from your agent and need to decide which apartments to visit (see Figure 1). Your maximum budget is $1500, you don’t want the apartment to be smaller than 900 square feet, and you prefer not to live on the first floor. However, at a bargain price you are ready to consider the first floor as an option.

Let’s start building those conditions in a formula one by one.

How to use IF function

First, let’s set a condition that target apartment rent shouldn’t exceed our $1500 budget:

  1. Open IF function by typing =IF( in your target cell (cell G3 in our example above)
  2. Define the condition to test
    • Select or type in a cell with the rent amount: D3
    • Type in the condition for that cell: <=1500
  3. logical functions in excel if

    Figure 2. Logical functions in Excel: IF

    Define the value to return if the condition is true

    • Type a comma to move to the next argument of IF function
    • Type “yes”
  4. Define the value to return if the condition is false
    • Type a comma to move to the next argument of IF function
    • Type “no”
  5. Close IF function by typing right bracket
  6. Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)

=IF(D3<=1500,”yes”,”no”)     – the final formula in cell G3

As you can see in Figure 2, the formula filtered out item 3 in our list, because the price of $1600 is above our $1500 budget.

 

logical functions in excel insert function

Figure 3. Insert Function screen

Alternatively for steps 1 – 5 above, you can also use the Insert Function command (Figure 3):

 

  1. Click on Insert Function link (fx sign to the left of the formula bar)
  2. Fill in the parameters of IF functions in appropriate fields
  3. Click OK

How to use AND function

Now, let’s update our formula a little to include minimum apartment size of 900 square feet (Figure 4):

  1. Select cell G3 and click on the formula bar to edit it
  2. Substitute our current condition with a more sophisticated formula including AND function:
    • Open AND function before our existing D3<=1500 condition by typing AND(
    • Add the second parameter to the AND function
      • Type a comma to move to the next parameter of AND function
      • Select a cell with square footage by clicking on it or by typing E3
      • Type the square footage condition: >=900
    • Close the AND function by typing right bracket
  3. Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)

=IF(AND(D3<=1500,E3>=900),”yes”,”no”)     – updated formula in cell G3 (updates bolded)

logical functions in excel and

Figure 4. Logical functions in Excel: AND

How to use NOT function

The next step would be to specify that we don’t regularly consider apartments on the first floor (Figure 5):

  1. Select cell G3 and click on the formula bar to edit it
  2. Substitute our current condition by adding the third parameter to our existing AND function:
    • Type a comma to move to the next parameter of AND function
    • Open NOT function by typing NOT(
    • Select a cell with the floor number by clicking on it or by typing F3
    • Type in the floor number condition: =1
    • Close NOT function by typing right parenthesis
  3. Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)

=IF(AND(D3<=1500,E3>=900,NOT(F3=1)),”yes”,”no”)    – updated formula in cell G3 (updates bolded)

logical functions in excel not

Figure 5. Logical functions in Excel: NOT

How to use OR function

Finally, let’s clarify that we are ready to consider first-floor apartments when the price is low enough (Figure 6):

  1. Select cell G3 and click on the formula bar to edit it
  2. Substitute our last parameter of the AND function with a more sophisticated condition using OR:
    • Open OR function before our existing NOT(F3=1) condition by typing OR(
    • Add the second parameter to our new OR function
      • Type a comma to move to the next parameter of the OR function
      • Select a cell with monthly rent by clicking on it or by typing D3
      • Type in the maximum rent condition: <=1300
    • Close OR function by typing right parenthesis
  3. Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)

=IF(AND(D3<=1500,E3>=900,OR(NOT(F3=1),D3<=1300)),”yes”,”no”)   – updated formula in cell G3 (updates bolded)

logical functions in excel not

Figure 6. Logical functions in Excel: NOT

Test yourself...

Logical Functions Quiz

Take a quick quiz to understand how well you know logical functions in Excel:

Share This