Excel Logical Functions (Table of Contents)
Introduction to Logical Functions in Excel
Logical functions in Excel work with conditions. They help deal with different situations accordingly. These functions perform work similar to IF-ELSEIF-ELSE statements in a programming language. All of these functions have simple syntaxes and they can be beautifully combined with other functions to obtain desired results. There are various types of logical functions some of which are commonly used in data operations in Excel.
Types of Logical Functions in Excel
The various types of logical functions in Excel are IF, IFERROR, AND, OR, NOT, TRUE, and FALSE. In the following section, we will see how to use each of them through examples.
1. IF
It is a very simple logical function which checks if the condition to be validated are true or false. We will demonstrate the use of IF function using a very simple example. There’s a simple dataset containing data of students and their marks. Now, if the passing marks are 40 then we would like to declare the result for each student in terms of PASS and FAIL. This can be done using the IF function.
Syntax:
Example:
Step 1: When we enter the IF function, Excel automatically pops-up the list of arguments as shown in the following screenshot.
Step 2: The first argument is the logical test. In this case, the condition is that if marks are more than 40 then the result is PASS or else it is FAIL. We passed the condition as the argument in the IF function as shown below.
Step 3: The second and third arguments are ‘value_if_true’ and ‘value_if_false’ respectively. If marks are greater than 40 then the result should be PASS otherwise FAIL. This has been accomplished by passing the arguments as shown in the following screenshot.
4.9 (2,356 ratings)
View Course
Step 4: Copy the IF function across all the required cells, and we are returned the correct result as shown in the following screenshot.
2. IFERROR
This function is used when we want to return some meaningful result in place of an error. There are various types of errors in Excel, and IFERROR is used to deal with these errors. At many times, in Excel, we are returned error values as results. In such cases, IFERROR function becomes handy.
Syntax:
As we can see in the syntax, what basically IFERROR function does is that, if the value passed into it is not an error value then that value will be given as output by the function otherwise value that we have specified in ‘value if error’ argument.
Step 1: The IFERROR function takes into consideration two arguments as seen in the syntax. This is also shown in the screenshot as below.
Step 2: In this example, we have marks for certain students. For some of the students the result is not available and for them we have #N/A error in the Marks column. We want this error value to be replaced with some meaningful text which is possible through the use of IFERROR. The following screenshot shows this.
As we can see in the above screenshot, we passed marks as the first argument, and kept the string “Not Available” as the second argument. It means, if marks are valid then output marks or else “Not Available”. This is as shown by the following screenshot.
3. AND
This functions returns either TRUE or FALSE value by checking the conditions that are passed into it. If all the conditions passed into AND function are met then the TRUE value is returned and if even a single condition is not met then the FALSE value is returned.
Syntax:
Step 1: We have marks for three different subjects for certain students. Now, we want to check if the student has passed or failed. Here, if students secures marks less than 40 even in a single subject out of the three subjects, then he has failed in the examination. We can determine the result for each student using AND function. When we enter AND function, we find that arguments are highlighted. In this case, they are logical conditions as shown below.
Step 2: Using AND we will get result for each student by passing the conditions in the function as shown below.
When the function is implemented, we get the result as shown below. Note, here, TRUE means pass and FALSE means fail.
4. OR
It returns TRUE value even if a single condition out of the total conditions passed into it is met. It returns FALSE when all the arguments are FALSE.
Syntax:
Step 1: We have marks of three different subjects for certain students. Now, if a student has secured 40 marks in at least one of the three subjects then the result should be pass or else fail. When we enter OR function, the arguments are highlighted as below.
Step 2: The desired criteria for determining student’s result can be met using OR function as shown below.
When the above step is followed, we get the desired result as shown below. Observe it.
5. NOT
It reverses the logical value i.e. TRUE to FALSE and FALSE to TRUE. It is implemented usually in context-based situations.
Syntax: NOT(logical)
Step 1: We pass certain logical conditions into NOT, and check how the function works. Have a look at the following screenshot.
The actual result for each of them is shown. However, the logical values are reversed when we pass the logical conditions into NOT function as shown below.
6. TRUE and FALSE
They return corresponding logical values, TRUE returns TRUE while FALSE returns FALSE.
Syntax: TRUE(), FALSE()
We have the result of students. For PASS we will display TRUE and for FAIL, FALSE. The following screenshot shows the implementation.
The result that we obtain is as shown below.
Things to Remember
- The logical functions should be implemented in various context to understand there full capability.
- Difference in available features in logical functions for different versions of Excel should be checked.
- Each of the argument in a logical function should be studied in order to understand its role and significance.
Recommended Articles
This has been a guide to Logical Functions in Excel. Here we discuss How to use Logical Functions in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
