0
0

MS Excel — Logical & Conditional Functions

Introduction

Logical and conditional functions in MS Excel help users make decisions based on conditions. These functions are widely used in marksheets, attendance, salary calculations, and eligibility checks.

Exams test this pattern to see whether candidates can understand decision-based spreadsheet logic, not just simple calculations.

Pattern: MS Excel — Logical & Conditional Functions

Pattern

The key idea is to apply conditions using logical tests and return different results based on whether the condition is TRUE or FALSE.

Step-by-Step Example

Question

Which MS Excel function checks a condition and returns one value if the condition is TRUE and another value if the condition is FALSE?

  • A. AND()
  • B. OR()
  • C. IF()
  • D. COUNTIF()

Solution

  1. Step 1: Understand the requirement

    The question asks for a function that tests a condition and returns two different outputs depending on the result.

  2. Step 2: Recall logical and conditional functions

    • AND() → Returns TRUE only if all conditions are TRUE
    • OR() → Returns TRUE if any one condition is TRUE
    • IF() → Returns one value for TRUE and another for FALSE
    • COUNTIF() → Counts cells based on a condition

  3. Step 3: Match the requirement with the correct function

    Only IF() can return two different results based on a condition.

  4. Final Answer:

    IF() → Option C

  5. Quick Check:

    IF(logical_test, value_if_true, value_if_false) confirms the behaviour ✅

Quick Variations

• Checking multiple conditions together → AND()

• Checking at least one condition → OR()

• Counting values that meet a condition → COUNTIF()

• Adding values that meet a condition → SUMIF()

Trick to Always Use

  • Step 1 → Identify whether the question asks for a TRUE/FALSE test or a value-based result.
  • Step 2 → Match keywords:
    • If condition then result → IF()
    • All conditions must be true → AND()
    • Any one condition true → OR()
    • Count with condition → COUNTIF()

Summary

Summary

  • Logical functions help Excel make decisions automatically.
  • IF() is the most important and frequently tested conditional function.
  • AND() and OR() are used to combine multiple conditions.
  • COUNTIF() and SUMIF() apply conditions to counting and addition.

Example to remember:
IF marks ≥ 40 → Pass | Else → Fail

Practice

(1/5)
1. Which MS Excel function returns TRUE only when all given conditions are satisfied?
easy
A. AND()
B. OR()
C. IF()
D. COUNTIF()

Solution

  1. Step 1: Identify the logic requirement

    The function must check multiple conditions together.

  2. Step 2: Recall logical functions

    AND() returns TRUE only if all conditions are TRUE.

  3. Final Answer:

    AND() → Option A.

  4. Quick Check:

    AND(TRUE, TRUE) = TRUE; any FALSE makes it FALSE ✅
Hint: All conditions true = AND().
Common Mistakes: Using OR() when all conditions are required.
2. Which MS Excel function returns TRUE if at least one of the given conditions is satisfied?
easy
A. AND()
B. OR()
C. IF()
D. SUMIF()

Solution

  1. Step 1: Understand the condition

    The function should succeed even if one condition is TRUE.

  2. Step 2: Match with logical functions

    OR() returns TRUE when any one condition is TRUE.

  3. Final Answer:

    OR() → Option B.

  4. Quick Check:

    OR(FALSE, TRUE) = TRUE ✅
Hint: Any one true = OR().
Common Mistakes: Confusing OR() with AND().
3. Which MS Excel function is used to count the number of cells that satisfy a specific condition?
easy
A. COUNT()
B. SUM()
C. COUNTIF()
D. IF()

Solution

  1. Step 1: Identify the task

    The task is counting cells based on a condition.

  2. Step 2: Recall conditional counting functions

    COUNTIF() counts cells that meet a specified condition.

  3. Final Answer:

    COUNTIF() → Option C.

  4. Quick Check:

    COUNTIF(range, condition) applies condition-based counting ✅
Hint: Count with condition = COUNTIF().
Common Mistakes: Using COUNT() which ignores conditions.
4. Which MS Excel function adds values in a range only if they meet a specified condition?
medium
A. SUMIF()
B. SUM()
C. COUNTIF()
D. AVERAGE()

Solution

  1. Step 1: Understand the requirement

    The task involves addition with a condition.

  2. Step 2: Match with conditional sum function

    SUMIF() adds values that satisfy a condition.

  3. Final Answer:

    SUMIF() → Option A.

  4. Quick Check:

    SUMIF(range, criteria, sum_range) confirms conditional addition ✅
Hint: Sum with condition = SUMIF().
Common Mistakes: Using SUM() which adds all values.
5. Which MS Excel function can be used to display "Pass" if marks are 40 or above, otherwise "Fail"?
medium
A. AND()
B. OR()
C. COUNTIF()
D. IF()

Solution

  1. Step 1: Identify output-based decision

    The output changes based on a condition.

  2. Step 2: Match with conditional output function

    IF() returns different values for TRUE and FALSE.

  3. Final Answer:

    IF() → Option D.

  4. Quick Check:

    IF(marks≥40,"Pass","Fail") matches the requirement ✅
Hint: Condition with two outputs = IF().
Common Mistakes: Using AND/OR which only return TRUE/FALSE.

Mock Test

Ready for a challenge?

Take a 10-minute AI-powered test with 10 questions (Easy-Medium-Hard mix) and get instant SWOT analysis of your performance!

10 Questions
5 Minutes