Basic Statistics
Number of bits of data: =COUNT(range)
Sum of data: =SUM(range)
Mean: =AVERAGE(range)
Median: =MEDIAN(range)
Mode: =MODE.SNGL(range)
5‑Figure Summary
Highest (H): =MAX(range)
Lowest (L): =MIN(range)
Lower Quartile (Q1)
Using =QUARTILE.INC(data,1) can give unexpected values, because Excel calculates quartiles as percentiles rather than medians of halves.
This SMALL‑based formula works reliably:
=IF(ISEVEN(ROUNDDOWN(COUNT(range)/2,0)),
AVERAGE(
SMALL(range,ROUNDDOWN(COUNT(range)/2,0)/2),
SMALL(range,ROUNDDOWN(COUNT(range)/2,0)/2+1)
),
SMALL(range,ROUNDUP(ROUNDDOWN(COUNT(range)/2,0)/2,0))
)
Median (Q2): =MEDIAN(range)
Upper Quartile (Q3)
Excel’s =QUARTILE.INC(data,3) may also behave unexpectedly.
This LARGE‑based formula works reliably:
=IF(ISEVEN(ROUNDDOWN(COUNT(range)/2,0)),
AVERAGE(
LARGE(range,ROUNDDOWN(COUNT(range)/2,0)/2),
LARGE(range,ROUNDDOWN(COUNT(range)/2,0)/2+1)
),
LARGE(range,ROUNDUP(ROUNDDOWN(COUNT(range)/2,0)/2,0))
)
More
Range: =H − L
IQR: =Q3 − Q1
SIQR: =IQR / 2
Standard Deviation: =STDEVA(range)
Variance: =VARA(range)
Z‑test (one‑tail): =Z.TEST(range, value)
Pearson correlation: =PEARSON(range1, range2)
t‑test (1‑tail, paired): =T.TEST(range1, range2, 1, 1)
t‑test (2‑tail, paired): =T.TEST(range1, range2, 2, 1)
t‑test (2‑tail, equal variance): =T.TEST(range1, range2, 2, 2)
t‑test (2‑tail, unequal variance): =T.TEST(range1, range2, 2, 3)
Excel Example
Test scores for Maths and Physics tests are compared.
The spreadsheet is set up with data set A in cells C4:C3000 and data set B in cells D4:D3000.
Summary of Maths data:
Summary of Physics data:
Show Formula view:
Formula sheet:
Q1 formula
=IF(ISEVEN(ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)),
AVERAGE(
SMALL($C$4:$C$3000,ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)/2),
SMALL($C$4:$C$3000,ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)/2+1)
),
SMALL($C$4:$C$3000,ROUNDUP(ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)/2,0))
)
Q3 formula
=IF(ISEVEN(ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)),
AVERAGE(
LARGE($C$4:$C$3000,ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)/2),
LARGE($C$4:$C$3000,ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)/2+1)
),
LARGE($C$4:$C$3000,ROUNDUP(ROUNDDOWN(COUNT($C$4:$C$3000)/2,0)/2,0))
)
