Nobody works out 50,000 squares by hand
You have learned to find a mean, a spread and a quartile with a few numbers. Real data has thousands of rows.
Every tool has ready-made formulas for these measures. The skill is to know the name, what it does, and when to use it.
Key words
| Word | What it means |
|---|---|
| Function | a ready-made calculation with a name |
| Formula | a calculation typed into a cell, starting with = |
| Aggregate | a function that turns many rows into one value, such as an average |
| Central tendency | a single value for the typical value of the data |
| Dispersion | how spread out the values are |
| Position | where a value sits when the data is put in order |
| Interpolate | work out a value between two known values |
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can name the function for each measure in a spreadsheet, in SQL and in Python, and say when each is used.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
One measure, three tools
| Measure | Spreadsheet formula | SQL or DuckDB | Python statistics |
|---|---|---|---|
| Mean | `=AVERAGE(A1:A8)` | `avg(score)` | `mean(data)` |
| Median | `=MEDIAN(A1:A8)` | `median(score)` | `median(data)` |
| Mode | `=MODE(A1:A8)` | `mode(score)` | `mode(data)` |
| Minimum and maximum | `=MIN(A1:A8)`, `=MAX(A1:A8)` | `min(score)`, `max(score)` | `min(data)`, `max(data)` |
| Standard deviation | `=STDEV.P(A1:A8)` | `stddev_pop(score)` | `pstdev(data)` |
| Quartile | `=QUARTILE(A1:A8,1)` | `quantile_cont(score, 0.25)` | `quantiles(data, method="inclusive")` |
| Percentile | `=PERCENTILE(A1:A8,0.9)` | `quantile_cont(score, 0.9)` | `quantiles(data, n=10, method="inclusive")` |
The cells students fill in are left blank in this preview.
No spreadsheet here
Important
This platform has no spreadsheet program, so the formulas above are shown as text. DuckDB and Python run the same measures here. On site you use a real spreadsheet program.
Try it: all the measures in DuckDB
SELECT count(*) AS n,
min(score) AS lowest,
max(score) AS highest,
avg(score) AS mean,
median(score) AS median,
stddev_pop(score) AS sd,
quantile_cont(score, 0.25) AS q1,
quantile_cont(score, 0.75) AS q3
FROM scores;
Why the quartiles differ from yours
By hand you found Q1 as 4 and Q3 as 6 for these eight scores. The tool says Q3 is 5.5.
It interpolates: it works out a value between two scores. Both ways are used. What matters is that you know the method and say which you used.
Try it: Python's statistics module
from statistics import mean, median, mode, pstdev
data = [2, 4, 4, 4, 5, 5, 7, 9]
print("mean", mean(data))
print("median", median(data))
print("mode", mode(data))
print("pstdev", pstdev(data))
# pstdev is the population form, the one divided by how many.
# statistics.stdev is the sample form. Try it.