# Spreadsheets that make sense
FindTutor • Original ICT / IT practice pack

## A small shop worksheet
Enter the following data into cells A1:C4:

| Item | Quantity | Unit price |
|---|---|---|
| Notebook | 3 | 4 |
| Pen | 5 | 2 |
| Folder | 2 | 6 |

D1: Line total. F1: Discount rate. F2: 0.10 (format as 10%). E1: Discounted total.

## Relative references
In D2, `=B2*C2` gives 12. Copying the formula down changes the row references: D3 uses `=B3*C3`.

## Absolute references
In E2, use `=D2*(1-$F$2)`. Copy down to E4. `$F$2` stays fixed as the formula moves; D2 becomes D3 and then D4.

A mixed reference locks only the row or column. `$B2` locks column B; `B$2` locks row 2.

## Practice
1. Calculate all three line totals in D2:D4.
2. Calculate the discounted totals in E2:E4.
3. Write a formula for the combined undiscounted total in D5.
4. Write a formula in G2 showing "Restock" if B2 is less than 4, otherwise "Enough". Copy it down.
5. What goes wrong if E2 uses F2 without dollar signs and is copied down?
6. Suggest a validation rule for quantities, and explain why validation does not guarantee accuracy.
7. Which chart would best compare the three line totals? Give one reason.

## Answers
1. D2 = 12, D3 = 10, D4 = 12.
2. E2 = 10.8, E3 = 9, E4 = 10.8. Format as currency or two decimal places as appropriate.
3. `=SUM(D2:D4)` gives 34.
4. `=IF(B2<4,"Restock","Enough")`. Results: Restock, Enough, Restock.
5. F2 becomes F3 and then F4, so the formulas no longer reference the discount rate.
6. Require a whole number greater than or equal to zero. A plausible but incorrectly entered quantity could still pass the rule.
7. A bar or column chart allows direct comparison of categories. Label the items and the value axis clearly.

Formula separators may vary by spreadsheet locale. Follow your qualification's required software and practical conventions. This is an introductory original worksheet, not an official exam-board publication.