The official lab is headed "Advanced Spreadsheets/Excel Lab/PSPP Open Source". All 15 experiments are spreadsheet exercises, and the practical exam tests them in a spreadsheet — so learn these first.
Then do each one again in Python. See
SYLLABUS-REVIEW.md finding D8: this lab never
touches Python even though Python is taught in the same semester, and that gap
is worth closing yourself. The Python versions live in
python/ and every number below was cross-checked against them.
NOTE
Experiment 2 is reconstructed. The official text survives only as the fragment "a positive result." — see review findings D1 and D3.
Before you start: turn on the Analysis ToolPak, which several experiments need. File → Options → Add-ins → Manage: Excel Add-ins → Go → tick Analysis ToolPak. In LibreOffice the equivalents live under Data → Statistics.
Task: build a contingency table from sales data, compute conditional probabilities, and check whether the variables are independent.
Region in A, Purchase in B.Drag Region to Rows, Purchase to Columns, and Purchase to Values
(set it to Count). That is your contingency table.
Add the margins: Design → Grand Totals → On for Rows and Columns.
Joint probability of each cell: =cell / $grand_total$ — anchor the grand
total with $ so it does not shift when you fill across.
Conditional probability P(Premium | North): =cell / row_total.
=row_total*col_total/grand_total
for each cell. If those expected values match the observed counts, the
variables are independent. They rarely do exactly — Experiment 15's
chi-square test tells you whether the gap is bigger than chance.WHY IT MATTERS
Key formulas: COUNTIFS(A:A,"North",B:B,"Premium") gives one cell directly
without a pivot table.
Task (reconstructed): a disease affects 1% of the population. A test is 99% sensitive and 95% specific. Given a positive result, what is the probability the person has the disease?
Lay it out as a table:
| Cell | Formula | Value |
|---|---|---|
B1 P(D) |
typed | 0.01 |
B2 P(not D) |
=1-B1 |
0.99 |
B3 P(+ \| D) sensitivity |
typed | 0.99 |
B4 P(- \| not D) specificity |
typed | 0.95 |
B5 P(+ \| not D) |
=1-B4 |
0.05 |
B6 P(+) |
=B3*B1+B5*B2 |
0.0594 |
B7 P(D \| +) |
=B3*B1/B6 |
0.1667 |
WHY IT MATTERS
The point: a 99%-sensitive test, yet a positive result means only a 16.7% chance of having the disease. Out of 10,000 people, 99 true positives are swamped by 495 false positives. Confusing P(D|+) with P(+|D) is the base rate fallacy and it is the most examined idea in this part of the syllabus.
Build the 10,000-person table in the sheet — it makes the result obvious.
Marks in A2:A21.
| Measure | Formula |
|---|---|
| Mean | =AVERAGE(A2:A21) |
| Median | =MEDIAN(A2:A21) |
| Mode | =MODE.SNGL(A2:A21) (single) or =MODE.MULT(A2:A21) (several) |
| Count | =COUNT(A2:A21) |
Then demonstrate why it matters: add one mark of 500 in A22 and watch the
mean lurch while the median barely moves. Write that comparison in the sheet —
it is the interpretation marks, not the formula, that examiners reward.
MODE.SNGL returns #N/A when every value is unique. That is correct
behaviour, not an error to hide — say "no mode" in your answer.
| Measure | Formula | Note |
|---|---|---|
| Range | =MAX(A2:A21)-MIN(A2:A21) |
|
| Q1 | =QUARTILE.INC(A2:A21,1) |
|
| Q3 | =QUARTILE.INC(A2:A21,3) |
|
| IQR | =QUARTILE.INC(A2:A21,3)-QUARTILE.INC(A2:A21,1) |
|
| Sample variance | =VAR.S(A2:A21) |
divides by n−1 |
| Population variance | =VAR.P(A2:A21) |
divides by n |
| Sample sd | =STDEV.S(A2:A21) |
|
| Population sd | =STDEV.P(A2:A21) |
|
| Coefficient of variation | =STDEV.S(A2:A21)/AVERAGE(A2:A21) |
format as % |
.S or .P is the decision that costs marks. Use .S when your data is a
sample from a larger population — which it almost always is in these exercises.
Using .P on a sample understates the spread.
Outlier fences: =Q1-1.5*IQR and =Q3+1.5*IQR, then flag values outside
them with conditional formatting.
40, 50, 60, 70, 80, 90, 100 in C2:C8.Data → Data Analysis → Histogram. Input range A2:A21, bin range
C2:C8, tick Chart Output.
Right-click the bars → Format Data Series → Gap Width = 0. Histogram bars must touch; that is what distinguishes a histogram from a bar chart.
Comment on the shape — the question asks for it:
mean > median → tail to the right, positively skewedmean < median → tail to the left, negatively skewedmean ≈ median ≈ mode → symmetricQuantify it: =3*(AVERAGE(range)-MEDIAN(range))/STDEV.S(range) is Pearson's
skewness coefficient. Excel also has =SKEW(range).
Build a two-way count with COUNTIFS, e.g.
=COUNTIFS($A:$A,$E2,$B:$B,F$1) filled across a small grid.
Select the grid → Insert → Column Chart → Clustered Column.
Histogram vs bar chart — state the difference in your answer: a bar chart shows categories with gaps between bars in any order; a histogram shows continuous data in adjacent intervals with no gaps. Drawing the wrong one is a routine way to lose marks.
Hours studied in A, exam score in B.
Select both columns → Insert → Scatter (Markers only). Never join the points with lines.
Right-click a point → Add Trendline → Linear → tick Display Equation and Display R-squared.
Correlation: =CORREL(A2:A11,B2:B11) or =PEARSON(A2:A11,B2:B11).
=COVARIANCE.S(A2:A11,B2:B11) for a sample,
=COVARIANCE.P(...) for a population.Interpretation, which is where the marks are:
| |r| | Reading |
|---|---|
| 0.9 – 1.0 | very strong |
| 0.7 – 0.9 | strong |
| 0.4 – 0.7 | moderate |
| below 0.4 | weak |
Covariance tells you only the direction; its size depends on the units, so converting hours to minutes multiplies it by 60. Correlation is unit-free and bounded by ±1, which is why it is the one you report.
Always add: correlation is not causation.
Discrete: =RANDBETWEEN(1,6) filled down 1000 rows simulates a die. Tally
with =COUNTIF($A$2:$A$1001,D2) and chart it.
Continuous: =NORM.INV(RAND(),100,15) draws from Normal(100, 15).
RAND() and RANDBETWEEN() are volatile — they recalculate on every edit.
To freeze a sample, copy the column and Paste Special → Values. Do this
before computing anything from it, or your statistics will change as you work.
Values in A, probabilities in B.
=SUM(B2:B6) equals exactly 1. If not, stop — the data is wrong.C2: =A2*B2, filled down. Then E(X) = =SUM(C2:C6).D2: =A2^2*B2, filled down. Then E(X²) = =SUM(D2:D6).Var(X) = =SUM(D2:D6)-SUM(C2:C6)^2SD(X) = =SQRT(variance)The shortcut Var(X) = E(X²) − [E(X)]² is faster than E[(X−μ)²] and gives an identical answer. Use it under time pressure.
Binomial, n = 10, p = 0.3, with k in A2:A12:
P(X = k): =BINOM.DIST(A2,10,0.3,FALSE)P(X ≤ k): =BINOM.DIST(A2,10,0.3,TRUE)=10*0.3, variance =10*0.3*0.7Poisson, λ = 3:
=POISSON.DIST(A2,3,FALSE)=POISSON.DIST(A2,3,TRUE)The last argument, FALSE/TRUE, switches between PMF and CDF. Getting it
backwards is the most common error in this experiment.
Chart both as column charts and describe the shapes: the binomial is symmetric at p = 0.5 and skewed otherwise; the Poisson is right-skewed for small λ and approaches a normal shape as λ grows.
Normal(100, 15):
| Quantity | Formula |
|---|---|
=NORM.DIST(x,100,15,FALSE) |
|
CDF P(X ≤ x) |
=NORM.DIST(x,100,15,TRUE) |
P(X > x) |
=1-NORM.DIST(x,100,15,TRUE) |
P(a ≤ X ≤ b) |
=NORM.DIST(b,...,TRUE)-NORM.DIST(a,...,TRUE) |
| Inverse (percentile) | =NORM.INV(0.95,100,15) |
| Standard normal | =NORM.S.DIST(z,TRUE), =NORM.S.INV(p) |
Verify the empirical rule in the sheet: ±1 sd ≈ 68.27%, ±2 ≈ 95.45%, ±3 ≈ 99.73%.
Exponential(λ = 0.5): =EXPON.DIST(x,0.5,FALSE) for the PDF,
TRUE for the CDF. Mean = 1/λ, variance = 1/λ².
Demonstrate memorylessness: P(X > 5 | X > 2) = P(X > 3). Compute both sides and show they match.
Extends Experiment 7 with Spearman's rank correlation.
=CORREL(A2:A11,B2:B11).Rank each column: =RANK.AVG(A2,$A$2:$A$11,1). Use RANK.AVG, not RANK,
so that ties get averaged ranks.
Spearman: =CORREL(rank_x_range, rank_y_range), or use the formula
ρ = 1 − 6Σd² / n(n²−1) where d is the difference in ranks.
For several variables at once: Data → Data Analysis → Correlation builds the whole correlation matrix.
When to use which: Pearson measures linear association and assumes roughly normal data. Spearman works on ranks, so it catches any monotonic relationship and shrugs off outliers.
Input Y range = scores, Input X range = hours, tick Labels, Residuals and Line Fit Plots.
Read the output:
| Output | Meaning |
|---|---|
Multiple R |
|r|, the correlation |
R Square |
fraction of variance in y explained by x |
Adjusted R Square |
R² penalised for extra predictors |
Standard Error |
typical size of a residual |
Coefficients: Intercept |
b₀ |
Coefficients: X Variable 1 |
b₁, the slope |
Significance F / P-value |
tests whether the slope is really non-zero |
Formula shortcuts: =SLOPE(y,x), =INTERCEPT(y,x), =RSQ(y,x),
=FORECAST.LINEAR(new_x, y, x).
Interpret the slope in context — "each extra hour of study is associated with about 4.3 more marks" — not just "b₁ = 4.3". Check the residual plot for patterns: a curve means a straight line was the wrong model. And never predict outside the range of x you observed; that is extrapolation.
For simple regression, R² = r² and t² = F. Use both as arithmetic checks on your own work.
=AVERAGE(range), =STDEV.S(range), =COUNT(range).=STDEV.S(range)/SQRT(COUNT(range)).Margin of error, population sd unknown (the usual case):
=CONFIDENCE.T(0.05, STDEV.S(range), COUNT(range))
Population sd known: =CONFIDENCE.NORM(0.05, sigma, n)
mean ± margin.Build all three of 90%, 95% and 99% and note in the sheet that the interval widens as confidence rises — more certainty costs precision — and narrows as n grows, in proportion to 1/√n.
Write the interpretation carefully. "If we repeated this sampling many times, about 95% of the intervals so constructed would contain the true population mean." Not "there is a 95% probability the true mean is in this interval" — the true mean is a fixed number, not a random one.
State H₀ and H₁, then α, then the statistic, then the p-value, then the decision, then a conclusion in the words of the original problem. Every step earns marks.
z-test (population sd known):
=(xbar-mu0)/(sigma/SQRT(n)), p-value =2*(1-NORM.S.DIST(ABS(z),TRUE))
t-test (population sd unknown):
=T.TEST(range1, range2, tails, type) where tails is 1 or 2 and type is
1 = paired, 2 = equal variances, 3 = unequal variances. Or use
Data Analysis → t-Test: Two-Sample Assuming Equal Variances.
Chi-square test of independence:
=row_total*col_total/grand_total.=CHISQ.TEST(observed_range, expected_range) returns the p-value
directly — not the statistic. For the statistic use
=CHISQ.INV.RT(p, df) or sum (O−E)²/E by hand.
Check every expected frequency is at least 5, and say so.
F-test for equal variances: =F.TEST(range1, range2) gives a two-tailed
p-value. By hand, put the larger variance on top:
F = s₁²/s₂² with df = (n₁−1, n₂−1).
Decision rule: p < α → reject H₀. Otherwise fail to reject H₀ — never "accept H₀", which claims more than the test can support.
| H₀ true | H₀ false | |
|---|---|---|
| Reject H₀ | Type I error (α) | Correct (power) |
| Fail to reject | Correct | Type II error (β) |
Lowering α makes Type I errors rarer and Type II errors more likely. Only a bigger sample reduces both.
PSPP is the free alternative to SPSS named in the syllabus, and may be what your lab has installed.
| Task | PSPP menu path |
|---|---|
| Descriptive statistics | Analyze → Descriptive Statistics → Descriptives |
| Frequencies / histogram | Analyze → Descriptive Statistics → Frequencies |
| Contingency table + chi-square | Analyze → Descriptive Statistics → Crosstabs |
| Correlation | Analyze → Bivariate Correlation |
| Regression | Analyze → Linear Regression |
| One-sample / independent t-test | Analyze → Compare Means |
Enter variables in Variable View first, then data in Data View — the opposite order to a spreadsheet, and the usual source of confusion.