Logical Tests in Excel: 4 Most Useful Functions

Important! In this sub-chapter, you will learn the 4 most popular functions for performing logical tests in Excel.

When working with Excel, you will often encounter situations where you have to perform logic tests.

For example, determining student exam results, employee gender, and calculating sales discounts with conditions.

If you only have a small amount of data, it’s fine to calculate it without performing logical tests. But what if you have thousands of rows of data?

One-by-one calculations will waste your time – Waste your money!

It’s time! You need to understand how to perform Excel logical tests and what formulas/functions to use.

So, don’t waste your time, immediately follow the logical tests in Excel in this guide.

What is Logical Test in Excel? And Why is it Important?

Logical Tests in Excel are evaluations that check whether a condition is TRUE or FALSE. These tests are fundamental for analyzing data, automating tasks, and creating a dynamic spreadsheet.

Excel logical tests play an important for the following reasons:

1. Enhanced Data Analysis: They allow you to sift through large datasets and pinpoint specific information that meets your criteria. Imagine a sales spreadsheet with hundreds of entries. Logical tests in Excel can help you filter data to find all sales above a certain amount in a specific region, performed by a particular salesperson, all within seconds. This targeted analysis empowers you to identify trends and make data-driven decisions.
2. Streamlined Workflows: Excel Logical tests automate repetitive tasks. For instance, you can use them with conditional formatting to automatically highlight negative values in red, saving you time from manually formatting each cell. Similarly, logical tests in Excel can be used in formulas to perform calculations only if specific conditions are met, reducing the need for complex manual adjustments.
3. Smarter Spreadsheets: Logical tests in Excel add intelligence to your spreadsheets. By incorporating them into functions like IF statements, you can create dynamic spreadsheets that react and adapt based on the data they hold. For example, an IF statement with a logical test can automatically assign a “Pass” or “Fail” grade based on a calculated score.
4. Conditional Formatting Customization: Excel Logical Tests unlock the full potential of conditional formatting. You can define intricate rules that change the appearance of cells based on specific conditions. This allows you to visually identify patterns, outliers, and important data points within your spreadsheets at a glance.
5. Powerful Function Applications: Many advanced Excel functions leverage logical tests to perform complex operations. For instance, VLOOKUP and HLOOKUP functions use logical tests to search for specific values within tables based on defined criteria. This allows you to efficiently extract and analyze data from different parts of your spreadsheet.

In essence, logical tests transform Excel from a simple data storage tool into a powerful platform for data manipulation, analysis, and automation.

They empower you to work smarter, not harder, by extracting valuable insights from your data.

4 Most Popular Functions for Logical Tests in Excel

Currently, there are 9 functions to perform logic tests in Excel. 4 of them are very popular among data analysts. They are the IF, AND, OR, and NOT functions.

Mastering these four logical functions will help you compare data, test data against criteria, check data types, and refine other functions/formulas based on calculation results.

For example, an IF formula can determine results based on predetermined criteria. Meanwhile, the AND, OR, and NOT functions can produce TRUE or FALSE based on testing criteria.

So, to make this guide easier to understand, please download the following Excel Logical Function file:

#1 – IF Function: Excel Logical Tests For One or More Conditions

The IF function is a function that is useful for testing whether a condition (criterion) is met and then returning a value if it is true and another value if it is false.

For example, I want to know which of the following five students passed the exam:

The following criteria I use for logic tests:

• If the score is greater than or equal to 75, then Pass.
• If the value is less than 75, then Fail.

To test logic using the IF function, please follow the following steps:

1. Type the IF Function in cell C2 as follows: =IF(B2>=75,"Pass","Fail"). Then press Enter.
2. To test the values of Putri, Budi, Dedek, and Adhe, please use Excel’s AutoFill feature to copy the formula/function automatically. (Learn Excel AutoFill). You can see the results in the following image:

With these results, you already know who passed the exam and who didn’t. Below I explain the meaning of the IF function used:

IF Function Formula Breakdown:
=IF(B2>=75,“PASS”,“FAIL”)

Explanation:
If the value in cell B2 is greater than or equal to 75, then PASS, apart from that it FAIL.

#2 – AND Function: Logical Test in Excel for Multiple Conditions

The AND function can be useful for testing whether the logic of 2 or more cells meets the requirements or not.

Important! Please learn the guide to rows, columns, cells, and ranges in Excel to make it easier for you to use formulas and functions.

In its use, the AND function only produces “TRUE” and “FALSE” values, it isn’t text/values that you can define yourself.

For example, I have a list of student pre-tests and post-tests like the following picture:

I want to know which students passed the exam using the following criteria:

• If the pre-test score is greater than or equal to 75 and the post-test score is greater than or equal to 70, then “TRUE”.
• If the pre-test score is less than 75 and the post-test score is less than 70, then “FALSE”.

From the criteria above, please follow the logical tests in Excel using the following AND function:

1. Type the AND function in cell D2 as follows: =AND(B2>=75,C2>=70).
2. Do Excel AutoFill (Click-Hold-Drag) like the previous method to test other student test results. You can see the results in the following image:

You can see, only Putri has a pre-test score greater than or equal to 75 and a post-test score greater than or equal to 70. So Excel returns the value TRUE. Meanwhile, other students do not meet the criteria.

Rolan had a pre-test score of less than 75 (did not pass the criteria) and a post-test score of less than 70 (did not pass the criteria). Then Excel returns FALSE.

Budi and Dedek had post-test scores greater than 70 (passed the criteria), but pre-test scores were smaller than 75 (did not pass the criteria). Then Excel returns FALSE.

Meanwhile, Adhe had a pre-test score greater than 75 (passed the criteria), but the post-test score was smaller than 70 (did not meet the criteria). Then Excel returns FALSE.

Below I explain the breakdown of the AND function used:

AND Function Formula Breakdown:
=AND(B2>=75,C2>=70)

Explanation:
Return TRUE if the value in cell B2 is greater than or equal to 75 and the value in cell C2 is greater than or equal to 70. If either is not true, return FALSE.

#3 – OR Function: One of the Conditions Must Be Met

The OR function can be useful for logical testing in Excel that evaluates whether data meets one (can be more) criteria or not.

In its use, if one (or more) criteria is met, the OR function will return “TRUE”. Conversely, if none of the criteria is met, it will return “FALSE”.

Using the same example as the AND function above, here are the steps for logical tests in Excel using the OR function:

1. Type the OR function in cell D2 as follows: =OR(B2>=75,C2>=70)
2. Do Excel AutoFill (Click-Hold-Drag) as before. Results:

You can see, only Rolan has a pre-test score smaller than 75 and a post-test score smaller than 70. Both criteria are not met, so Excel returns FALSE.

Putri’s post-test score meets both criteria, then Excel returns TRUE.

Meanwhile, if one of Budi, Dedek, and Adhe’s test scores meets the criteria, Excel returns TRUE.

Below I explain a breakdown of the OR function used:

OR Function Formula Breakdown:
=OR(B2>=75,C2>=70)

Explanation:
Return TRUE if the value in cell B2 is greater than or equal to 75 or the value in cell C2 is greater than or equal to 70. If neither criterion is met, then return FALSE.

#4 – NOT Function: Exceptions In Logical Tests in Excel

The NOT formula/function is useful for reversing facts or as an exception in criteria.

For example, I want to know which students do not have a pre-test score greater than or equal to 75.

So, Excel will only return TRUE for students who have a pre-test score lower than 75.

With these criteria, please take the logical tests in Excel using the following NOT function:

1. Type the NOT function in cell D2 as follows: =NOT(B2>=75)
2. Do Excel AutoFill (Click-Hold-Drag) as before. You can see the results in the following image:

You can see, that only Putri and Adhe have a pre-test score greater than 75, so Excel returns FALSE.

Meanwhile, other students had pre-test scores lower than 75, so Excel returned TRUE.

Below I explain a breakdown of the NOT function used:

NOT Function Formula Breakdown:
=NOT(B2>=75)

Explanation:
Return TRUE if the following criteria are not met: The value in cell B2 is greater than or equal to 75. If the criteria is met, return FALSE.

Note: The NOT function is very rarely used as a single function. However, you combine this function with other functions (such as IF, VLOOKUP, etc) for advanced logic testing.

Next, please improve your Excel skills by studying the following related guides. Or visit the Excel guide center at M Jurnal to upgrade yourself to become an Excel expert.