Using logical functions in formulas

Data Preparation in Excel

Iason Prassides

Content Developer, DataCamp

Logical functions

  • Test if a condition is true or false
  • With logical functions, we can create conditional formulas
    • Choose conditions
    • Obtain different outputs
  • Four logical functions
    • AND()
    • OR()
    • NOT()
    • IF()

Image showing arrows branching into three different paths and ending at different outcomes.

Data Preparation in Excel

The AND() function

AND()

  • Syntax: AND(logical1, [logical2], ...)
  • Tests one or more conditions and outputs:
    • TRUE if both conditions met
    • Otherwise, FALSE

Example - Identify records where product is Pears and total revenue is over $2,500.

 

Example data in Excel showing the creation of a column using the AND function and two variables as conditions

Data Preparation in Excel

The OR() function

OR()

  • Syntax: OR(logical1, [logical2], ...)
  • Tests one or more conditions and outputs:
    • TRUE if at least one condition met
    • Otherwise, FALSE

Example - Identify records where product is Pears or total revenue is over $2,500.

 

Example data in Excel showing the creation of a column using the OR function and two variables as conditions

Data Preparation in Excel

The NOT() function

NOT()

  • Syntax: NOT(logical)
  • Takes one logical argument
  • Output is TRUE when the opposite of the condition is true
  • Otherwise output is FALSE

Example - Identify records where total revenue is $2,500 or less.

 

Example data in Excel showing the creation of a column using the NOT function and one variable as a condition

Data Preparation in Excel

The IF() function

IF()

  • Syntax: IF(logical_test, [value_if_true], [value_if_false])
  • Apply different outcomes based on logical test result
  • Real-life scenario - going to the shops
    • Is it raining?
      • Yes - take an umbrella
      • No - leave umbrella at home

 

Image of a woman with a pick umbrella checking if it is raining.

Data Preparation in Excel

Combining logical functions

  • Test multiple conditions to find true or false outcomes
  • Combine logical functions to create nested formulas
    • Multiple conditions to produce two outcomes

IF plus AND functions =IF(AND(logical1, [logical2], ...), [value_if_true], [value_if_false])

IF plus OR functions =IF(OR(logical1, [logical2], ...), [value_if_true], [value_if_false])

IF plus NOT functions =IF(NOT(logical), [value_if_true], [value_if_false])

Data Preparation in Excel

Combining IF() functions

  • Create nested IF formulas:

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

 

  • Real life scenario - weather check
    • Is it snowing?
      • Yes - wear a winter jacket
      • No - is it raining?
        • Yes - use an umbrella
        • No - wear normal outdoor clothes

 

Cartoon image of a person in three different type of weather conditions - sun, rain, and snow.

Data Preparation in Excel

Let's practice!

Data Preparation in Excel

Preparing Video For Download...