Logical Functions

Excel’s logical functions are a key building block of many advanced formulas. Is it Green?
Logical functions return the boolean values TRUE or FALSE. If you need a
primer on logical formulas, this video goes through many examples.

AND, OR and NOT

The core of Excel’s logical functions are the AND function, the OR func-
tion, and the NOT function. In the screen below, each of these function is
used to run a simple test on the values in column B:

IF and IFS functions

The IF function is one of the most used functions in Excel. In the screen VIDEO
below, IF checks test scores and assigns “pass” or “fail”:
How to build
logical formulas

GUIDE
50 examples of
formula criteria
The logical functions above can be combined with the IF function to create
more complex logical tests. Alternatively, multiple IF functions can be
nested together to return more than two values as a result.

101 EXCEL FUNCTIONS 11

New in Excel 2019 and Office 365, the IFS function can run Quick Navigation
multiple logical tests without nesting IFs. ABS AGGREGATE AND
AVERAGE AVERAGEIF
AVERAGEIFS CEILING
CHAR CHOOSE CLEAN
CODE COLUMN COLUMNS
CONCAT CONCATENATE
CONVERT COUNT COUNTA
COUNTBLANK COUNTIF
COUNTIFS DATE DATEDIF
DAY EDATE EOMONTH
EXACT FILTER FIND
FLOOR GETPIVOTDATA
HLOOKUP HOUR
HYPERLINK IF IFERROR
IFNA IFS INDEX INDIRECT
INT ISBLANK ISERROR
ISEVEN ISFORMULA
ISLOGICAL ISNUMBER
ISODD ISTEXT LARGE
IFERROR and IFNA LEFT LEN LOOKUP LOWER
The IFERROR function and IFNA function can be used as a simple way to MATCH MAX MAXIFS
trap and handle errors. In the screen below, VLOOKUP is used to retrieve MID MIN MINIFS MINUTE
cost from a menu item. Column F contains just a VLOOKUP function, with MOD MODE MONTH
no error handling. Column G shows how to use IFNA with VLOOKUP to MROUND NETWORKDAYS
display a custom message when an unrecognized item is entered. NOT NOW OFFSET OR
PROPER RAND RANDARRAY
RANDBETWEEN RANK
REPLACE RIGHT ROUND
ROUNDDOWN ROUNDUP
ROW ROWS SEARCH
SECOND SEQUENCE
SMALL SORT SORTBY
SUBSTITUTE SUBTOTAL
SUM SUMIF SUMIFS
SUMPRODUCT TEXT
TEXTJOIN TIME TODAY
TRANSPOSE TRIM
UNIQUE UPPER VLOOKUP
WEEKDAY WEEKNUM
Whereas IFNA only catches an #N/A error, the IFERROR function will catch WORKDAY XLOOKUP
any formula error. XMATCH YEAR YEARFRAC

Table of Contents

101 EXCEL FUNCTIONS 12