We learnt Mathematical Functions, Text Functions, Cleanup and Case Functions in Excel and how to use these functions. In this post, we will learn about Logical functions in excel.
- Introduction To Excel Formulas & Functions
- Inserting Formula, Function & Named Range In Excel
- Mathematical Functions In Excel
- Text Functions In Excel
- Cleanup & Case Functions In Excel
- Logical Functions In Excel
- Lookup Functions In Excel
- Date & Time Functions In Excel
- Max & Min Functions In Excel
Logical functions are used for performing tests based on specific conditions.
Function # 1 IF
This is one of the most important logical functions. It tests a set of conditions and returns results based on those conditions. Let us consider a simple example in order to understand this function better. Assume that we have a student database which contains two fields: Student Name and Marks Scored. Now our aim here is to find indicate a Pass/Fail status against the name of each student based on certain conditions. The conditions are as follows:
- If the student has scored above 40 indicate Pass
- If the student has scored below 40 indicate Fail
This formula tells excel to check if the value in cell D7>=40. If the condition is true then it should print “Pass” otherwise “Fail”.
Function # 2 AND and OR
These functions are used for creating long conditional tests based on multiple criteria. Let us consider a small student database which contains three fields “Student Name”, “Percent Scored” and “Extracurricular rating”. Let us suppose that we want to award trophies to students based on these conditions:
- If the student has scored above 90 and has an extracurricular rating>=8.
- If the student has scored above 97 or has an extracurricular rating>=7
Let us write a formula to test the first condition initially and decide whether the trophy should be awarded or not:
Condition 1: If the student has scored above 90 and has an extracurricular rating>=8 award the trophy.
=IF(AND(E5>90,F5>=8),”Award trophy”,”Not eligible”)
This formula tells excel to test if the value is cell D8 (Marks scored) and the value in cell E18 (Extracurricular Rating) are greater than 90 and 8 respectively. If this condition is true than it should print “Award trophy” else “Not eligible”.
Note: Using the AND function here we are actually testing two criterias.
Condition 2: If the student has scored above 97 or has an extracurricular rating>=7
Let us suppose that we have to check the trophy status taking both condition 1 and condition 2 into account. The student has to be awarded the trophy if either of the conditions are true.
Our condition now becomes – If the student has scored above 90 and has an extracurricular rating>=8 OR If the student has scored above 97 or has an extracurricular rating>=7 award him/her the trophy.
Here we will use the OR with AND function.
=IF(OR(AND(E5>90,F5>=8),AND(E5>97,F5>=7)),”Award trophy”,”Not eligible”)
This formula tells excel to check whether condition 1 OR condition 2 are true or not. If either of the conditions are true then it should print “Award trophy” else “Not eligible”.
This function negates a condition. For e.g. NOT(FALSE) is TRUE.
This function is mostly used with the IF function.Let us suppose that we have small student database which contains the names of students along with their percentages. For any student who has scored less than 60 we want to report the result as poor. Let us do this using IF and NOT.
This condition tells excel to check print “Good” if the value in the cell E6 is not less than 60.
Function # 4 NESTED IF
This function is used when there is a hierarchy of conditions which need to be tested with the outputs printed appropriately. Let us consider a simple example to understand Nested IF better.
Let us suppose that we have a small student database which contains three fields: Names of students, Marks and their Branch. Now assume that we have to print their grades based on these conditions:
- If the marks>90 print A
- If the marks is between 80 and 90 print B
- If the marks is between 70 and 79 print C
- If the marks is between 60 and 69 print D
- -If the marks is less than 60 print E
The formula tells excel to check the cell E7 for four nested conditions. Each IF is nested in the false statement of another IF i.e. we have nested each IF in the false criteria of another IF. Please note that it is important to close the parenthesis properly in order to get the correct results.
In next post, we will learn Lookup Functions.