Quick Links
Excel’s logical functions test whether a statement or data is true or false, before enabling the program to carry out an action based on the result. They are useful for analyzing data, automating certain tasks or calculations, and, ultimately, making decisions.
When used indendently, they return TRUE or FALSE, depending on whether the criteria you set are met.
I’ll run through three logical functions (or groups of functions) that I find particularly helpful, and how you can also make the most of them in real-world scenarios.
IF With AND, OR, and NOT
Before I explain how these functions can be used together, I’ll explain what each does separately.
AND, OR, and NOT on their own help determine the conditions. In the example below, I want to work out three things for each applicant:
In cell F2, I need to type
because I want Excel to tell me whether the value in B2 is greater than 18andthe value in D2 is equal to “Gold”.
In cell G2, I’ll type
because I want Excel to identify whether the value in C2 is “Full"orthe value in E2 is “Yes”.
Finally, in cell H2, I’ll need to type
because I need to check that the value in cell B2 is not greater than 50.
When creating logical arguments, all text needs to go inside double quotes.
Then, I’lluse Excel’s AutoFillto copy the formulas to the remaining rows.
Notice how each output is either TRUE or FALSE, depending on whether the conditions have been met. While this is handy, having a specific indicator word or phrase can be even more useful. That’s where IF comes into play, as it returns a specific value depending on the set conditions.
This time, I want to achieve the following outcomes for each applicant:
So, in cell F2, I’ll type
because I want Excel to check that the value in B2 is greater than 18andthe value in D2 is “Gold”, and then return “Senior” if both are true or “Junior” if not.
In cell G2, I need to go with
because if the value in C2 is “Full"orthe value in E2 is “Yes”, I need to know that they “Can travel”. If neither argument is correct, they “Cannot travel”.
Finally, in cell F2, I’ll type
because if the value in B2 isnotgreater than 50, the applicant is “Eligible for scholarship”, but if it is greater than 50, it’s “No scholarship”.
Again, I will drag the AutoFill handles down for all three columns to populate the remaining rows of my table.
Tryusing IF, AND, and OR with checkboxes in Excelto track task progress. you may also opt forthe XOR function, which, in its simplest form, returns TRUE if only one is TRUE, or FALSE if both values are TRUE.
IFERROR
I use IFERROR to keep all my spreadsheets tidy. After all, nobody wants a spreadsheet full of #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL! errors, and the IFERROR helps to prevent this.
In this example, I am tracking the goals-per-game ratio of ten players. To do this, I typed
into cell D2, and then extended this calculation down the whole of column D.
However, because player C has not played any games, the division returns #DIV/0!.
To tidy this up, I would embed all calculations in column D within the IFERROR function.
wherexis the calculation being performed, andyis the value to return if there is an error.
If you leaveyblank after the comma, Excel will return 0 for an erroneous calculation.
So, in cell D2, I will type
and extend this down the column. Notice how the
calculation is still there, but it’s embedded within the IFERROR function. In this case, anytime there is an error in the calculation, it will return a dash, which looks much tidier than an error message.
Bear in mind that using IFERROR to hide the errors in the spreadsheet can make identifying calculation errors more difficult. This is why I only tend to use it in scenarios when #DIV/0! would otherwise appear.
IS With IF
There are several IS functions, and each of them does very different things. I use them often (within the IF function) to check whether there are any errors or inconsistencies within my data.
Before we look at how to combine them with the IF function, let’s look at them in isolation.
All IS functions have the same syntax:
where [TYPE] is the type of IS function you want to use, andais the cell reference or value to be evaluated.
Here are the types of IS functions you may choose from, and you can see some examples below:
Even though they start with IS, the ISPMT andISOWEEKNUM functionsare not part of the IS group.
Let’s look at some of these in an Excel spreadsheet. In columns B to J, I used the specified IS functions to test the values in column A.
For example, in cell B2, I typed
and in cell G4, I typed
If your IS formula references a cell ina formatted Excel table, the value in the parentheses would be the column name. In the example above, typing
into cell F1 would then automatically apply it to the other cells in column F in the table.
To use the IS functions with IF, you need to embed the former inside the latter.
where [TYPE] is the type of IS function you want to use from the list above,ais the cell reference or value to be evaluated,bis the value or formula if TRUE, andcis the value or formula if FALSE.
In the example below, I wanted to work out my employees' projected weekly profits based on their daily profits, but produce a message if the daily profit is blank. To do this, I typed this formula into C2:
because I wanted Excel to work out whether cell B2 was blank, and either return “Data required” (if B2 was blank) or multiply the value in B2 by seven (if it wasn’t blank). I thenused AutoFillto copy the relative formula to the remaining cells in column C.
You could do the same with any of the IS functions listed above.
As well as using Excel’s logical functions, I use various othercombinations of functions to evaluate and use data in Excel’s tables, such as INDEX with MATCH, and COUNTIF with SUM. If you’re not familiar with these, it’s certainly worth giving them a go!
