MS Excel Logical Functions

Logical functions in Excel are powerful tools that help you make decisions based on conditions. Whether you're comparing values or testing multiple conditions, these functions save you time and effort. Let's explore the most common logical functions in Excel with easy-to-understand examples.

 

Logical Functions for Class Data Example

Student	Math	English	Science
Mohan	85	78	92
Sheela	45	63	55
Prem	76	88	81
Neha	95	82	90
Dinesh	58	49	65
Sneha	67	70	62
Mohini	88	92	79
Seema	34	59	46
Suresh	70	85	78
Priyanka	93	87	85

 

1. IF Function

We’ll use the IF function to assign grades based on total marks (the sum of Math, English, and Science).

Formula:
=IF(SUM(B2:D2)>=250, "A", IF(SUM(B2:D2)>=200, "B", IF(SUM(B2:D2)>=150, "C", "D")))

Explanation:

• If total marks are greater than or equal to 250, the student gets an "A".
• If between 200 and 249, they get a "B".
• If between 150 and 199, they get a "C".
• Otherwise, they get a "D".

For Mohan (who scored 85, 78, and 92), the total is 255, so the grade will be "A".

 

2. AND Function

Let’s check if students passed in all subjects (passing mark is 50).

Formula:
=AND(B2>=50, C2>=50, D2>=50)

Explanation:

• If Math, English, and Science marks are all 50 or above, the result is TRUE (student passed all subjects).
• Otherwise, it returns FALSE.

For Mohan, the result will be TRUE because he passed all subjects.

 

3. OR Function

The OR function will check if a student passed at least one subject.

Formula:
=OR(B2>=50, C2>=50, D2>=50)

Explanation:

• If any one of the three subjects has a score of 50 or more, the result is TRUE.
• Otherwise, it’s FALSE.

For Sheela, who has scores of 45, 63, and 55, the result will be TRUE because she passed in English and Science.

 

4. NOT Function

We’ll use the NOT function to flip the result of a condition. Let’s check if a student didn’t pass all subjects.

Formula:
=NOT(AND(B2>=50, C2>=50, D2>=50))

Explanation:

• If the student passed all subjects, the AND function will return TRUE.
• The NOT function will flip it to FALSE, meaning the student did not fail any subject.

For Dinesh, who failed in English (score 49), the NOT function will return TRUE, meaning he didn’t pass all subjects.

 

5. XOR Function

The XOR function (Exclusive OR) returns TRUE if one of the conditions is true, but not both. Let’s use it to check if a student passed exactly one subject out of Math and Science.

Formula:
=XOR(B2>=50, D2>=50)

Explanation:

• The function checks if the student passed exactly one subject (either Math or Science but not both).

For Sheela, who scored 45 in Math and 55 in Science, the result will be TRUE because she passed only one subject (Science).

 

6. IFERROR Function

The IFERROR function is used to handle potential errors in formulas. Let’s handle any error that might occur during a division operation (e.g., dividing total marks by 0).

Formula:
=IFERROR(SUM(B2:D2)/0, "Error")

Explanation:

• We are forcing an error by dividing the total marks by 0. Instead of showing an error message, IFERROR will return "Error".

For any student, this formula will return "Error" since division by 0 is not allowed.

 

7. IFNA Function

We’ll use IFNA to handle situations where a formula results in a #N/A error. This is useful when using functions like VLOOKUP or MATCH.

Formula:
=IFNA(VLOOKUP(A2, G2:H11, 2, FALSE), "Not Found")

Explanation:

• This checks if the student's name (in column A) is found in a list (in range G2

).

• If not found, instead of returning a #N/A error, the formula will return "Not Found".

If the student’s name isn’t found in the list, it will return "Not Found".

 

8. SWITCH Function

We can use the SWITCH function to assign grades based on total marks, just like we did with the IF function, but in a simpler and cleaner way.

Formula:
=SWITCH(TRUE, SUM(B2:D2)>=250, "A", SUM(B2:D2)>=200, "B", SUM(B2:D2)>=150, "C", "D")

Explanation:

• The function checks multiple conditions and returns the corresponding grade.
• If the total is 250 or above, the grade is "A". If between 200 and 249, it’s "B", and so on.

For Prem, with total marks of 245, the result will be "B".

 

Summary of Logical Function Applications

Student	Math	English	Science	Total Marks	Passed All (AND)	Passed Any (OR)	Didn’t Pass All (NOT)	Grade (SWITCH)
Mohan	85	78	92	255	TRUE	TRUE	FALSE	A
Sheela	45	63	55	163	FALSE	TRUE	TRUE	C
Prem	76	88	81	245	TRUE	TRUE	FALSE	B
Neha	95	82	90	267	TRUE	TRUE	FALSE	A
Dinesh	58	49	65	172	FALSE	TRUE	TRUE	C
Sneha	67	70	62	199	TRUE	TRUE	FALSE	C
Mohini	88	92	79	259	TRUE	TRUE	FALSE	A
Seema	34	59	46	139	FALSE	TRUE	TRUE	D
Suresh	70	85	78	233	TRUE	TRUE	FALSE	B
Priyanka	93	87	85	265	TRUE	TRUE	FALSE	A

 

Final Remarks

Logical functions in Excel such as IF, AND, OR, NOT, XOR, IFERROR, IFNA, and SWITCH allow you to create dynamic, decision-making spreadsheets. Using the data of a class of 10 students, we have seen how to implement these functions to calculate grades, check passing conditions, and handle errors.