5.4: Logical Functions

Last updated
Save as PDF

Page ID: 14249

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

Many employers and educators are placing increasing emphasis on the value of critical thinking. Critical thinking skills are often associated with problem solving skills, empathy, and thinking and acting in purposeful ways by making fact-based decisions. Excel is a practical tool for collecting and analyzing data critically. Excel’s logical functions are especially useful for enabling critical thinking. However, before exploring the utilization of Excel’s logical functions, it is necessary to review the logical operators that are used to compare data between cells. The operators are often referred to as comparison operators. The six main logical operators are explained in the following graphic:

These comparison operators can be used in Excel functions to compare all types of data, including dates, numbers, Boolean values (True or False, Yes or No), or text. All arguments must evaluate to the Boolean values of either True or False, or contain another, nested logical function.

The IF function is one of the most popular and useful functions in Excel. It can be used to make a decision based on a comparison. If the comparison is true, one value is displayed in the cell; if the comparison is false, a second value is displayed. The syntax of the IF function is: IF(logical_test, [value_if_true], [value_if_false]).

logical_test – a value or logical expression that can be either TRUE or FALSE. Required.In this argument, you can specify a text value, date, number, or any comparison operator.
value_if_true – the value to return when the logical test evaluates to TRUE, i.e. if the condition is met. Optional.
value_if_false – the value to be returned if the logical test evaluates to FALSE. Optional.

Simple IF functions test to see if a cell contains a certain text string, and returns different values depending on if the logical test returns a True or False logic. A sample formula is illustrated in the screenshot below. That’s some logical critical thinking!

Instead of returning text strings, the IF formula can test the specified condition, perform a corresponding math operation and return a value based on the result. This is done by using arithmetic operators or other Excel functions in the value_if_true and /or value_if_false arguments. For example, =IF(C4>=D4, E4*.25, E4*10%). This formula is determining if the value in column C is greater than or equal to the value in column D. If the result of the comparison is true, then the value to be displayed should be the value in column E multiplied by .25 (25%). If the result of the comparison is false, then the value to be displayed should be the value in column E multiplied by 10% (.10).

Other popular Excel Logical functions include the AND, as well as the OR functions. These functions allow users to create multiple logical tests within a single IF function. These functions return either TRUE or FALSE results when their arguments are evaluated. The AND function only returns TRUE if every condition is met. However, the OR function returns TRUE if any condition is met. The syntax of both functions are the same: =AND(logical1, logical2, etc.) Examples of each function are illustrated in the screenshots below. The first illustration uses the AND function to determine if any of the people are eligible for the Inner Circle. They need to have a Customer Satisfaction Score of at least 4.0, and be a Vikings fan. The second example uses the OR function. The more lenient comparison operator yields more eligible people for the Inner Circle.