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

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]))

Syntax for IFS()

IFS(logical test 1, [value if true 1],[logical test 2], [value if true 2]...)

Syntax for SWITCH()

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

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

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.

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

A diagram of a row level evalution

A diagram of a column level evaluation

Example of a SUM() function

Diagram of an aggregate logical function

Diagram of a SUMIF() function

Syntax for COUNTIF()

`COUNTIF(range, criteria)`

Syntax for COUNTIFS()

`COUNTIFS(range 1, criteria 1, [range 2], [criteria 2])`

Data Analysis in Excel