Exam practice
Every question from the end of every unit, with hints and answers to open. After you check an answer, mark how you did, and come back to the ones you didn’t get.
10 questions
Unit 5 · Spreadsheets and Data
Explain the difference between a relative and an absolute reference, with an example of when you need each.
Answer
A relative reference (like B2) changes when the formula is copied: copied one row down, B2 becomes B3. You need it when each row works on its own data, for example =B2*C2 for each item’s amount on a bill, copied down the column.
An absolute reference (like $B$20) has $ signs and doesn’t change when copied. You need it when every copy must use the same cell, for example dividing each item’s amount by the one total in B20 to find its share: =B2/$B$20*100.
A stationery shop’s sheet has prices in B2:B10 and numbers sold in C2:C10. i. What formula goes in D2 for the first item’s amount? ii. How do you get the amounts of the other items without typing eight more formulas? iii. Write the formula for the total in D11.
Answer
- i.
=B2*C2 - ii.Copy D2 down to D10 (drag the fill handle, or copy and paste). Each copy’s relative addresses move with it: D3 becomes
=B3*C3, D10=B10*C10. - iii.
=SUM(D2:D10)
What is the difference between sorting and filtering? Give a question about a class survey that each one answers.
Answer
Sorting puts all the rows in order by one column (whole rows moving together); nothing is hidden. Example: “Who lives farthest from school?”: sort by distance, descending, and look at the top row.
Filtering shows only the rows that match a value or condition and hides the others, without deleting them. Example: “Which students come by public bus?”: filter the Comes by column to Public bus.
A survey of 40 students has the house each belongs to (Everest, Kanchenjunga, Lhotse, Makalu) in B2:B41 and their marks in a quiz in C2:C41. Write formulas to find i. how many students are in Everest house, ii. the total marks of Lhotse house, iii. the average mark of Makalu house, and iv. how many students scored 15 or more.
Answer
- i.
=COUNTIF(B2:B41, "Everest") - ii.
=SUMIF(B2:B41, "Lhotse", C2:C41) - iii.
=AVERAGEIF(B2:B41, "Makalu", C2:C41) - iv.
=COUNTIF(C2:C41, ">=15")
The four house names are typed in F2:F5. Write one formula for G2 that counts the students in the house named in F2, and that can be copied down to G5 to count the other three houses. Explain why it needs $ signs.
Answer
=COUNTIF($B$2:$B$41, F2)
The range of houses, $B$2:$B$41, must stay the same in every copy, so it is fixed with $ signs. The house name, F2, must move: G3 needs F3, G4 needs F4. Without the $ signs, the copy in G3 would count in B3:B42, missing the first student and including an empty row.
Here are some answers from an online form. Name the problem in each row and say how you would fix it.
| Name | Comes by | km | Minutes |
|---|---|---|---|
| Sita | walking | 1.5 | 20 |
| Ram | Bicycle | 2 km | 12 |
| Nima | Bus | 6 | |
| Sabina | Walk | 1 | 200 |
| Ram | Bicycle | 2 km | 12 |
Answer
- Sita: “walking” is the same answer as “Walk” in different words. Change it to Walk.
- Ram: “2 km” is a number stored as words, which SUM and AVERAGE skip. Change it to 2 (the unit belongs in the header). His row also appears twice: a repeat, so delete one copy.
- Nima: the minutes are missing. Ask Nima if you can; otherwise leave the cell empty (not 0) and say that the average is of those who answered.
- Sabina: 200 minutes for a 1 km walk is impossible (over three hours). Ask her; probably she meant 20. If you can’t ask, leave it out of the average and note why.
A health post records each patient’s age in one of three ways: exact age in years; age groups (0–14, 15–59, 60 and over); or just the number of patients each day. Give one question each way can answer that the next one can’t, and one reason to choose a less exact way.
Answer
- Exact age can answer “What is the average age of the patients?”, which age groups can’t.
- Age groups can answer “How many patients were children under 15?”, which the daily count can’t.
- The daily count can still answer “On which day were there most patients?”
A reason to choose a less exact way: it is quicker to record when the health post is busy, it needs less storage, and it says less about each patient, which protects their privacy. Exact ages are only worth collecting if a question needs them.
For 10 days, a shop wrote down the highest temperature and the number of cold drinks sold. The scatter chart shows a clear trend: hotter days, more cold drinks. i. Describe the trend in words. ii. How could the shop use it? iii. Give one limit of a prediction from it.
Answer
- i.The higher the temperature, the more cold drinks are sold: the dots climb from left to right in a band.
- ii.From the weather forecast, the shop can predict how many cold drinks it will sell tomorrow, and buy and chill enough stock, without buying too much.
- iii.Any one, for example: the prediction only works inside the temperatures recorded (on a day much hotter than any of the ten, it is a guess); ten days is a small amount of data; other things also change sales, like a festival or a holiday; and the trend holds for this shop, not necessarily for others.
Rina’s family gets Rs 30,000 a month. They spend Rs 8,000 on rent, Rs 12,000 on food, Rs 3,000 on school fees, Rs 2,000 on electricity and gas, and Rs 1,500 on phone and internet. i. Show how you would set this budget up in a spreadsheet, with formulas. ii. How much is left? iii. What if the rent rises to Rs 9,500?
Answer
i. For example:
| A | B | |
|---|---|---|
| 1 | Money in | 30000 |
| 2 | Rent | 8000 |
| 3 | Food | 12000 |
| 4 | School fees | 3000 |
| 5 | Electricity and gas | 2000 |
| 6 | Phone and internet | 1500 |
| 7 | Total out | =SUM(B2:B6) |
| 8 | Left to save | =B1-B7 |
- ii.Total out = 8,000 + 12,000 + 3,000 + 2,000 + 1,500 = Rs 26,500, so Rs 30,000 − Rs 26,500 = Rs 3,500 is left.
- iii.Change B2 to 9500. Total out becomes Rs 28,000, and the formulas show at once that only Rs 2,000 is left.
A student surveyed 15 friends in her own class through a messaging app, asking “Don’t you agree that our school day is too long?” She reports: “Most students in Nepal think the school day is too long.” List four problems with her investigation, and rewrite her finding honestly.
Answer
- Who was asked: 15 friends in one class, not students across Nepal, or even across her school. Her friends may think like her.
- Who could answer: only those on the messaging app.
- A leading question: “Don’t you agree…” pushes people to say yes. A fair question would be “Is our school day too long, about right, or too short?”
- Too small: 15 answers can’t speak for a whole country.
She also doesn’t say how many answered which way, so there is no evidence for “most”.
An honest finding: “Of 15 of my friends in Class 7, asked through a messaging app, 11 agreed that our school day is too long. The question may have pushed them to agree, and they may not think like other students.” (11 stands for whatever her real count was.)