IF & Nested Logical Formulas Test
Test your practical skills with Excel logical functions. Practice single-condition IF, multi-tier nested IF statements, IFS linear evaluation, and boolean compounding with AND, OR, and XOR.
Three Required Arguments of the IF Function
What are the three positional arguments defined in Microsoft Excel's standard IF function syntax?
Evaluate Delivery Threshold with Basic IF
In target cell C2, evaluate the order delivery fee: If Order Total (B2) is greater than or equal to 100, return 0, otherwise return 15.
| # | A | B | C |
|---|---|---|---|
| 1 | Order ID | Order Total ($) | Shipping Fee ($) |
| 2 | ORD-501 | 135 | |
| 3 | ORD-502 | 74 | |
| 4 | ORD-503 | 100 |
Behavior of the AND() Function
Which of the following statements correctly describe the behavior of Excel's AND() function? (Select all that apply)
Validate Quality Check with IF and AND
In target cell D2, verify inspection status: If Weight (B2) is >= 50 AND Purity (C2) is >= 0.95, return "Pass", otherwise return "Fail".
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Batch Code | Weight (g) | Purity | Quality Status |
| 2 | BAT-801 | 52 | 0.98 | |
| 3 | BAT-802 | 48 | 0.99 | |
| 4 | BAT-803 | 55 | 0.91 |
Default Return Value for Omitted value_if_false
If you write the formula =IF(A1>50, "High") and cell A1 contains the number 25, what will Excel display in the formula cell?
Check Urgent Delivery with IF and OR
In target cell D2, flag priority shipment: If Shipping Method (B2) is "Express" OR Order Weight (C2) > 20, return "Priority", otherwise return "Standard".
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Package ID | Shipping Method | Weight (kg) | Shipment Class |
| 2 | PKG-101 | Express | 8 | |
| 3 | PKG-102 | Ground | 25 | |
| 4 | PKG-103 | Ground | 12 |
Key Advantages of IFS Function Over Nested IF
Which of the following are distinct advantages of using IFS() instead of deeply nested IF() statements? (Select all that apply)
Assign Customer Tier with Nested IF
In target cell C2, assign customer tier based on Annual Spend (B2): If Spend > 50000 return "Platinum", otherwise if Spend > 20000 return "Gold", else return "Silver".
| # | A | B | C |
|---|---|---|---|
| 1 | Customer | Annual Spend ($) | Tier |
| 2 | Apex Holdings | 62,000 | |
| 3 | Beacon Logistics | 28,000 | |
| 4 | Crestview Media | 14,000 |
Numeric Return Values vs Quoted Text Numbers
What problem occurs when writing =IF(A1>=100, "50", "0") instead of =IF(A1>=100, 50, 0)?
Multi-Tier Performance Rating with IFS
In target cell C2, assign rating using IFS: If Score (B2) >= 90 return "Excellent", if B2 >= 75 return "Good", and for all other scores (TRUE) return "Needs Improvement".
| # | A | B | C |
|---|---|---|---|
| 1 | Employee | Review Score | Performance Rating |
| 2 | Marcus Vance | 82 | |
| 3 | Elena Rostova | 94 | |
| 4 | Devon Miller | 68 |
Credit Risk Assessment & Exposure Flagging
Scenario Background: The Risk Management committee requires an automated risk tier system. Accounts with Overdue Days (C2) > 60 AND Outstanding Balance (B2) > 10000 must be marked "High Risk". All other accounts remain "Standard".
In target cell D2, evaluate account risk: return "High Risk" if Overdue Days (C2) > 60 AND Outstanding Balance (B2) > 10000, otherwise return "Standard".
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Client Account | Outstanding ($) | Overdue Days | Risk Tier |
| 2 | ACT-301 | 18,500 | 75 | |
| 3 | ACT-302 | 8,200 | 90 | |
| 4 | ACT-303 | 24,000 | 30 |
Function and Output of NOT()
What is the primary function of the NOT() operator in Excel formulas?
Flag Excluded Department with IF and NOT
In target cell C2, assign audit status: If Department (B2) is NOT equal to "Executive", return "Audit", otherwise return "Exempt".
| # | A | B | C |
|---|---|---|---|
| 1 | Staff Member | Department | Audit Status |
| 2 | Sonia Patel | Operations | |
| 3 | David Sterling | Executive | |
| 4 | Liam O'Connor | Marketing |
Logic Gate: Understanding XOR() (Exclusive OR)
Which statements accurately reflect the behavior of the XOR() (Exclusive OR) function in modern Excel? (Select all that apply)
Enforce Single Promo Voucher Rule with XOR
In target cell D2, validate promo code eligibility: If customer has Promo A (B2="Yes") XOR Promo B (C2="Yes"), return "Valid", otherwise return "Invalid".
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Customer ID | Promo A | Promo B | Eligibility |
| 2 | CUST-901 | Yes | No | |
| 3 | CUST-902 | Yes | Yes | |
| 4 | CUST-903 | No | No |
Executive Discretionary Bonus Calculation
Scenario Background: The Compensation Committee allocates an annual performance bonus. An employee receives a $5,000 bonus if their Closed Sales (B2) exceed $150,000 OR if their Client Retention (C2) is at least 95%. Otherwise, bonus is $1,000.
In target cell D2, calculate the bonus payout: return 5000 if Sales (B2) > 150000 OR Retention (C2) >= 0.95, otherwise return 1000.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Account Director | Sales ($) | Retention | Bonus ($) |
| 2 | Clara Higgins | 162,000 | 0.91 | |
| 3 | Julian Brooks | 138,000 | 0.96 | |
| 4 | Tara Thorne | 115,000 | 0.88 |
Handling Blank Cells in Numerical Comparisons
In Excel, if cell A1 is completely blank (empty) and you evaluate =IF(A1=0, "Zero", "Non-Zero"), what does Excel return?
Identify Missing Data with Empty String Comparison
In target cell C2, verify record completeness: If Phone (B2) is empty (=""), return "Missing", otherwise return "Complete".
| # | A | B | C |
|---|---|---|---|
| 1 | Candidate | Phone | Record Status |
| 2 | Rachel Scott | ||
| 3 | Daniel Kim | 555-0192 | |
| 4 | Sarah Jenkins | 555-0144 |
Case Sensitivity in Logical Tests
Which of the following formulas correctly evaluate text comparison without being case-sensitive? (Select all that apply)
Inventory Restock Reorder Alert System
Scenario Background: The Logistics warehouse must flag stock replenishments. If Current Stock (B2) is less than or equal to Reorder Level (C2), return "Reorder Needed". Otherwise, return "Sufficient".
In target cell D2, evaluate stock level: return "Reorder Needed" if Current Stock (B2) <= Reorder Level (C2), otherwise return "Sufficient".
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | SKU | Current Stock | Reorder Level | Inventory Alert |
| 2 | SKU-401 | 45 | 50 | |
| 3 | SKU-402 | 120 | 60 | |
| 4 | SKU-403 | 15 | 20 |
Handling #N/A and Calculation Errors with IFNA / IFERROR
What is the primary architectural difference between IFERROR() and IFNA()?
Multi-Condition Project Priority with Nested IF
In target cell C2, assign project priority based on Budget (B2): If Budget > 100000 return "High", if Budget > 40000 return "Medium", else return "Low".
| # | A | B | C |
|---|---|---|---|
| 1 | Project Name | Budget ($) | Priority |
| 2 | Cloud Migration | 125,000 | |
| 3 | Portal Redesign | 65,000 | |
| 4 | Security Audit | 25,000 |
Maximum Nesting Levels in Modern Excel
What is the maximum number of nested IF functions supported in modern versions of Microsoft Excel (Excel 2007 and newer)?
Common Pitfalls in Logical IF Formulas
Which of the following mistakes are frequent causes of formula bugs when using IF statements? (Select all that apply)
Shipping Freight Surcharge Calculation
Scenario Background: The logistics carrier applies an overweight surcharge. If Package Weight (B2) is greater than 50 kg, the freight cost is Base Cost (C2) plus $75. Otherwise, the freight cost is equal to Base Cost (C2).
In target cell D2, calculate the total freight cost: if Weight (B2) > 50, return Base Cost (C2) + 75, otherwise return Base Cost (C2).
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Tracking ID | Weight (kg) | Base Cost ($) | Total Freight ($) |
| 2 | TRK-201 | 62 | 140 | |
| 3 | TRK-202 | 38 | 110 | |
| 4 | TRK-203 | 50 | 95 |
Default Behavior of IFS When No Conditions Match
What error does the IFS() function return if all tested conditions evaluate to FALSE and no default condition was provided?
Valid Methods to Create a Fallback in IFS()
How can you specify a reliable default fallback value at the end of an IFS() formula? (Select all that apply)
Commission Payout with IF and AND
In target cell D2, calculate sales commission: If Revenue (B2) >= 50000 AND Margin (C2) >= 0.20, return Revenue * 0.10, otherwise return Revenue * 0.03.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Sales Rep | Revenue ($) | Margin | Commission ($) |
| 2 | Harrison Cole | 60,000 | 0.25 | |
| 3 | Miranda Bailey | 75,000 | 0.15 | |
| 4 | Trevor Phillips | 35,000 | 0.22 |
SWITCH Function vs Nested IF
In what scenario is the SWITCH() function generally superior to a nested IF structure?
Candidate Screening Qualification Matrix
Scenario Background: The Talent Acquisition department needs to filter applicants. A candidate is marked "Qualified" if their Assessment Score (B2) is at least 80 AND they have at least 3 Years of Experience (C2). Otherwise, they are marked "Review".
In target cell D2, assign candidate status: return "Qualified" if Score (B2) >= 80 AND Experience (C2) >= 3, otherwise return "Review".
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Candidate | Score | Years Exp | Screening Status |
| 2 | Amira Kassam | 85 | 4 | |
| 3 | Brian Zhang | 92 | 2 | |
| 4 | Chloe Dupont | 74 | 5 |
M. Ichsanul Fadhil
Founder, ExcelClashPre-employment screening exams designed with instant browser-based formula grading.
Related Excel Skill Tests
Basic Excel Skills Test: Free Online Assessment
Intermediate Excel Skills Test: Assess Workplace Readiness
VLOOKUP & XLOOKUP Skills Test: Workplace Assessment
Excel Test for Administrative Assistants
Assessment Discussion & Questions
Ask questions, discuss problem solving techniques, or share feedback on this test.
Loading comments...