Logical Formulas
Introduction: Why Learn Logical Formulas?
Imagine you're a teacher managing a student report card, or an HR person checking eligibility criteria. You often need to make decisions based on conditions like:
-
Did the student pass or fail?
-
Is the candidate qualified based on multiple criteria?
That’s where logical formulas help—by letting your Google Sheet think and make decisions for you.
Step 1: Create the Base Table
Let’s assume you’re evaluating marks of students in two subjects.
| Name | Subject 1 | Subject 2 | IF Result | AND Result | OR Result | NOT Result |
|---|---|---|---|---|---|---|
| Aayush | 78 | 82 | ||||
| Riya | 55 | 35 | ||||
| Aman | 32 | 39 | ||||
| Neha | 44 | 67 | ||||
| Krish | 29 | 51 |
💡 Passing marks assumed = 40
Step 2: IF Formula – Simple Decision Making
We want to check if a student passed in Subject 1.
📍 Formula in D2 (IF Result):
=IF(B2>=40, "Pass", "Fail")
This means:
-
If marks in Subject 1 are greater than or equal to 40, return
"Pass" -
Else return
"Fail"
Copy this formula down for all rows.
Step 3: AND Formula – All Conditions Must Be True
Let’s check if a student passed in both subjects.
📍 Formula in E2 (AND Result):
=AND(B2>=40, C2>=40)
-
Returns TRUE only if both Subject 1 and Subject 2 marks are 40 or more.
-
Otherwise, returns FALSE.
Step 4: OR Formula – Any One is Enough
Now check if the student passed in at least one subject.
📍 Formula in F2 (OR Result):
=OR(B2>=40, C2>=40)
-
Returns TRUE if either subject has 40 or more.
-
If both are below 40, it returns FALSE.
Step 5: NOT Formula – Complete Failure Check
Let’s find students who failed both subjects.
We’ll reverse the OR condition using NOT.
📍 Formula in G2 (NOT Result):
=NOT(OR(B2>=40, C2>=40))
-
If the student passed at least one subject →
ORreturns TRUE →NOTmakes it FALSE -
If failed both →
ORreturns FALSE →NOTmakes it TRUE
This tells you exactly who failed all.
Mini Project: Student Evaluation Sheet
Encourage students to create their own evaluation sheet like this:
-
Add columns for total marks, percentage, grade, and remarks
-
Use logical formulas to assign grades like A/B/C based on percentage
-
Add conditional formatting to highlight failures in red
