Advanced Logical Functions Practice Questions with Solutions

Introduction

Advanced logical formulas help Excel handle more complex decisions and business rules. In this chapter, you will practice IFS, XOR, LET, TRUE, FALSE, nested logical conditions, and combinations of logical functions with calculations. The questions are designed for learners who already understand basic IF, AND, OR, and NOT and now want to solve more realistic Excel problems. Advanced Logical Functions Practice questions with solutions to help yo understand the concepts. Advanced Logical Functions practice questions with solutions to help you understand the concepts.

Question 1: Grade Students Using IFS

Problem Statement

Assign a grade according to the student’s marks:

• 90 or above → A+
• 80 or above → A
• 70 or above → B
• 60 or above → C
• 50 or above → D
• Below 50 → F

Excel Data

Solution

In C2, enter:

=IFS(B2>=90,"A+",B2>=80,"A",B2>=70,"B",B2>=60,"C",B2>=50,"D",TRUE,"F")

Copy the formula down.

Expected Output

IFS is useful when there are several possible conditions and results.

Question 2: Identify Employees Eligible for Two Different Benefits Using XOR

Problem Statement

An employee receives a special benefit when exactly one of these conditions is TRUE:

• Sales are at least ₹5,00,000
• Attendance is at least 90%

If both conditions are TRUE or both are FALSE, the employee is not eligible.

Excel Data

Solution

In D2, enter:

=IF(XOR(B2>=500000,C2>=90%),"Eligible","Not Eligible")

Copy down.

Expected Output

XOR returns TRUE when an odd number of its logical conditions are TRUE. With two conditions, that means exactly one must be TRUE.

Question 3: Use LET to Simplify a Repeated Calculation

Problem Statement

Calculate an employee’s sales after a 10% commission deduction.

Instead of repeating the sales calculation multiple times, use LET to create a variable.

Excel Data

Solution

In D2, enter:

=LET(CommissionAmount,B2*C2,B2-CommissionAmount)

Copy down.

Expected Output

LET allows you to assign a name to a calculation and reuse it within the same formula.

Question 4: Create a Sales Performance Level Using IFS and AND

Problem Statement

Classify employees using both sales and customer ratings.

Rules:

• Sales ≥ ₹8,00,000 AND Rating ≥ 4.5 → Outstanding
• Sales ≥ ₹6,00,000 AND Rating ≥ 4 → Excellent
• Sales ≥ ₹4,00,000 AND Rating ≥ 3.5 → Good
• Otherwise → Needs Improvement

Excel Data

Solution

In D2, enter:

=IFS(AND(B2>=800000,C2>=4.5),"Outstanding",AND(B2>=600000,C2>=4),"Excellent",AND(B2>=400000,C2>=3.5),"Good",TRUE,"Needs Improvement")

Copy down.

Expected Output

Question 5: Use TRUE and FALSE for Eligibility Testing

Problem Statement

Determine whether each student satisfies both requirements:

• Marks ≥ 60
• Attendance ≥ 75%

Return the logical value TRUE or FALSE instead of text.

Excel Data

Solution

In D2, enter:

=AND(B2>=60,C2>=75%)

Copy down.

Expected Output

Here, TRUE and FALSE are logical values rather than text strings.

Question 6: Check Whether Exactly One Document Is Missing

Problem Statement

A customer’s account requires two documents:

• ID Proof
• Address Proof

The company wants to identify customers who have exactly one of these documents missing.

Use XOR.

Excel Data

Solution

In D2, enter:

=IF(XOR(B2="No",C2="No"),"One Missing","Check Complete")

Copy down.

Expected Output

When both documents are missing, XOR is FALSE because two conditions are TRUE.

Question 7: Use LET for a Discount Decision

Problem Statement

A store gives discounts based on the final order value after applying a membership discount.

Rules:

• Premium customers get 10% off.
• Regular customers get 5% off.
• If the final amount is ₹10,000 or more → High Value Order
• Otherwise → Regular Order

Excel Data

Solution

In D2, enter:

=LET(Discount,IF(C2="Premium",10%,5%),FinalAmount,B2*(1-Discount),IF(FinalAmount>=10000,"High Value Order","Regular Order"))

Copy down.

Expected Output

This example shows how LET can make a longer formula easier to organize.

Question 8: Advanced Employee Status Using Multiple Logical Conditions

Problem Statement

Classify an employee using these rules:

• If the employee is on probation → Probation
• Otherwise, if attendance is below 75% → Attendance Issue
• Otherwise, if performance rating is at least 4 → Good Standing
• Otherwise → Needs Review

Excel Data

Solution

In E2, enter:

=IFS(D2="Probation","Probation",B2<75%,"Attendance Issue",C2>=4,"Good Standing",TRUE,"Needs Review")

Copy down.

Expected Output

The order of conditions matters because IFS returns the result for the first TRUE condition.

Question 9: Use XOR with Two Approval Requirements

Problem Statement

A purchase requires two approvals:

• Manager Approval
• Finance Approval

The system should display:

• Both Approved when both are Yes
• One Approval Missing when exactly one is No
• Both Missing when both are No

Excel Data

Solution

In D2, enter:

=IF(AND(B2="Yes",C2="Yes"),"Both Approved",IF(XOR(B2="Yes",C2="Yes"),"One Approval Missing","Both Missing"))

Copy down.

Expected Output

This is a practical example of combining AND, XOR, and nested IF.

Question 10: Build an Advanced Sales Evaluation Formula

Problem Statement

Evaluate employees using the following rules:

1. If sales are zero → No Sales
2. If sales are at least ₹8,00,000 AND rating is at least 4.5 → Outstanding
3. If sales are at least ₹6,00,000 OR rating is at least 4 → Strong
4. If sales are at least ₹3,00,000 AND attendance is at least 80% → Satisfactory
5. Otherwise → Needs Improvement

Excel Data

Solution

In E2, enter:

=IFS(B2=0,"No Sales",AND(B2>=800000,C2>=4.5),"Outstanding",OR(B2>=600000,C2>=4),"Strong",AND(B2>=300000,D2>=80%),"Satisfactory",TRUE,"Needs Improvement")

Copy down.

Expected Output

Notice that Arjun is classified as Strong because the rating condition is satisfied, even though sales are below ₹3,00,000.

Key Takeaways

• IFS is useful when you have several conditions and different results.
• XOR is useful when you need to determine whether an odd number of conditions are TRUE.
• With two conditions, XOR is TRUE when exactly one condition is TRUE.
• LET allows you to create named variables inside a formula.
• TRUE and FALSE are logical values that can be used directly in formulas.
• The order of conditions in IFS is important.
• More complex formulas can combine IFS, AND, OR, NOT, and XOR.
• LET can make long formulas easier to read and maintain.
• Advanced logical formulas are useful for employee evaluation, approval systems, grading, discounts, eligibility and business rules.
• Test formulas with boundary values and unusual cases to make sure the logic works correctly.

FAQs

1. What is the IFS function in Excel?

IFS checks multiple conditions in order and returns the result associated with the first TRUE condition.

=IFS(B2>=90,"A+",B2>=80,"A",B2>=70,"B",TRUE,"F")

2. What is the difference between IF and IFS?

IF is commonly used for one condition or a smaller number of logical branches, while IFS is designed to handle multiple condition-result pairs more directly.

3. What does XOR mean in Excel?

XOR returns TRUE when an odd number of its logical arguments are TRUE. With two conditions, it means exactly one condition must be TRUE.

=XOR(A2="Yes",B2="Yes")

4. What is LET used for in Excel?

LET allows you to assign names to values or calculations inside a formula and then reuse them.

=LET(Total,B2*C2,Total*10%)

5. Can I use AND inside IFS?

Yes. This is useful when an IFS condition requires multiple requirements to be satisfied.

=IFS(AND(B2>=800000,C2>=4.5),"Outstanding",TRUE,"Other")

6. Can OR be used inside IFS?

Yes. OR can be used when any one of several conditions should trigger a particular result.

=IFS(OR(B2>=600000,C2>=4),"Strong",TRUE,"Other")

7. Why is TRUE used at the end of an IFS formula?

TRUE can be used as the final condition to provide a default result when none of the previous conditions is TRUE.

8. Does the order of IFS conditions matter?

Yes. IFS returns the result for the first condition that evaluates to TRUE. Therefore, more specific or higher-priority conditions should generally be placed before broader conditions.

9. Can XOR be used with text?

Yes. You can compare text values and then pass the logical results to XOR.

=XOR(A2="Yes",B2="Yes")

10. When should I use LET?

LET is particularly useful when a formula contains the same calculation multiple times or when naming intermediate calculations makes a complex formula easier to understand.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar