Welcome to Armstrong Computers College, where vision meets excellence. This blog presents the full NVTI past paper for Level Three Spreadsheet Management conducted in May/June 2017. The purpose is to help students understand how spreadsheet concepts are examined and to provide a complete guide for practicing theory and practical tasks in Microsoft Excel.
SECTION A – Theory Questions and Answers
1. State and explain the general syntax for IF statement in MS Excel.
=IF(logical_test, value_if_true, value_if_false)2. Explain Pivot tables and state their uses.
Pivot tables summarize large data sets, helping to group, filter, and analyze data dynamically without changing the raw values.
3. Procedure for locking MS Excel worksheet and cells.
Select cells → Format Cells → Protection tab → Tick "Locked" → Review tab → Protect Sheet → (Optional password).
4. Differentiate between ROUND and TRUNC formulas.
ROUND rounds off a number to specific digits. TRUNC cuts off digits after the decimal point without rounding.
5. List five (5) statistical functions/formulas.
AVERAGE, MAX, MIN, COUNT, STDEV.P
6. Explain the component of a nested IF statement.
A nested IF includes multiple IFs within each other to test more than one condition:
=IF(A1<40,"FAIL",IF(A1<60,"PASS",IF(A1<80,"CREDIT","DISTINCTION")))7. Write down the formulas for the following:
- Minimum:
=MIN(range) - Maximum:
=MAX(range) - Lower case of BOY:
=LOWER("BOY") - Current date and time:
=NOW() - Join "JOHN" and "BULL":
=CONCATENATE("JOHN","BULL")or="JOHN"&"BULL"
8. Explain the formula auditing mode and shortcut key.
Formula auditing is used to trace relationships between cells and formulas. Shortcut: Ctrl + `
9. What is validation in Excel and how to restrict 5–10 only?
Validation limits the type of input. Use Data > Data Validation > Allow: Whole number → Between 5 and 10.
10. How to insert a text file into an Excel environment?
Data tab → Get External Data → From Text/CSV → Browse and import → Use import wizard.
SECTION B – Spreadsheet Practical Task
a) Reproduce the worksheet below
| CANDIDATE ID | GENDER CODE | MARKS | GRADE INTERPRETATION |
|---|---|---|---|
| NVTI2014JUNE010001 | M | 23 | |
| NVTI2014JUNE010002 | F | 62 | |
| NVTI2014JUNE010003 | M | 77 | |
| NVTI2014JUNE010004 | F | 9 | |
| NVTI2014JUNE010005 | M | 2 | |
| NVTI2014JUNE010006 | F | 45 | |
| NVTI2014JUNE010007 | M | 67 | |
| NVTI2014JUNE010008 | F | 87 | |
| NVTI2014JUNE010009 | M | 99 | |
| NVTI2014JUNE010010 | F | 87 | |
| NVTI2014JUNE010011 | M | 45 | |
| NVTI2014JUNE010012 | F | 34 | |
| NVTI2014JUNE010013 | M | 67 | |
| NVTI2014JUNE010014 | F | 88 | |
| NVTI2014JUNE010015 | M | 5 | |
| NVTI2014JUNE010016 | F | 34 | |
| NVTI2014JUNE010017 | M | 56 | |
| NVTI2014JUNE010018 | F | 78 | |
| NVTI2014JUNE010019 | M | 90 | |
| NVTI2014JUNE010020 | F | 88 | |
| NVTI2014JUNE010021 | M | 55 | |
| NVTI2014JUNE010022 | F | 22 | |
| NVTI2014JUNE010023 | M | 55 | |
| NVTI2014JUNE010024 | F | 67 | |
| NVTI2014JUNE010025 | M | 12 | |
| NVTI2014JUNE010026 | F | 43 | |
| NVTI2014JUNE010027 | M | 55 | |
| NVTI2014JUNE010028 | F | 88 | |
| NVTI2014JUNE010029 | M | 99 | |
| NVTI2014JUNE010030 | F | 55 | |
| STATISTICAL VALUES | |||
| HIGHEST MARK | |||
| LOWEST MARK | |||
| AVERAGE MARK | |||
| TOTAL MARK | |||
| MEDIAN MARK | |||
| STANDARD DEVIATION | |||
- Input the provided data records.
- Use formulas to calculate:
- Highest Mark:
=MAX(range) - Lowest Mark:
=MIN(range) - Average Mark:
=AVERAGE(range) - Total Mark:
=SUM(range) - Median Mark:
=MEDIAN(range) - Standard Deviation:
=STDEV.P(range)
- Highest Mark:
- Apply nested IF formula for GRADE INTERPRETATION:
LOWER UPPER GRADE
INTERPRETATION0 39 REFERRED 40 59 PASS 60 79 CREDIT 80 100 DISTINCTION =IF(MARK<40,"REFERRED",IF(MARK<60,"PASS",IF(MARK<80,"CREDIT","DISTINCTION"))) - Bold all headings and statistical results.
- Add borders around the data.
- Save and print the spreadsheet.
- Use Ctrl + ` to display formulas, adjust layout to A4 Landscape, and print again.