Practical 9 – Mastering Logical Formulas

1. Objective

The primary objective of this practical is to enable students to implement conditional logic in Excel using the IF, OR, and AND functions. Students will learn to:

·         Construct basic IF statements to perform actions based on a single condition.

·         Utilize the AND function to check if all specified conditions are true.

·         Utilize the OR function to check if at least one of the specified conditions is true.

·         Combine IF with AND and OR to create complex conditional formulas.

·         Apply nested IF statements for multiple conditions.

·         Enhance data analysis and decision-making capabilities in spreadsheets.

2. Introduction

Logical functions are the backbone of decision-making in Excel. They allow your spreadsheet to evaluate conditions and return different results or perform different actions based on whether those conditions are met. The IF function is the most common, letting you specify what happens when a condition is true or false. The AND and OR functions are often used inside IF to test multiple conditions simultaneously, making your formulas incredibly powerful for complex scenarios like evaluating eligibility criteria, applying specific rules, or generating automated responses based on data.

3. Pre-requisites

·         Successful completion of Practical 3.

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic formula entry and cell references.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

Ensure you have your completed marksheet from Practical 3 open. We will use this table as our dataset for applying logical formulas.

Step 5.1: Opening the Marksheet and Reviewing IF Function Basics

1.     Open the Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Review Result Column (Simple IF):

o    Click on cell J4.

o    Observe the formula: =IF(H4>=0.35,"Pass","Fail")

o    Explanation: This is a simple IF statement.

§  H4>=0.35 is the logical_test (Is Alice’s percentage 35% or more?).

§  "Pass" is the value_if_true (If yes, display "Pass").

§  "Fail" is the value_if_false (If no, display "Fail").

3.     Review Grade Column (Nested IF):

o    Click on cell I4.

o    Observe the formula: =IF(H4>=0.8,"A",IF(H4>=0.6,"B",IF(H4>0.45,"C","")))

o    Explanation: This is a nested IF statement. When the first IF is false, it proceeds to the second IF as its value_if_false, and so on. This allows for multiple outcomes based on a series of conditions.

Step 5.2: Using the AND Function

The AND function returns TRUE if all its arguments evaluate to TRUE, and FALSE if one or more arguments evaluate to FALSE.

1.     Add a New Column: In cell K3, type Good Performance.

2.     Define Condition: We want to identify students who scored ‘Grade A’ AND whose Total Marks are greater than 400.

3.     Enter AND Formula: In cell K4, type =AND(I4="A", G4>400) and press Enter.

o    Explanation:

§  I4="A": Checks if the grade in cell I4 is "A".

§  G4>400: Checks if the total marks in cell G4 are greater than 400.

§  Both conditions must be true for the AND function to return TRUE.

4.     Copy Down: Use the fill handle to drag the formula from K4 down to K8.

o    Observation: You will see TRUE or FALSE for each student.

Step 5.3: Using the OR Function

The OR function returns TRUE if any of its arguments evaluate to TRUE, and FALSE only if all arguments evaluate to FALSE.

1.     Add a New Column: In cell L3, type High Potential.

2.     Define Condition: We want to identify students who scored over 90 in Subject 1 OR over 90 in Subject 2.

3.     Enter OR Formula: In cell L4, type =OR(B4>90, C4>90) and press Enter.

o    Explanation:

§  B4>90: Checks if Subject 1 marks are greater than 90.

§  C4>90: Checks if Subject 2 marks are greater than 90.

§  If either (or both) conditions are true, the OR function returns TRUE.

4.     Copy Down: Use the fill handle to drag the formula from L4 down to L8.

o    Observation: You will see TRUE or FALSE for each student.

Step 5.4: Combining IF with AND and OR

This is where logical functions become truly powerful. You can embed AND or OR within an IF statement’s logical_test to handle multiple conditions and return custom results.

1.     Combine IF and AND: Let’s create a "Scholarship Eligible" column. A student is eligible if they have a ‘Grade A’ AND Percentage is greater than 85%.

o    Add a New Column: In cell M3, type Scholarship Eligible.

o    Enter Formula: In cell M4, type =IF(AND(I4="A", H4>0.85),"Yes","No") and press Enter.

§  Explanation: The AND(I4="A", H4>0.85) acts as the logical_test for the IF function. If both conditions are met, IF returns "Yes"; otherwise, "No".

o    Copy Down: Use the fill handle to drag the formula from M4 down to M8.

2.     Combine IF and OR: Let’s identify "At Risk" students who either Failed OR scored less than 40 in Subject 5.

o    Add a New Column: In cell N3, type At Risk.

o    Enter Formula: In cell N4, type =IF(OR(J4="Fail", F4<40),"Yes","No") and press Enter.

§  Explanation: The OR(J4="Fail", F4<40) acts as the logical_test for the IF function. If either condition is met, IF returns "Yes"; otherwise, "No".

o    Copy Down: Use the fill handle to drag the formula from N4 down to N8.

Step 5.5: Adding More Complex Nested IF with AND

Let’s refine the "Grade" assignment to include a "Distinction" for Grade A students with very high percentages.

1.     Modify Grade Formula: Go back to cell I4. We will modify the existing IF formula.

o    The original was: =IF(H4>=0.8,"A",IF(H4>=0.6,"B",IF(H4>0.45,"C","")))

o    Let’s add a condition for Distinction: If percentage >= 0.9 then "Distinction". This needs to be checked before just assigning "A".

o    Modified Formula: =IF(H4>=0.9,"Distinction",IF(H4>=0.8,"A",IF(H4>=0.6,"B",IF(H4>0.45,"C",""))))

o    Explanation: Now, the first condition checks for 90% or above. If true, "Distinction" is assigned. Only if that is false does it then check for 80% or above for "A", and so on.

2.     Copy Down: Drag the modified formula from I4 down to I8.

o    Observation: Observe if any students now receive "Distinction" based on the new criteria.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet:

1.     "Eligibility for Retest" Status:

o    Add a new column Retest Eligibility.

o    A student is eligible for a retest if they Failed (from column J) AND their Total Marks are greater than 150.

o    Use an IF(AND(...)) formula to display "Eligible" or "Not Eligible".

2.     "Dean’s List" Status:

o    Add a new column Dean's List.

o    A student makes the Dean’s List if they received a Grade A OR their Percentage is greater than 88%.

o    Use an IF(OR(...)) formula to display "Yes" or "No".

3.     "Subject Improvement Needed" Status:

o    Add a new column Improvement Needed.

o    A student needs improvement if they scored less than 50 in Subject 3 OR less than 50 in Subject 4.

o    Use an IF(OR(...)) formula to display "Review" or "Good".

4.     "Overall Performance Status" (Complex Nested IF):

o    Add a new column Overall Status.

o    Implement the following logic using nested IF (you might need AND or OR within these):

§  If Percentage >= 90% AND Result is "Pass" -> "Excellent"

§  Else if Percentage >= 70% AND Result is "Pass" -> "Very Good"

§  Else if Result is "Pass" -> "Satisfactory"

§  Else -> "Needs Attention"

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with the Marksheet sheet, including all new columns and formulas from the procedure and exercises.

·         Ensure all logical functions (IF, OR, AND) are correctly implemented and yield accurate results according to the specified conditions.

·         Be prepared to explain the logic of each formula, especially how AND and OR functions evaluate multiple conditions.