Statistical Analysis
Point-Biserial Correlation (r_pb) for Binary Groups & Exam Item Discrimination
Compute Point-Biserial Correlation r_pb = ((M_1 - M_0) / SD_pop) * SQRT(p * q) from subgroup means and verify algebraic identity with CORREL on 0/1 binary indicators.
Why using population STDEVP—not sample STDEV—matches Pearson CORREL identically
In exam item analysis (1 = answered item correctly, 0 = missed item vs total exam score) and clinical group comparisons, the Point-Biserial Correlation r_pb quantifies association between a true binary 0/1 variable X and a continuous score Y. A classic textbook mistake substitutes sample standard deviation s_{n-1} into ((M_1 - M_0) / SD) * SQRT(p * q), producing a value that fails to match Pearson CORREL(X, Y) and can even exceed 1.0 in tiny samples!
Dividing subgroup mean gap (M_1 - M_0) by population standard deviation STDEVP(Y) (sigma_n) and multiplying by SQRT(p * (1 - p)) reproduces Pearson CORREL(X, Y) to 15 significant digits—and lets analysts compute exact r_pb from published summary tables (M_1, M_0, SD_n, p) even when raw respondent rows are unavailable.
| Diagnostic Quantity | Formula | 6-Examinee Sample ([88,92,90] vs [74,70,72]) |
|---|---|---|
| Group 1 Mean M_1 / Group 0 Mean M_0 | AVERAGEIF(B2:B7, 1..0, C2:C7) | 90.00 vs 72.00 (Gap = +18.00) |
| Population SD_n & Split SQRT(p*q) | STDEVP(C2:C7) & SQRT(0.5 * 0.5) | 9.1469 & 0.5000 |
| Closed-Form Point-Biserial r_pb | ((90 - 72) / 9.1469) * 0.5 | 0.9839 (Matches CORREL = 0.9839) |
Closed-Form Subgroup-Means Point-Biserial Formula
Guard degenerate all-0 or all-1 indicator columns (p = 0 or p = 1) before calling AVERAGEIF, and scale (M_1 - M_0) / STDEVP(C2:C7) by SQRT(p * (1 - p)).
=LET(p_one, AVERAGE(B2:B7), sd_all, STDEVP(C2:C7), IF(OR(p_one <= 0, p_one >= 1, sd_all = 0), "DEGENERATE_VARIANCE", LET(m_one, AVERAGEIF(B2:B7, 1, C2:C7), m_zero, AVERAGEIF(B2:B7, 0, C2:C7), ROUND(((m_one - m_zero) / sd_all) * SQRT(p_one * (1 - p_one)), 4))))Direct CORREL Parity Check
Verify that ROUND(CORREL(B2:B7, C2:C7), 4) equals E4 across every non-degenerate score mutation.
=ROUND(CORREL(B2:B7, C2:C7), 4)Try the example
Use the original values or change them to test your understanding.