ADUVIE

ADUVIE INTERNATIONAL SCHOOL | HIGH SCHOOL ICT

Dual-Board Mastery: Cambridge IGCSE 0417 & WASSCE Digital Technology

0% Complete

WEEK 07 CURRICULAR SUITE

Advanced Spreadsheets: Nested Logic & Formula Auditing

Syllabus Alignment: Cambridge 0417 Unit 20; WASSCE Computer Studies (Advanced Spreadsheets)
Mission Objective: Master complex logical functions (Nested IF, COUNTIF, SUMIF, AND, OR), error trapping, and formula auditing printout evidence for Cambridge Paper 3 examinations.

🎥 Video Lessons 💻 Practical Work ⚙️ Practice Tool 🧠 Notes 🃏 Key Words 🕵️ Common Mistakes 📝 Quiz 🎯 Self-Check

🎥 Video Lessons

Cambridge IGCSE · WAEC · Core Theory

Topic: Cambridge 0417 Paper 3: Advanced Nested IF, ROUNDUP & Formula Auditing | Duration: 17:30
What to look out for: Constructing tiered nested IF formulas =IF(B15<10, 4, IF(B15<=15, 6, 9)), rounding with ROUNDUP, and printing Formula View with gridlines.

⚙️ Hands-On Practice Tool

Interactive

Test Cambridge 0417/03 crew requirements: =IF(B15<10, 4, IF(B15<=15, 6, 9)). Move the journey hours slider to see crew tiers and cost allocations switch dynamically.

Journey Hours (B15): 12 hours

=IF(12 < 10, 4, IF(12 <= 15, 6, 9))

Evaluated Crew Tier: 6 Crew Members

Rate A Crew (2 x $25/hr): $600.00

Rate B Crew ((Crew - 2) x $18/hr): $864.00

🧠 Notes & Key Concepts

Theory Summary

🔹

• Simple IF: `=IF(logical_test, value_if_true, value_if_false)`.

🔹

• Nested IF: Replaces `value_if_false` with another `IF` function to evaluate multiple tiers. For N outcomes, exactly N - 1 `IF` statements are required. Ensure all open parentheses are closed at the end.

🔹

• Boolean Operators: `=IF(AND(A1>50, B1="Yes"), "Pass", "Fail")` requires both conditions to be true. `OR()` requires at least one condition to be true.

🔹

• COUNTIF & SUMIF: `=COUNTIF(range, criteria)` where criteria must be in quotes if it contains operators (e.g. `">50"`). `=SUMIF(range, criteria, [sum_range])`.

🔹

• Formula Auditing Printout: Cambridge Paper 3 examiners penalize submissions that fail to print row and column headings (1, 2, 3... and A, B, C...) or gridlines in formula view.

★ Cambridge / WAEC Model Scenario:

Construct a nested IF formula in cell E2 to assign grades based on the mark in cell D2: 80 and above = 'Distinction'; 60 and above = 'Merit'; 40 and above = 'Pass'; otherwise 'Unclassified'.

💡 Worked Solution & Rationale:

`=IF(D2>=80, "Distinction", IF(D2>=60, "Merit", IF(D2>=40, "Pass", "Unclassified")))`
Notice: 4 possible outcomes require 3 nested `IF` functions and 3 closing parentheses.

🃏 Key Words — Click a Card to Reveal the Definition

6 Terms

Card 01

Nested IF

👆 Click / Tap to Reveal Definition

Definition 01

A formula placing an IF function inside another IF function to evaluate multiple tiered conditions sequentially.

Card 02

ROUNDUP

👆 Click / Tap to Reveal Definition

Definition 02

Mathematical function that rounds a number up towards positive infinity to a specified number of decimal places.

Card 03

Formula View

👆 Click / Tap to Reveal Definition

Definition 03

Excel view mode (Ctrl + ~) that displays underlying calculation formulas instead of calculated numerical values.

Card 04

Gridlines

👆 Click / Tap to Reveal Definition

Definition 04

Light gray lines separating cells. Mandatory on Cambridge formula printouts alongside row/column headings.

Card 05

IFERROR

👆 Click / Tap to Reveal Definition

Definition 05

Error-handling function that traps runtime calculation errors (#N/A, #DIV/0!) and returns a custom friendly message.

Card 06

COUNTIF

👆 Click / Tap to Reveal Definition

Definition 06

Statistical function that counts cells within a specified range that satisfy a single criteria condition.

🕵️ Common Exam Mistakes — Can You Spot the Error?

Examiner Trap

⚠️ Candidate Error Trap:

A candidate wrote: `=IF(D2>=40, "Pass", IF(D2>=60, "Merit", IF(D2>=80, "Distinction", "Unclassified")))`

🎯 Your Detective Mission:

Explain why a student who scored 95 receives a grade of 'Pass' with this formula, and how to fix it.

📝 Quiz — Test Yourself (10 Questions)

Instant Marking & Explanation

1. How many nested `IF` functions are needed to evaluate 5 distinct outcomes?

A) 3

B) 4

C) 5

D) 6

Mark Scheme Rationale: N outcomes require N - 1 nested `IF` statements (5 - 1 = 4).

2. Which function counts the number of cells in range A1:A50 that contain values greater than 100?

A) `=COUNT(A1:A50, ">100")`

B) `=COUNTIF(A1:A50, ">100")`

C) `=SUMIF(A1:A50, ">100")`

D) `=TOTALIF(A1:A50, 100)`

Mark Scheme Rationale: `=COUNTIF(A1:A50, ">100")` is the correct function.

3. What is the keyboard shortcut in Excel to toggle between Values View and Formula View?

A) `Ctrl + F`

B) `Ctrl + ~` (tilde)

C) `Ctrl + P`

D) `Alt + F4`

Mark Scheme Rationale: `Ctrl + ~` toggles formula display mode.

4. Which function traps errors and displays a custom friendly message like 'Item Not Found'?

A) `IFERR()`

B) `IFERROR()`

C) `CATCH()`

D) `ISERROR()`

Mark Scheme Rationale: `IFERROR(formula, "friendly message")` catches runtime errors.

5. Which function sums values in column C only if the corresponding cell in column B equals 'Completed'?

A) `=SUM(C1:C20, B1:B20="Completed")`

B) `=SUMIF(B1:B20, "Completed", C1:C20)`

C) `=COUNTIF(B1:B20, "Completed")`

D) `=IFSUM(B1:B20, "Completed", C1:C20)`

Mark Scheme Rationale: `SUMIF(range, criteria, sum_range)`.

6. In Page Setup, where must an exam candidate go to enable row and column headings for printing?

A) Header/Footer tab

B) Sheet tab

C) Margins tab

D) Page tab

Mark Scheme Rationale: Page Setup -> Sheet tab -> Check 'Row and column headings'.

7. What does the formula `=AND(B2>10, C2<50)` return if B2=15 and C2=60?

A) TRUE

B) FALSE

C) `#VALUE!`

D) 0

Mark Scheme Rationale: `AND` requires both conditions to be true. C2<50 is false, so it returns FALSE.

8. What does the formula `=OR(B2>10, C2<50)` return if B2=15 and C2=60?

A) TRUE

B) FALSE

C) `#VALUE!`

D) 1

Mark Scheme Rationale: `OR` requires at least one condition to be true. B2>10 is true, so it returns TRUE.

9. Which error occurs when a formula attempts to divide a number by zero or an empty cell?

A) `#NULL!`

B) `#DIV/0!`

C) `#VALUE!`

D) `#NAME?`

Mark Scheme Rationale: `#DIV/0!` indicates division by zero.

10. What does `COUNTIFS` allow that standard `COUNTIF` cannot do?

A) Count text cells

B) Count cells across multiple criteria ranges simultaneously

C) Add numbers

D) Multiply cells

Mark Scheme Rationale: `COUNTIFS` evaluates multiple criteria pairs across multiple ranges.

Your Score: 0 / 10 (0%)

Answer the questions above to see your score.

🎯 How Did You Find This Week?

Self-Check

1. Rate your understanding of Week 07 (Advanced Spreadsheets: Nested Logic & Formula Auditing):

🏆 Got it — Exam Ready

⚖️ Mostly — Needs More Practice

🚨 Not Yet — Need Help

2. Next step: Open your YEAR 12 ICT & DIGITAL TECHNOLOGY — LIVING EXAM TRACKER.xlsx on Google Classroom and update your score for this week.