Statistical & Aggregation
Discrete Probability Mass Interval, Expected Value & Variance (PROB & SUMIFS)
Validate discrete probability mass tables (sum = 1, non-negative weights), calculate interval probabilities P(L <= X <= U) via SUMIFS (PROB), and compute distribution mean and standard deviation.
Validate probability axioms before summing discrete outcome intervals
Given discrete numerical outcomes x_1..x_k in A2:A6 and their associated probabilities p_1..p_k in B2:B6, Excel’s PROB(x_range, prob_range, lower_limit, [upper_limit]) sums p_i for all outcomes satisfying lower_limit <= x_i <= upper_limit — provided every p_i >= 0 and SUM(p_i) = 1.
Building the check explicitly with IF(OR(MIN(B2:B6) < 0, ABS(SUM(B2:B6) - 1) > 0.000001), "Invalid Probability Mass", SUMIFS(B2:B6, A2:A6, ">=" & D2, A2:A6, "<=" & D3)) enforces Kolmogorov’s normalization axiom cleanly while pairing naturally with expected value E[X] = SUMPRODUCT(A2:A6, B2:B6) and standard deviation SQRT(SUMPRODUCT(B2:B6, (A2:A6 - mu)^2)).
| Daily Claims (x) | Probability p(x) | Weighted x * p(x) | In Window [1, 3]? |
|---|---|---|---|
| 0 claims | 0.25 | 0.00 | Excluded (< 1) |
| 1 claim | 0.35 | 0.35 | Included (0.35) |
| 2 claims | 0.20 | 0.40 | Included (+0.20 = 0.55) |
| 3 claims | 0.15 | 0.45 | Included (+0.15 = 0.70) |
| 4 claims | 0.05 | 0.20 | Excluded (> 3) | Mean = 1.40 |
Axiom-Guarded Interval Probability P(Lower <= X <= Upper)
Verifies non-negative weights summing to 1.0 before summing probabilities between D2 and D3.
=IF(OR(MIN(B2:B6) < 0, ABS(SUM(B2:B6) - 1) > 0.000001), "Invalid Probability Mass", ROUND(SUMIFS(B2:B6, A2:A6, ">=" & D2, A2:A6, "<=" & D3), 6))Discrete Expected Value E[X] and Standard Deviation
Computes the probability-weighted mean and exact discrete standard deviation.
=LET(mu, SUMPRODUCT(A2:A6, B2:B6), ROUND(SQRT(SUMPRODUCT(B2:B6, (A2:A6 - mu) ^ 2)), 4))Try the example
Use the original values or change them to test your understanding.