Statistical Analysis
Standard Normal Cumulative Probability, GAUSS Band & Inverse Quantile (NORM.S.DIST & NORM.INV)
Compute left-tail normal probabilities Phi(z), center-to-z GAUSS intervals Phi(z) - 0.5, and inverse normal quantile cutoffs x = mu + z_p * sigma using portable rational polynomials.
Connecting cumulative Phi(z), GAUSS(z), and inverse probit cutoffs
Whereas the Gaussian bell-curve density ordinate measures height at z, cumulative probability Phi(z) integrates area from -infinity up to z, and GAUSS(z) = Phi(z) - 0.5 isolates the probability mass between the mean (0) and z.
Using Abramowitz and Stegun's 5-term rational polynomial (26.2.17, maximum absolute error 7.5e-8) for Phi(z) alongside the rational probit quantile approximation (26.2.23) for Phi^-1(p) makes SLA service-level and safety-stock sheets portable across Excel and Arte Sheets.
| Metric | Input | Formula Relationship | Value |
|---|---|---|---|
| Cumulative Phi(z) | z = 1.96 | 1 - phi(z) * P5(t) | 0.9750 |
| GAUSS(z) (0 to z) | z = 1.96 | Phi(z) - 0.5 | 0.4750 |
| Standard Probit z_p | p = 0.975 | tau - R(tau) | 1.960 |
| Scaled Cutoff x_p | mu=100, sd=15, p=0.975 | mu + sd * z_p | 129.40 |
Cumulative Normal Probability Phi(z)
Evaluate poly_t = 1 / (1 + 0.2316419 * |z|), multiply by the standard normal ordinate EXP(-0.5 * z^2) / SQRT(2 * PI()), and reflect across 0.5 when z < 0.
=LET(z_val, B2, z_abs, ABS(z_val), poly_t, 1 / (1 + 0.2316419 * z_abs), pdf_z, EXP(-0.5 * z_abs ^ 2) / SQRT(2 * PI()), tail_p, pdf_z * poly_t * (0.31938153 + poly_t * (-0.356563782 + poly_t * (1.781477937 + poly_t * (-1.821255978 + 1.330274429 * poly_t)))), ROUND(IF(z_val >= 0, 1 - tail_p, tail_p), 4))Inverse Normal Quantile Cutoff (NORM.S.INV & NORM.INV)
Transform tail probability p_low = MIN(p, 1 - p) via tau = SQRT(-2 * LN(p_low)), apply the rational correction, and scale by mu + sigma * z_p.
=LET(prob, B5, p_low, IF(prob <= 0.5, prob, 1 - prob), tau, SQRT(-2 * LN(p_low)), z_mag, tau - (2.515517 + 0.802853 * tau + 0.010328 * tau ^ 2) / (1 + 1.432788 * tau + 0.189269 * tau ^ 2 + 0.001308 * tau ^ 3), IF(prob = 0.5, 0, ROUND(IF(prob < 0.5, -z_mag, z_mag), 3)))Try the example
Use the original values or change them to test your understanding.