Using Logical Functions
Excel’s logical functions are:
✅ AND
✅ OR
✅ IF
✅ NOT
✅ FALSE
✅ TRUE
✅ IFERROR
These logical functions are important when doing advanced work in Excel because
they can help you control the behavior of your worksheets based on specific logical
conditions.
This module will focus on the use of the logical functions IF, AND and OR.
The IF function
Excel’s IF function can often prove to be very useful. You can use this function to
branch to different values or actions depending on a specified condition. The
structure of an If function is as follows: IF (logical test, value if true, value if false)
IF functions are called conditional functions because the value that the function
returns will depend on whether or not a specific condition is satisfied. As an
example, consider the following function: IF (A1=10, 5, 1)
This function states that if cell A1 has a value of 10 the cell that contains the
function will have the value of 5. But if A1 doesn’t have a value of 10, the cell that
contains the function will have a value of 1. In other words, the function reads: if A1
equals 10 then return the number 5, else, return the number 1. Let’s say that this
next IF function is entered into cell B2: IF (A1<=100, A1*.5, C3*2)
This function states that if the contents of cell A1 is less than or equal to 100, the
value in cell B2 will be the value in A1 multiplied by .5; else, the value in B2 will be
the value of cell C3 multiplied by 2.
You can insert an IF function by invoking the Insert Function dialogue and looking
under the Logical category, or by typing it directly into the formula bar.
The logic of the IF function can be a little confusing until you get used to it. The best
way to get comfortable with IF functions, is to practice using them.
Using the Function Library
Excel 2021 contains an extensive library of functions that you can call upon to help
you solve problems. These tools are available in the Function Library button group,
on the Formulas ribbon.
The first and largest button is Insert Function. This button will open a dialog
allowing you to search for and insert hundreds of functions.
You can also click the small fx button next to the formula bar to display the Insert
Function box.
Clicking the Insert Function button activates the Insert Function dialogue box and
provides access to the large range of functions available in Excel.
Once the Insert Function dialogue box is open:
Select the function you wish to use from the available list and click OK or
Type the name of the function you wish to use in the Search for a function
area, press
Enter, select the function when it appears in the list and click OK.
Manually entering a function
If you know which function you wish to use, you can enter a function into a
worksheet by inputting it manually (i.e. by typing the function directly into a cell).
When you do this in Excel 2021, a screen tip will appear with the possible functions
that correspond with the letters of the function name you have entered.
For example, if you type =IF into a cell the following appears.
If there is more than one function listed, you can toggle between them by using the
arrow up or arrow down keys.
Once you have selected the function you wish to use, continue by typing in a left
bracket, which will cause the function syntax to display in another screen tip.
Continue to enter all the arguments required for the function to give you a result,
ensuring you type a comma between each argument.
Press Enter to display the result.
The AND Function
The AND function will return true if all of its arguments are true. If one argument is
false, the AND function will return false. For example, the logical statement 1<10 is
true and the statement 2>1 is also true. As a result, the compound statement 1<10
AND 2>1 is true, because both of the statements that are being joined by AND are
true. The following image demonstrates this in Excel.
Notice that the arguments joined by AND are placed in parenthesis and separated
by commas. The Excel function =AND(1<10, 2>1) means the exact same thing as
saying 1<10 AND 2>1 in plain English. Because 1 is less than 10 and 2 is greater
than 1, the Excel AND function returns the logical value True.
You can have as many as 255 logical arguments to an Excel AND function. You can
enter these arguments directly, or use cell references as the situation requires. (All of
the arguments should be separated by commas.)
The following table (often referred to as a truth table) may help you understand the
AND function. In the last column of the truth table, you will see the value returned
by the function, according to the corresponding values of the function arguments
The OR Function
The logical OR function will return true if one or more of the arguments to the
function are true. For instance, the compound statement 1<10 OR 2<1 is true, since
1 is less than 10. It does not matter that the statement 2<1 is false, because you
have a choice of either 1<10 OR 2<1 to make the compound statement true. If one
or both of the arguments is true, the overall OR statement will also be true.
In Excel, you compose an OR function like this:
If one or more of the function arguments is true, the OR function will return true.
Notice the function and its comma separated arguments in the formula bar.
Here is a truth table for the OR function.
Nested IF functions
The IF function is ideal for making choices based on logical tests. Furthermore, you
can nest IF functions one inside another. =IF(A1=10,100,IF(A1=5,200,””))
In the case of this IF function, if the value in cell A1 is 10 the function will return 100.
If the value in A1 is not 10, the function will test if the value in A1 is 5. If the value in
A1 is 5, the function will return 200. If it is not 5 (and also not 100) the function will
display a blank cell..
When you nest logical functions, you must make sure that the number of closing
parenthesis matches the number of opening parenthesis used in the function. If you
count the parenthesis in the function from the image above, you will see two
opening, and two closing parenthesis..
Applying Logical Functions
The spreadsheet in the image shown above is used to calculate the future budget
increase for different divisions of a fictional company. If you look carefully, you will
notice that the Miami division has had its budget increased from 1,500,000 to
1,800,000. Also, the Chicago division has had its budget increased from 2,000,000 to
2,400,000. The question that arises here is, “What reasoning was used to arrive at
these budget increases for these particular cities?
For the answer to this question, look at the formula bar when a cell from the Future
Budget Increase for Division column is made active.
When cell F2 is the active cell, you can see the formula from cell F2 in the formula
bar.
=IF(OR(C2-D2>500000,AND(C2>D2,E2>20%)),B2*1.2,B2)
To understand this formula as a whole, you must first understand how each logical
function is used in the formula. To start, remember that the IF function will return a
certain value based on a logical test.
=IF(logical test ,value if true, value if false)
In this case, the logical test is:
OR(C2-D2>500000,AND(C2>D2,E2>20%))
The OR function will return true if one or more of its arguments are true. The first
argument in the OR function is C2-D2>500000. This is a simple argument that will
return true when the value of cell D2 subtracted from cell C2 is greater than
500,000.
The second argument AND(C2>D2,E2>20%) is a logical AND function. This function
will return true only when both of its arguments are true. That is, the AND function
12 0207 987 3777 www.MicrosoftTraining.net
will return true only when the value in C2 is greater than the value in D2, and the
value in E2 is grater than 20%.
If the first argument to the OR function is false, the AND function must return true
for the OR function to be true. Alternatively, if the AND function returns false, the
first argument to the OR function must return true for the OR function itself to
return true.
0 Comments