How to calculate a z-score
✓ Verified in LibreOffice 25.8.7.3How many standard deviations a value sits from the mean — outlier detection, comparing across scales.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =(A2-AVERAGE($A$2:$A$100))/STDEV.P($A$2:$A$100) | Or the built-in =STANDARDIZE(A2,AVERAGE(...),STDEV.P(...)). Use STDEV.S for sample data. |
| Google Sheets | =(A2-AVERAGE($A$2:$A$100))/STDEV.P($A$2:$A$100) | Identical, including STANDARDIZE. |
| LibreOffice Calc | =(A2-AVERAGE($A$2:$A$100))/STDEV.P($A$2:$A$100) | Identical. |
How it works
Subtract the mean, divide by the standard deviation: the 6 in this data (mean 4, σ ≈ 1.265) is 1.58 standard deviations above average. Z-scores make different scales comparable — a 1.58 on test A means the same relative standing as a 1.58 on test B — and power the common |z| > 3 outlier rule. Population vs sample deviation (P vs S) changes the denominator slightly; be consistent across the column, and see the standard-deviation recipe for which to pick.
Verified, not just documented
We ran =ROUND((A6-AVERAGE(A2:A6))/STDEV.P(A2:A6),4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 1.5811 — exactly the expected result. Every formula here is confirmed by actually executing it.