Sample Dataset
We’ll work with a dataset for students that tracks their performance across various subjects and other details.
The dataset has columns: ID, Name, Math, English, Science, History, Geography, Computer, Urdu, Islamiat, Art, Sports, Attendance, Total Marks, and Result.
Table of Contents |
Introduction
Excel’s logical functions let you test conditions (like “Is the
Math score ≥ 50?”) and return results based on those tests. Below, we’ll cover
10 logical functions, explaining what they do, their syntax, and how to use
them with our dataset. For each function, we’ll provide a formula example and
explain what it does for a student like Ahmed Ali or Bilal Iqbal. We’ll assume
you’re adding these formulas in new columns or single cell used for result.
1. IF Function
The IF function checks if a condition is true or false and returns
one value if true and another if false. For example, it can check if a student
passed a subject.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example: Let’s check if a student passed Math (marks ≥ 50). In result cell, enter the formula:=IF(C2>=50,"Pass","Fail")
This checks the Math score in C2. If it’s 50 or more, it shows
"Pass"; otherwise, it shows "Fail". For Ahmed Ali (C2 =
78), it returns "Pass". For Bilal Iqbal (C3 = 34), it returns
"Fail". After entering the formula, drag it down to all students.
2. AND Function
The AND function checks multiple conditions and returns TRUE only
if all conditions are true. It’s useful for checking if a student meets several
criteria.
Syntax: =AND(condition1, condition2, …)
Example: Check if a student passed both Math and Science (both ≥ 50). In result cell, use the formula: =AND(C2>=50,E2>=50)
This checks if Math (C2) and Science (E2) are both 50 or higher.
For Ahmed Ali (Math = 78, Science = 88), it returns TRUE. For Bilal Iqbal (Math
= 34, Science = 55), it returns FALSE because Math is below 50. Drag the
formula down to all cells
range.
3. OR Function
The OR function checks multiple conditions and returns TRUE if at
least one condition is true. It’s great for checking if a student meets at
least one criterion.
Syntax: =OR(condition1, condition2, …)
Example: Check if a student passed either Math or English (≥ 50 in at least one). In result cell, enter the formula: =OR(C2>=50,D2>=50)
For Ayesha Khan (Math = 56, English = 72), it returns TRUE because
both are ≥ 50. For Bilal Iqbal (Math = 34, English = 48), it returns FALSE
because neither is ≥ 50. Drag the formula down to all cells range.
4. NOT Function
The NOT function reverses a condition’s result: TRUE becomes FALSE,
and FALSE becomes TRUE. It’s useful for checking the opposite of a condition.
Syntax: =NOT(condition)
Example: Check if a student did not fail English (marks ≥ 50). In result cell, type the formula: =NOT(D2<50)
This checks if English (D2) is not less than 50. For Fatima Noor
(English = 86), it returns TRUE because 86 is not < 50. For Usman Zia
(English = 38), it returns FALSE because 38 is < 50. Drag the formula down
to cells range.
5. XOR Function
The XOR function (exclusive OR) returns TRUE if exactly one
condition is true, but not both or neither. It’s helpful for checking exclusive
conditions.
Syntax: =XOR(condition1, condition2, …)
Example: Check if a student passed either Math or Science, but not both. In result cell, enter the formula: =XOR(C2>=50,E2>=50)
For Ayesha Khan (Math = 56, Science = 61), it returns FALSE because
both are ≥ 50. For Bilal Iqbal (Math = 34, Science = 55), it returns TRUE
because only Science is ≥ 50. Drag the formula down to all cells range.
6. IFERROR Function
The IFERROR function catches errors in a formula and shows a custom
message instead of an error code like #DIV/0!.
Syntax: =IFERROR(value, value_if_error)
Example: Calculate the average of Math, English, and Science, but show "Error" if the formula fails. In cell U2, type the formula: =IFERROR((C2+D2+E2)/3,"Error")
For Hamza Tariq (Math = 62, English = 59, Science = 70), it returns
63.67 (the average). If the formula encounters an error (like non-numeric
data), it shows "Error".
7. IFNA Function
The IFNA function handles the #N/A error specifically, replacing it
with a custom value. It’s useful for lookup functions that might not find a
match.
Syntax: =IFNA(value, value_if_na)
Example: Look up a student’s Math marks by name and show "Not Found" if the name doesn’t exist. In result cell, enter the formula: =IFNA(XLOOKUP("Ali",B2:B8,C2:C8),"Not Found")
Since "Ali" isn’t an exact match in B2:B8, it returns
"Not Found". If you change "Ali" to "Ahmed Ali",
it returns 78 (his Math score). You can test this formula in result cell and drag
it down, but it will give the same result for all rows since it’s searching for
a fixed name.
8. TRUE and FALSE
Functions
The TRUE and FALSE functions simply return the logical values TRUE
or FALSE. They’re useful in formulas that need a constant logical value or for
testing.
Syntax: =TRUE(), =FALSE()
Example: To test these, enter =TRUE() in result cell and =FALSE() in 2nd result cell. They always return TRUE and FALSE, respectively. These can be used in other formulas, like combining with IF to set default values.
9. IFS Function
The IFS function checks multiple conditions in order and returns
the value for the first TRUE condition. It’s like multiple IF statements in one
formula.
Syntax: =IFS(condition1, value1, condition2, value2, …)
Example: Assign grades based on Total Marks (N2). In result cell, enter the formula:
=IFS(N2>=800,"A+",N2>=700,"A",N2>=600,"B",TRUE,"C")
For Fatima Noor (Total Marks = 892), it returns "A+". For
Hamza Tariq (Total Marks = 644), it returns "B". The TRUE at the end
acts as a default, giving "C" if no other condition is met. Drag the
formula down to all cells
range.
10. SWITCH Function
The SWITCH function checks a value against a list of options and
returns the matching result. It’s like a simplified IFS for specific values.
Syntax: =SWITCH(expression, value1, result1, value2, result2, …, default)
Example: Assign performance based on the Result column (O2). In result cell, enter: =SWITCH(O2,"Pass","Good","Fail","Needs Improvement","Unknown")
For Mariam Rauf (Result = Pass), it returns "Good". For
Usman Zia (Result = Fail), it returns "Needs Improvement". If O2 is
neither "Pass" nor "Fail" (like a blank cell), it returns
"Unknown". Drag the formula down to cells range.
Summary
Excel's logical functions are tools for making decisions in your
data. The IF function is the most basic, returning one value if a condition is
true ("Pass") and another if it's false ("Fail"). You can
check multiple conditions with the AND function (all must be true) and the OR
function (at least one must be true). The NOT function simply reverses a
condition's result. More advanced functions include XOR, which returns true for
exactly one true condition, and IFS, which checks multiple conditions sequentially.
Functions like IFERROR and IFNA are crucial for error handling, replacing
confusing error codes with custom messages. SWITCH simplifies checking a single
value against a list of options. By combining these functions, you can automate
data analysis, from grading student performance to creating dynamic reports,
making your spreadsheets much smarter and more efficient.
0 Comments