Logical Functions In Excel – MS Excel Functions Tutorial Part 6

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.

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

We will use the IF function to enter the status.

=IF(D7>=40,”Pass”,”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”.

When this formula is copied across the cells E8 to E12 this condition will be checked for the cells D8 to D12 and the corresponding values will be return based on the condition.

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 first use the AND function to indicate against the name of each student whether he/she has to awarded the trophy or not.

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.

When we copy this formula across the cells G6 to G10 the formula will automatically adjust itself and the relevant status will be printed.

Now let us bring our second condition into picture.

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 formula when copied across cells G6 to G10 will print the relevant status.

Function # 3 NOT

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.

=IF(NOT(E6<60),”Good”,”Poor”)

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 for this condition will be:

=IF(E7>90,”A”,IF(E7>=80,”B”,IF(E7>=70,”C”,IF(E7>=60,”D”,”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.

  1. Plz suggest me the formula for the following:

    “FIRST CLASS HONOURS will be awarded to a candidate who has achieved a mark of at least 80 per cent in more than half of the modules”

    Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">