MS 365 Excel Basics 05:
IF Function and Logical Test. IFS, IFNA, OR, AND, ISNUMBER Functions & More!
Table of Contents
IF Function arguments ............................................................................................................................................ 2
Logical Tests ........................................................................................................................................................... 2
Types of Logical Tests .............................................................................................................................................. 4
Comparative Operators ........................................................................................................................................... 4
IS Functions ........................................................................................................................................................... 5
AND, OR and NOT Function arguments .................................................................................................................... 5
AND and OR Logical Test example from video #2 ...................................................................................................... 6
IF Function to deliver one of two items ..................................................................................................................... 7
Use IF Function in an Invoice and use “” A Zero Length Text String ............................................................................. 8
IF with AND function ............................................................................................................................................... 9
IF with OR Function ................................................................................................................................................. 9
IFS function ...........................................................................................................................................................10
IFNA function arguments........................................................................................................................................10
IFNA function example ...........................................................................................................................................10
IFERROR Function arguments ................................................................................................................................11
When Not To Use the IFERROR function ..................................................................................................................11
Array Constant .......................................................................................................................................................11
Page 1 of 11
IF Function arguments
Logical Tests
• A logical test is an expression (formula) that evaluates to one of only two possible values TRUE or FALSE.
• TRUE and FALSE values are called logical values or Boolean values (after mathematician George Boole).
• A logical test can have one or more requirements called conditions or criteria.
o If you ask the question: "Are sales greater than or equal to 50,000?", the condition is >=50000.
o If you ask the question "Is the value a number?", the condition is Is the data type a number?
• You can use comparative operators (>, >=, <, <=, =, <>) to create individual logical tests.
o When you use comparative operators, you place the operator directly between values such as:
65000>50000 or G20>G19. This is different than with the SUMIFS and similar functions, where the
comparative operator is a text value. Example:
o You can use IS functions (like ISNUMBER or ISTEXT) to create individual logical tests. Example:
Page 2 of 11
• You can use the aggregate functions AND, OR or NOT to create AND, OR or NOT Logical Tests. Example:
• Except in the Power Query tool, logical tests are not case-sensitive, so "Quad" = "quad" = TRUE. Example:
• Except in the Power Query tool, any non-zero number is interpreted as TRUE and zero is interpreted as FALSE.
• Any math operation on logical values converts TRUE to 1 and FALSE to 0 (zero). Example:
Page 3 of 11
Types of Logical Tests
• Single Condition Logical Test = Single condition must match.
• NOT Logical Test = Checks whether two items are not equal. A NOT Logical Test will also convert a TRUE to
FALSE and a FALSE to TRUE.
• OR Logical Test = Run two or more logical tests and one or more tests must equal TRUE for the OR Logical Test
to deliver a TRUE.
o Four possibilities for an OR Logical Test with two tests:
▪ TRUE, TRUE = TRUE
▪ FALSE, TRUE = TRUE
▪ TRUE, FALSE = TRUE
▪ FALSE, FALSE = FALSE
o The math operator for an OR Logical Test is the plus operator: +.
▪ TRUE + TRUE = 1 + 1 = 2
▪ FALSE + TRUE = 0 + 1 = 1
▪ TRUE + FALSE = 1 + 0 = 1
▪ FALSE + FALSE = 0 + 0 = 0
• AND Logical Test = Run two or more logical tests and all tests must equal TRUE for the AND Logical Test to
deliver a TRUE.
o Four possibilities for an AND Logical Test with two tests:
▪ TRUE, TRUE = TRUE
▪ TRUE, FALSE = FALSE
▪ FALSE, TRUE = FALSE
▪ FALSE, FALSE = FALSE
o The math operator for an AND Logical Test is the multiplication operator: *
▪ TRUE * TRUE = 1 * 1 = 1
▪ TRUE * FALSE = 1 * 0 = 0
▪ FALSE * TRUE = 0 * 1 = 0
▪ FALSE * FALSE = 0 * 0 = 0
• BETWEEN Logical Test = Is a type of AND Logical Test that tests whether a value is between a lower and upper
limit ,like: Is 15 between 10 and 19?
Comparative Operators
Page 4 of 11
IS Functions
AND, OR and NOT Function arguments
Page 5 of 11
AND and OR Logical Test example from video #2
Page 6 of 11
IF Function to deliver one of two items
Page 7 of 11
Use IF Function in an Invoice and use “” A Zero Length Text String
Page 8 of 11
IF with AND function
IF with OR Function
Page 9 of 11
IFS function
IFNA function arguments
IFNA function example
Page 10 of 11
IFERROR Function arguments
When Not To Use the IFERROR function
Array Constant
Array Constant = Hard code an array of values into formula.
Array Syntax:
{ } House the array
, = Column
; = Row
Example {1,2,3} in the formula SUM(LARGE(C17:G17,{1,2,3} to fore the LARGE function to deliver the three largest
values to the SUM function, so that SUM can add the three largest value.
Page 11 of 11
