Diving deeper with logical functions

Data Analysis in Excel

Nick Edwards

Analyst at Mynd

Customer segmentation

  • Customer segmentation divides a business' customer base into distinct groups based on their behaviors and characteristics
    • Based on various criteria:
      • Demographics
      • Geographic location
      • Purchasing behavior
    • Gain a better understanding of their customers
    • Improve the product, marketing or services

A pile of laundry to organize

Data Analysis in Excel

Nesting logical tests using IF

  • Nesting functions are a technique where two or more functions are used in a single formula.

Syntax for IF()

IF(logical test, [value if true], [value if false])

Syntax for a nested IF()

IF(logical test, [value if true], 
    IF(logical test, [value if true], 
    ..., [value if false]))
Data Analysis in Excel

Nesting logical tests using IFS

Syntax for IFS()

IFS(logical test 1, [value if true 1],[logical test 2], [value if true 2]...)
  • Easier than nesting multiple IF()s together
  • Returns the value for the first TRUE test
  • There is no [value if false]
    • Returns #N/A if none of the tests are true
Data Analysis in Excel

SWITCHing values

Syntax for SWITCH()

SWITCH(expression, value 1, result 1, [default or value 2],[result 2],...)
Data Analysis in Excel

SWITCHing values

Syntax for SWITCH()

SWITCH(expression, value 1, result 1, [default or value 2],[result 2],...)

Example: SWITCH the value in cell C1 based on the corresponding list:

Answer: SWITCH(C1,0,FALSE,1,TRUE)

Value Result
0 FALSE
1 TRUE
Data Analysis in Excel

SWITCHing values

Syntax for SWITCH()

SWITCH(expression, value 1, result 1, [default or value 2],[result 2],...)

Example: SWITCH the result in 2 - 1 to TRUE if it equals 1, and FALSE if it equals 0 or 2.

Value Result
0 BLUE
1 GREEN
2 RED

Answer: SWITCH(2-1,0,"BLUE",1,"GREEN",2,"RED") --> 2-1 = 1 --> Green

Data Analysis in Excel

Row level evaluations

A diagram of a row level evalution

Data Analysis in Excel

Aggregate functions

A diagram of a column level evaluation

Data Analysis in Excel

Aggregate functions

Example of a SUM() function

Data Analysis in Excel

Aggregate logical functions

Diagram of an aggregate logical function

Data Analysis in Excel

Aggregate logical functions

Diagram of a SUMIF() function

Data Analysis in Excel

Syntax for COUNTIF and COUNTIFS

Syntax for COUNTIF()

`COUNTIF(range, criteria)`

Syntax for COUNTIFS()

`COUNTIFS(range 1, criteria 1, [range 2], [criteria 2])`
  • Each criteria must be TRUE
Data Analysis in Excel

Let's practice!

Data Analysis in Excel

Preparing Video For Download...