Statistical
Brier Score & Binary Log-Loss (Cross-Entropy) probability calibration formulas
Evaluate probabilistic forecasts and ML classifiers using Brier Score AVERAGE((p - y)^2) and Binary Cross-Entropy Log-Loss -AVERAGE(y*LN(p) + (1-y)*LN(1-p)).
Why accuracy % hides overconfident bad probabilities—and how Brier & Log-Loss score them!
For binary outcomes y in {0,1}, the Brier score is the mean squared probability error. It reflects both calibration and discrimination; a lower total does not by itself prove better calibration. Constant predictions of 0.5 have Brier score 0.25, but a class-prevalence baseline is often a more informative comparison.
Binary log loss uses the negative natural log of the probability assigned to the observed outcome. Its penalty diverges logarithmically as that probability approaches zero, not exponentially. This implementation clips valid probabilities to [1e-6,1-1e-6] for a finite numerical approximation. Clipping changes endpoint scores: a certainly wrong prediction gets about 13.8155 instead of infinite loss, and a certainly correct prediction has a tiny positive loss.
| Forecast p (A) vs Actual y (B) | Squared Error (p - y)^2 (C) | Cross-Entropy Loss (D) | Model Summary |
|---|---|---|---|
| p = 0.90, y = 1 | 0.0100 | -LN(0.90) = 0.1054 | Confident hit |
| p = 0.80, y = 1 | 0.0400 | -LN(0.80) = 0.2231 | Good hit |
| p = 0.30, y = 0 | 0.0900 | -LN(0.70) = 0.3567 | Mild miss |
| p = 0.10, y = 0 | 0.0100 (Mean Brier = 0.0375) | -LN(0.90) = 0.1054 (Mean = 0.1976) | Beats 0.25 coin-flip! |
Brier Score across probabilistic predictions
C2:C5 computes =(A2 - B2) ^ 2; AVERAGE(C2:C5) gives exact Brier Score (0.0375).
=IF(OR(COUNT(A2:B5)<>8,MIN(A2:A5)<0,MAX(A2:A5)>1,AND(B2<>0,B2<>1),AND(B3<>0,B3<>1),AND(B4<>0,B4<>1),AND(B5<>0,B5<>1)),"Invalid inputs",ROUND(AVERAGE(C2:C5), 4))Clipped Binary Cross-Entropy (Log-Loss) row & mean formula
D2:D5 clips p safely via MIN(0.999999, MAX(0.000001, A2)) before calling LN.
Validate all four rows first: p must be numeric in [0,1] and y must be numeric 0 or 1. Clipping is not permission to accept malformed probabilities outside [0,1].
=IF(OR(COUNT(A2:B5)<>8,MIN(A2:A5)<0,MAX(A2:A5)>1,AND(B2<>0,B2<>1),AND(B3<>0,B3<>1),AND(B4<>0,B4<>1),AND(B5<>0,B5<>1)),"Invalid inputs",ROUND(AVERAGE(D2:D5), 4))Try the example
Use the original values or change them to test your understanding.