12.4 Spreadsheets

When data grows large, say six subject marks for two dozen students, totalling and averaging by hand is slow and mistake-prone. A spreadsheet is a digital notebook laid out in rows and columns of little boxes called cells. Each cell can hold text, a number, or a formula.

A range of cells is written Start:End. For example:

We then boil a range down with a built-in function:

Try This (on a computer) Open any spreadsheet app (Google Sheets, LibreOffice Calc, Excel) and key in a small marks table. Then:

  • type =AVERAGE(...) beneath a subject column to get the class average for that subject;
  • compare two subjects’ class averages, does English beat Telugu?
  • type =SUM(...) along each student’s row to get their total score.

One formula, dragged down a column, replaces dozens of hand additions.

Figure it Out: Mean, Median & Spreadsheets

Practice

  1. Find the mean of each, and note what you spot:   (i) the first 50 natural numbers;   (ii) the first 50 odd numbers;   (iii) the first 50 multiples of 4.
  2. A dot plot has one dot missing. Where must the missing value sit so the mean becomes 9? (Use the balance idea: the new value must restore equal total distances about 9.)
  3. Riya measured all 24 students’ heights with shoes on and got an average of 152.4 cm. Each pair of shoes adds 1.2 cm. (i) Must everyone be re-measured? (ii) What is the true average height?
  4. Three dot plots show the lengths (in minutes) of songs in three albums. Only one has mean 5.57 minutes. Explain how you’d decide which, using the balance idea rather than re-adding everything.
  5. Find the median of 7, 11, 18, 22, 27, 33, 39, 40, 40, 47, 52, 54, 69, 83, 90, 93. (i) What single value could be added without changing the median? (ii) What two values could be added without changing it? (iii) What value could be removed without changing it?
  6. State whether each is always, sometimes, or never true:   (i) Removing a value less than the median will decrease the median.   (ii) Including a value less than the mean will decrease the mean.   (iii) Including any 4 values will not affect the median.   (iv) Including 4 values less than the median will increase the median.
  7. The mean of 9, 14, 11, 5, 6, 21, y, 11 is 10.5. Find y.
  8. A data set of 16 values has mean 142. Find the sum of the data.
  9. For the data 13, 46, 9, 71, 19, 34, 38, 9, 28, 24, p, which of these could be p if the median is 28?   (i) 11   (ii) 24   (iii) 40   (iv) 100   (v) 28   (vi) 46   (vii) 30
  10. Six friends scored 5, 9, 7, 12, m, 8 marks in a quiz and their mean is 8.5. Find m, then state the median.
    1. Mean = \dfrac{1+2+\dots+50}{50} = \dfrac{1275}{50} = \mathbf{25.5}.   (ii) First 50 odd numbers: mean = \mathbf{50} (their sum is 50^2 = 2500).   (iii) First 50 multiples of 4: mean = \mathbf{102}. Observation: for evenly spaced numbers the mean is just the average of the first and last value, e.g. \frac{1+50}{2}=25.5, \frac{1+99}{2}=50, \frac{4+200}{2}=102.
  2. The total distance above 9 must equal the total distance below 9. Place the missing dot wherever it makes those two totals match, i.e. so its distance from 9 exactly cancels the leftover imbalance on the other side.
    1. No, there’s a quicker route. Every height was inflated by the same 1.2 cm, so the mean is inflated by exactly 1.2 cm. (ii) Correct average = 152.4 - 1.2 = \mathbf{151.2} cm.
  4. Find (or estimate) the balance point of each plot. The album whose dots balance at 5.57 minutes is the answer. Quick check: the mean sits where left-distances and right-distances cancel, eyeball which plot’s “centre of mass” hovers near 5.57.
  5. There are 16 values; median = \dfrac{40+40}{2} = \mathbf{40}. (i) Adding any value equal to \mathbf{40} keeps 40 in the middle. (ii) Add one value \le 40 and one \ge 40 (e.g. 30 and 50) so the middle pair stays 40,40. (iii) Remove a value so the new middle is still 40, e.g. remove a 40, leaving 15 values whose 8th (middle) value is still 40.
    1. Sometimes, only if it actually shifts which value occupies the middle. (ii) Always, a value below the mean lowers the total relative to the count, dragging the mean down. (iii) Sometimes, depends where the four values fall relative to the middle. (iv) Never, adding values below the median can only hold it or push it down, never up.
  7. Sum of the seven known values = 9+14+11+5+6+21+11 = 77. Mean \times 8 = 10.5 \times 8 = 84, so y = 84 - 77 = \mathbf{7}.
  8. Sum = \text{mean} \times \text{count} = 142 \times 16 = \mathbf{2272}.
  9. Sorting the ten known values: 9, 9, 13, 19, 24, 28, 34, 38, 46, 71. Adding p gives 11 values, so the median is the 6th. For the median to be 28, p must not push 28 out of the 6th slot, this needs p \ge 28. Valid choices: (iii) 40, (iv) 100, (v) 28, (vi) 46, (vii) 30. (The values 11 and 24, both below 28, would drop the median.)
  10. Known five sum to 5+9+7+12+8 = 41; mean \times 6 = 8.5 \times 6 = 51, so m = 51 - 41 = \mathbf{10}. Sorted: 5, 7, 8, 9, 10, 12; median = \dfrac{8+9}{2} = \mathbf{8.5}.