Logical Functions in Excel


Excel Logical Functions

There are eleven logical functions in Excel. They include IF, AND, OR, and NOT functions. These help analysts make decisions. These logical functions are on the Formulas ribbon and the Logical button.


Logical Functions

Excel Functions

You can find a list of Excel functions in the Formulas ribbon. Each icon represents a category of functions.

  • Recently Used
  • Financial
  • Logical
  • Text
  • Date & Time
  • Lookup & Reference
  • Math & Trig
  • Statistical
  • Engineering
  • Cube
  • Information
  • Compatibility
  • Web
Excel Functions

Logical Functions Table

FunctionDescription
ANDreturns TRUE only if all arguments are TRUE
FALSEreturns the logical value FALSE
IFchecks whether a condition is met, returns one value if TRUE and another value if FALSE
IFERRORreturns a specified value if expression is an error and the function value otherwise
IFNAreturns a specified value if the expression resolves to #N/A, and the function value otherwise
IFSruns multiple tests and returns a value corresponding to the first TRUE result
NOTchanges TRUE to FALSE, or FALSE to TRUE
ORreturns TRUE if any arguments are TRUE
SWITCHreturns a result on the first matching value
TRUEreturns the logical value TRUE
XORreturns a logical “exclusive or” of all arguments

Using multiple IF functions in the same formula is called a Nested IF function.

OperatorDescriptionExample
=equal toA1=E1
>greater thanA1>E1
>=greater than or equal toA1>=E1
<less thanA1<E1
<=less than or equal toA1<=E1
<>not equal toA1<>E1
Excel Logical Operators

AND

Logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. Returns TRUE if all arguments are TRUE. If one argument is FALSE, it returns FALSE.

=AND (logical1, [logical2], …)

logical1 – The first condition or logical value to evaluate.
logical2 – [optional] The second condition or logical value to evaluate.

FALSE

The FALSE function returns the logical value FALSE. There are no arguments but the parentheses are required.

=FALSE()

IF

The IF function can perform a logical test and returns one value if TRUE, and another value if FALSE. More than one condition can be tested by nesting IF functions. IF function can be combined with logical functions like AND and OR.

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

logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
value_if_false – [optional] The value to return when logical_test evaluates to FALSE.

IFERROR

The IFERROR function returns value_if_error if expression is an error and the value of the expression itself otherwise.

= IFERROR(value,value_if_error)

Value: is any value or expression or reference.
Value_if_error: is any value or expression or reference.

IFNA

The IFNA function returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.

=IFNA(value,value_if_na)

  • Value: is any value or expression or reference.
  • Value_if_na: is any value or expression or reference.

IFS

Runs multiple tests and returns a value corresponding to the first TRUE result. The IFS function uses multiple conditions without nested IF statements. IFS allows easier to read formulas. The IFS function can be used instead of nested IF statements.

=IFS (test1, value1, [test2, value2], …)

test1 – First logical test.
value1 – Result when test1 is TRUE.
test2 – Second logical test [optional].
value2 – Result when test2 is TRUE [optional].

The IFS function debuted in Excel 2019.

NOT

Returns the opposite of a given logical or boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.

=NOT (logical)

logical – A value or logical expression that can be evaluated as TRUE or FALSE.

OR

Logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. TRUE if any arguments evaluate TRUE; FALSE if not.

=OR (logical1, [logical2], …)

logical1 – The first condition or logical value to evaluate.
logical2 – [optional] The second condition or logical value to evaluate.

SWITCH

The SWITCH function evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned.

=SWITCH(expression,value1,result1,default_or_value2,result2,…)

Expression – is an expression to be evaluated.

TRUE

The TRUE function returns the logical value TRUE. There are no arguments but the parentheses are required.

=TRUE()

XOR

Returns a logical “exclusive or” of all arguments.

XOR(logical1,logical2,…)

Logical – logical1,logical2,… are 1 to 254 conditions you want to test that can be either TRUE or FALSE and can be logical values, arrays, or references.

Excel Conditional Formatting

Conditional formatting is located on the Home ribbon and Conditional Formatting button. This identifies cells based on values such as top 10%, bottom 10, greater than, or scales of top, bottom, or middle.

Highlight Cells Rules

Highlight cells option shows numbers that are greater than, less than, equal to, some specified value. It can also be used to show duplicate values or a specific text string.

  • Greater Than
  • Less Than
  • Between
  • Equal To
  • Text That Contains
  • A Date Occurring
  • Duplicate Values
Conditional Formatting: Highlight Cells

Top/Bottom Rules

Top/Bottom rules show the top 10 items, top 10%, bottom 10, bottom 10%, above average, or below average. These numbers can be changed so that only the top 3 are shown or the bottom 15% is revealed.

  • Top 10 Items
  • Top 10%
  • Bottom 10 Items
  • Bottom 10%
  • Above Average
  • Below Average
Conditional Formatting: Top/Bottom Rules

Data Bars

Data Bars show the relative size of a list of numbers. The largest numbers fill the cell with a fill color (blue, red, green, etc.). The gradient fill is more transparent and the solid fill is less transparent.

  • Gradient Fill
  • Solid Fill
Conditional Formatting: Data Bars

Color Scales

Color Scales show the relative size by colors in four quartiles. For example, in the Green Yellow Red scale, the higher numbers are green, the middle numbers are yellow and the smallest numbers are red. 

The following list is not complete:

  • Green Yellow Red – green is the highest and red is the lowest, green fades to yellow which fades to red
  • Red Yellow Green – red is the highest and green is the lowest, red fades to yellow which fades to green
  • Blue White Red – blue is the highest and red is the lowest, blue fades to white which fades to red
  • White Red – white is the highest and fades to red the lowest
  • Green White – green is the highest and fades to white the lowest
Conditional Formatting: Color Scales

Icon Sets

Icon Sets show the data in 3, 4, or 5 categories by size. 

  • Directional – arrows going up, horizontal, or down
  • Shapes – circles, triangles, or traffic lights
  • Indicators – flags or warnings
  • Ratings – stars, pie charts, and other symbols
Conditional Formatting: Icon Sets



Jeff Mankin

Jeff Mankin teaches financial literacy. His website is FinallyLearn.com.

Recent Posts

link to Expense

Expense

What is an expense? Expense is a cost to operate a business to produce revenue. In accounting, an expense occurs when an asset is used. This could include a cash outflow or consuming an...