Repository Public
1"""Generates the scipy / numpy / mpmath / decimal reference fixtures (see common.py).23These references are independent of any spreadsheet: distributions come from scipy.stats, special4functions from scipy.special and mpmath, descriptive statistics and regressions from numpy and5statsmodels, unit conversions from scipy.constants and decimal rounding from Python's decimal module.6"""7import cmath8import itertools9import math10import random11from decimal import ROUND_DOWN, ROUND_HALF_UP, ROUND_UP, Decimal1213import mpmath14import numpy as np15import scipy.constants as K16import scipy.special as sp17import scipy.stats as st18import statsmodels.api as sm1920from common import write_fixture21from data import DUPS, GROW, POS, X, X2, Y, data_sheet2223D = data_sheet()24mpmath.mp.dps = 40252627def lit(v):28 if isinstance(v, bool):29 return "TRUE" if v else "FALSE"30 if isinstance(v, str):31 return '"' + v + '"'32 if isinstance(v, int):33 return str(v)34 return repr(float(v))353637def case(name, args, expected, tol=1e-12, abs_tol=None):38 e = float(expected)39 if not math.isfinite(e):40 return None41 c = {"f": f"={name}({','.join(lit(a) for a in args)})", "e": e, "kind": "num", "tol": tol}42 if abs_tol is not None:43 c["abs"] = abs_tol44 return c454647def M(v):48 return mpmath.mpf(v)495051def refine(f, target, x0):52 """Quantile reference at 40 digits. scipy's own quantile functions are only accurate to about53 1e-8 in some tails, so its estimate is checked against the 40-digit CDF and, when the residual is54 not negligible, polished with a root finder."""55 if x0 == 0 or not math.isfinite(x0):56 return x057 try:58 x = M(x0)59 fx = f(x) - target60 if abs(fx) <= min(abs(target), abs(1 - target)) * M(10) ** -15:61 return float(x)62 return float(mpmath.findroot(lambda v: f(v) - target, (x, x * (1 + M(10) ** -9)), tol=M(10) ** -30, maxsteps=40))63 except Exception:64 return float("nan")656667def t_two(t, df):68 return mpmath.betainc(M(df) / 2, M(1) / 2, 0, M(df) / (df + t * t), regularized=True)697071def t_inv2(p, df):72 return refine(lambda t: t_two(t, df), M(p), st.t.isf(p / 2, df))737475def t_inv(p, df):76 if p == 0.5:77 return 0.078 if p < 0.5:79 return -refine(lambda t: t_two(t, df), 2 * M(p), st.t.isf(p, df))80 return refine(lambda t: t_two(t, df), 2 * (1 - M(p)), st.t.ppf(p, df))818283def chi_cdf(x, df):84 return mpmath.gammainc(M(df) / 2, 0, x / 2, regularized=True)858687def chi_sf(x, df):88 return mpmath.gammainc(M(df) / 2, x / 2, mpmath.inf, regularized=True)899091def f_cdf(x, d1, d2):92 return mpmath.betainc(M(d1) / 2, M(d2) / 2, 0, d1 * x / (d1 * x + d2), regularized=True)939495def f_sf(x, d1, d2):96 return mpmath.betainc(M(d2) / 2, M(d1) / 2, 0, d2 / (d2 + d1 * x), regularized=True)979899def pois_pmf(k, lam):100 return float(mpmath.exp(-M(lam) + k * mpmath.log(M(lam)) - mpmath.loggamma(k + 1)))101102103def binom_pmf(k, n, p):104 return float(mpmath.binomial(n, k) * M(p) ** k * (1 - M(p)) ** (n - k))105106107def collect(items):108 return [c for c in items if c is not None]109110111def distributions():112 c = []113 zs = [-8.5, -5, -3.2, -1.96, -1, -0.3, 0, 1e-8, 0.5, 1, 1.6448536269514722, 2.5, 4, 6.5, 9]114 for z in zs:115 c += [case("NORM.S.DIST", (z, True), st.norm.cdf(z)), case("NORM.S.DIST", (z, False), st.norm.pdf(z)), case("NORMSDIST", (z,), st.norm.cdf(z)),116 case("PHI", (z,), st.norm.pdf(z)), case("GAUSS", (z,), float(mpmath.erf(mpmath.mpf(z) / mpmath.sqrt(2)) / 2), abs_tol=1e-300),117 case("NORM.DIST", (z, 1.5, 2.25, True), st.norm.cdf(z, 1.5, 2.25)), case("NORM.DIST", (z, 1.5, 2.25, False), st.norm.pdf(z, 1.5, 2.25)),118 case("NORMDIST", (z, -3, 0.5, True), st.norm.cdf(z, -3, 0.5))]119 ps = [1e-300, 1e-100, 1e-20, 1e-10, 1e-5, 0.001, 0.01, 0.02425, 0.025, 0.1, 0.25, 0.4, 0.5, 0.6, 0.75, 0.9, 0.975, 0.97575, 0.99, 0.999, 0.99999, 1 - 1e-10]120 for p in ps:121 with mpmath.workdps(400):122 ref = float(mpmath.sqrt(2) * mpmath.erfinv(2 * M(p) - 1))123 c += [case("NORM.S.INV", (p,), ref, abs_tol=1e-16), case("NORMSINV", (p,), ref, abs_tol=1e-16),124 case("NORM.INV", (p, 10, 2.5), 10 + 2.5 * ref, tol=1e-12, abs_tol=1e-13), case("NORMINV", (p, -1, 0.1), -1 + 0.1 * ref, tol=1e-12, abs_tol=1e-13),125 case("LOGNORM.INV", (p, 0.5, 0.75), math.exp(0.5 + 0.75 * ref)), case("LOGINV", (p, 0.5, 0.75), math.exp(0.5 + 0.75 * ref))]126 for x in [1e-6, 0.01, 0.5, 1, 2.5, 10, 150, 1e6]:127 for m, s in [(0, 1), (0.5, 0.75), (3.5, 1.2)]:128 d = st.lognorm(s, scale=math.exp(m))129 c += [case("LOGNORM.DIST", (x, m, s, True), d.cdf(x)), case("LOGNORM.DIST", (x, m, s, False), d.pdf(x)), case("LOGNORMDIST", (x, m, s), d.cdf(x))]130 # Student t131 for df in [1, 2, 3, 5, 10, 30, 100, 1000]:132 for x in [-40, -6, -2.5, -1, -0.1, 0, 0.3, 1, 1.96, 3.5, 8, 60]:133 c += [case("T.DIST", (x, df, True), st.t.cdf(x, df)), case("T.DIST", (x, df, False), st.t.pdf(x, df)), case("T.DIST.RT", (x, df), st.t.sf(x, df))]134 if x >= 0:135 c += [case("T.DIST.2T", (x, df), 2 * st.t.sf(x, df)), case("TDIST", (x, df, 1), st.t.sf(x, df)), case("TDIST", (x, df, 2), 2 * st.t.sf(x, df))]136 for p in [1e-12, 1e-6, 0.001, 0.025, 0.1, 0.3, 0.5, 0.7, 0.9, 0.975, 0.999, 0.999999]:137 c += [case("T.INV", (p, df), t_inv(p, df), abs_tol=1e-15), case("T.INV.2T", (p, df), t_inv2(p, df)), case("TINV", (p, df), t_inv2(p, df))]138 # chi-squared139 for df in [1, 2, 3, 4, 7, 10, 25, 100, 500]:140 for x in [1e-8, 0.01, 0.5, 1, 2, 3.84, 7.5, 18.307, 40, 120, 600]:141 c += [case("CHISQ.DIST", (x, df, True), st.chi2.cdf(x, df)), case("CHISQ.DIST", (x, df, False), st.chi2.pdf(x, df)),142 case("CHISQ.DIST.RT", (x, df), st.chi2.sf(x, df)), case("CHIDIST", (x, df), st.chi2.sf(x, df))]143 for p in [1e-10, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999, 1 - 1e-9]:144 lo, hi = refine(lambda x: chi_cdf(x, df), M(p), st.chi2.ppf(p, df)), refine(lambda x: chi_sf(x, df), M(p), st.chi2.isf(p, df))145 c += [case("CHISQ.INV", (p, df), lo), case("CHISQ.INV.RT", (p, df), hi), case("CHIINV", (p, df), hi)]146 # F147 for d1, d2 in [(1, 1), (1, 5), (2, 2), (3, 7), (6, 4), (10, 20), (25, 3), (100, 100), (2, 500)]:148 for x in [1e-6, 0.01, 0.2, 0.5, 1, 1.5, 3, 15.2069, 50, 400]:149 c += [case("F.DIST", (x, d1, d2, True), st.f.cdf(x, d1, d2)), case("F.DIST", (x, d1, d2, False), st.f.pdf(x, d1, d2)),150 case("F.DIST.RT", (x, d1, d2), st.f.sf(x, d1, d2)), case("FDIST", (x, d1, d2), st.f.sf(x, d1, d2))]151 for p in [1e-8, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999]:152 lo, hi = refine(lambda x: f_cdf(x, d1, d2), M(p), st.f.ppf(p, d1, d2)), refine(lambda x: f_sf(x, d1, d2), M(p), st.f.isf(p, d1, d2))153 c += [case("F.INV", (p, d1, d2), lo), case("F.INV.RT", (p, d1, d2), hi), case("FINV", (p, d1, d2), hi)]154 # exponential, gamma, beta, Weibull155 for lam in [0.1, 1, 2.5, 40]:156 for x in [0, 1e-9, 0.05, 0.5, 1, 3, 20]:157 d = st.expon(scale=1 / lam)158 c += [case("EXPON.DIST", (x, lam, True), d.cdf(x)), case("EXPON.DIST", (x, lam, False), d.pdf(x)), case("EXPONDIST", (x, lam, True), d.cdf(x))]159 for a, b in [(0.5, 1), (1, 2), (2.5, 0.5), (9, 2), (30, 1.5), (150, 0.1), (0.05, 3)]:160 d = st.gamma(a, scale=b)161 for x in [1e-9, 0.01, 0.3, 1, 2.5, 10, 18, 45, 100]:162 c += [case("GAMMA.DIST", (x, a, b, True), d.cdf(x)), case("GAMMA.DIST", (x, a, b, False), d.pdf(x)), case("GAMMADIST", (x, a, b, True), d.cdf(x))]163 for p in [1e-9, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999, 1 - 1e-9]:164 q = refine(lambda x: mpmath.gammainc(M(a), 0, x / M(b), regularized=True), M(p), d.ppf(p))165 c += [case("GAMMA.INV", (p, a, b), q), case("GAMMAINV", (p, a, b), q)]166 for a, b in [(0.5, 0.5), (1, 1), (2, 3), (8, 10), (0.3, 4), (50, 20), (1, 6), (120, 150)]:167 d = st.beta(a, b)168 for x in [1e-9, 0.001, 0.1, 0.25, 0.4, 0.5, 0.7, 0.9, 0.999]:169 c += [case("BETA.DIST", (x, a, b, True), d.cdf(x)), case("BETA.DIST", (x, a, b, False), d.pdf(x)), case("BETADIST", (x, a, b), d.cdf(x))]170 d2 = st.beta(a, b, loc=1, scale=2)171 c += [case("BETA.DIST", (1 + 2 * x, a, b, True, 1, 3), d2.cdf(1 + 2 * x)), case("BETA.DIST", (1 + 2 * x, a, b, False, 1, 3), d2.pdf(1 + 2 * x)),172 case("BETADIST", (1 + 2 * x, a, b, 1, 3), d2.cdf(1 + 2 * x))]173 for p in [1e-9, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999]:174 q = refine(lambda x: mpmath.betainc(M(a), M(b), 0, x, regularized=True), M(p), d.ppf(p))175 c += [case("BETA.INV", (p, a, b), q), case("BETAINV", (p, a, b), q), case("BETA.INV", (p, a, b, -2, 5), -2 + 7 * q, abs_tol=1e-14)]176 for a, b in [(0.5, 1), (1, 1), (1.5, 3), (20, 100), (3, 0.2)]:177 d = st.weibull_min(a, scale=b)178 for x in [1e-6, 0.05, 0.5, 1, 2, 5, 105]:179 c += [case("WEIBULL.DIST", (x, a, b, True), d.cdf(x), abs_tol=1e-300), case("WEIBULL.DIST", (x, a, b, False), d.pdf(x), abs_tol=1e-300),180 case("WEIBULL", (x, a, b, True), d.cdf(x), abs_tol=1e-300)]181 return collect(c)182183184def discrete():185 c = []186 for n, p in [(1, 0.5), (10, 0.5), (10, 0.1), (20, 0.9), (100, 0.3), (1000, 0.01), (1000, 0.5), (5000, 0.999), (60, 0.75), (15, 1e-6)]:187 ks = sorted(set([0, 1, 2, n // 4, n // 2, int(n * p), int(n * p) + 1, n - 1, n]))188 for k in ks:189 if k > n:190 continue191 c += [case("BINOM.DIST", (k, n, p, False), binom_pmf(k, n, p), abs_tol=1e-300), case("BINOM.DIST", (k, n, p, True), st.binom.cdf(k, n, p), abs_tol=1e-300),192 case("BINOMDIST", (k, n, p, False), binom_pmf(k, n, p), abs_tol=1e-300)]193 for alpha in [0.001, 0.05, 0.25, 0.5, 0.75, 0.95, 0.999]:194 c += [case("BINOM.INV", (n, p, alpha), st.binom.ppf(alpha, n, p)), case("CRITBINOM", (n, p, alpha), st.binom.ppf(alpha, n, p))]195 lo, hi = max(0, int(n * p) - 2), min(n, int(n * p) + 3)196 c += [case("BINOM.DIST.RANGE", (n, p, lo, hi), float(sum(M(binom_pmf(k, n, p)) for k in range(lo, hi + 1)))),197 case("BINOM.DIST.RANGE", (n, p, lo), binom_pmf(lo, n, p), abs_tol=1e-300)]198 for mean in [0.01, 0.5, 1, 4.5, 20, 150, 2500]:199 for x in sorted(set([0, 1, 2, 5, int(mean), int(mean) + 1, int(mean * 1.5) + 3, int(mean * 3) + 10])):200 c += [case("POISSON.DIST", (x, mean, False), pois_pmf(x, mean), abs_tol=1e-300), case("POISSON.DIST", (x, mean, True), st.poisson.cdf(x, mean)),201 case("POISSON", (x, mean, True), st.poisson.cdf(x, mean))]202 for n, m, N in [(4, 8, 20), (30, 25, 100), (10, 50, 60), (500, 300, 1000), (5, 5, 5), (1, 1, 2), (50, 5000, 100000)]:203 lo, hi = max(0, n - N + m), min(n, m)204 for s in sorted(set([lo, lo + 1, (lo + hi) // 2, hi - 1, hi])):205 if lo <= s <= hi:206 d = st.hypergeom(N, m, n)207 c += [case("HYPGEOM.DIST", (s, n, m, N, False), d.pmf(s), tol=1e-11, abs_tol=1e-300), case("HYPGEOM.DIST", (s, n, m, N, True), d.cdf(s), tol=1e-11),208 case("HYPGEOMDIST", (s, n, m, N), d.pmf(s), tol=1e-11, abs_tol=1e-300)]209 for s, p in [(1, 0.5), (5, 0.25), (2, 0.7), (40, 0.9), (3, 0.01), (100, 0.5)]:210 for f in [0, 1, 3, 10, 45, 200]:211 d = st.nbinom(s, p)212 c += [case("NEGBINOM.DIST", (f, s, p, False), d.pmf(f), abs_tol=1e-300), case("NEGBINOM.DIST", (f, s, p, True), d.cdf(f)),213 case("NEGBINOMDIST", (f, s, p), d.pmf(f), abs_tol=1e-300)]214 return collect(c)215216217def special():218 c = []219 for x in [0.001, 0.5, 1, 1.5, 2.5, 5, 10.3, 50, 100, 170.5, -0.5, -1.5, -3.75, -10.2, 1e-10]:220 c.append(case("GAMMA", (x,), float(mpmath.gamma(x)), tol=4e-13))221 for x in [1e-10, 0.001, 0.5, 1, 2, 4.5, 10, 100, 1e5, 1e10]:222 ref = float(mpmath.loggamma(x))223 c += [case("GAMMALN", (x,), ref, tol=2e-13, abs_tol=1e-15), case("GAMMALN.PRECISE", (x,), ref, tol=2e-13, abs_tol=1e-15)]224 for x in [-6, -2.5, -1, -0.1, -1e-10, 0, 1e-12, 0.05, 0.5, 1, 1.5, 3, 5.5, 10, 27]:225 c += [case("ERF", (x,), float(mpmath.erf(x)), abs_tol=1e-300), case("ERF.PRECISE", (x,), float(mpmath.erf(x)), abs_tol=1e-300),226 case("ERFC", (x,), float(mpmath.erfc(x)), abs_tol=1e-300), case("ERFC.PRECISE", (x,), float(mpmath.erfc(x)), abs_tol=1e-300),227 case("ERF", (x, 1.25), float(mpmath.erf(1.25) - mpmath.erf(x)), tol=1e-12, abs_tol=1e-15)]228 for n in [0, 1, 2, 3, 5, 10, 25]:229 for x in [0.001, 0.1, 0.5, 1, 1.9, 2, 2.5, 5, 9.75, 20, 50, 100]:230 c += [case("BESSELJ", (x, n), float(mpmath.besselj(n, x)), tol=2e-11, abs_tol=1e-15), case("BESSELY", (x, n), float(mpmath.bessely(n, x)), tol=2e-11, abs_tol=1e-15),231 case("BESSELI", (x, n), float(mpmath.besseli(n, x)), tol=1e-12, abs_tol=1e-300), case("BESSELK", (x, n), float(mpmath.besselk(n, x)), tol=1e-12, abs_tol=1e-300)]232 for x in [-0.5, -3, -20]:233 c += [case("BESSELJ", (x, n), float(mpmath.besselj(n, x)), tol=2e-11, abs_tol=1e-15), case("BESSELI", (x, n), float(mpmath.besseli(n, x)), tol=1e-12, abs_tol=1e-300)]234 for x in [0.5, 1.75, 3]:235 c += [case("BESSELJ", (x, 2.9), float(mpmath.besselj(2, x)), tol=1e-11), case("BESSELK", (x, 1.5), float(mpmath.besselk(1, x)))]236 # trigonometry and logarithms at 40 digits237 fns = {238 "SIN": mpmath.sin, "COS": mpmath.cos, "TAN": mpmath.tan, "SINH": mpmath.sinh, "COSH": mpmath.cosh, "TANH": mpmath.tanh, "ASINH": mpmath.asinh,239 "ATAN": mpmath.atan, "SEC": mpmath.sec, "CSC": mpmath.csc, "COT": mpmath.cot, "ACOT": lambda x: mpmath.pi / 2 - mpmath.atan(x),240 "SECH": mpmath.sech, "CSCH": mpmath.csch, "COTH": mpmath.coth, "EXP": mpmath.exp, "DEGREES": mpmath.degrees, "RADIANS": mpmath.radians,241 }242 for name, fn in fns.items():243 for x in [-7.3, -1.2, -0.004, 0.31, 0.9, 1.7, 4.4, 12.6, 33.1]:244 c.append(case(name, (x,), float(fn(mpmath.mpf(x))), tol=4e-15 if name not in ("TAN", "SEC", "CSC", "COT") else 1e-13))245 for x in [1e-9, 0.02, 0.5, 0.999, 1.0001, 2, 17.5, 1e6, 1e300]:246 c += [case("LN", (x,), float(mpmath.log(x)), tol=4e-15, abs_tol=1e-18), case("LOG10", (x,), float(mpmath.log10(x)), tol=4e-15, abs_tol=1e-18),247 case("LOG", (x, 7), float(mpmath.log(x, 7)), tol=1e-14, abs_tol=1e-17), case("SQRT", (x,), float(mpmath.sqrt(x)), tol=4e-16),248 case("SQRTPI", (x,), float(mpmath.sqrt(mpmath.mpf(x) * mpmath.pi)), tol=1e-15), case("ACOSH", (x + 1,), float(mpmath.acosh(M(x + 1))), tol=1e-14, abs_tol=1e-17),249 case("ACOTH", (x + 1.5,), float(mpmath.acoth(M(x + 1.5))), tol=1e-13)]250 for x in [-0.999, -0.5, -1e-5, 0, 0.123, 0.75, 0.9999]:251 c += [case("ASIN", (x,), float(mpmath.asin(x)), tol=1e-15, abs_tol=1e-300), case("ACOS", (x,), float(mpmath.acos(x)), tol=1e-15),252 case("ATANH", (x,), float(mpmath.atanh(x)), tol=1e-14, abs_tol=1e-300), case("FISHER", (x,), float(mpmath.atanh(x)), tol=1e-14, abs_tol=1e-300),253 case("FISHERINV", (x * 5,), float(mpmath.tanh(mpmath.mpf(x) * 5)), tol=1e-15, abs_tol=1e-300)]254 for n in [0, 1, 5, 10, 20, 50, 100, 170]:255 c.append(case("FACT", (n,), float(mpmath.factorial(n)), tol=1e-15))256 c.append(case("FACTDOUBLE", (n,), float(mpmath.fac2(n)), tol=1e-14))257 for n, k in [(10, 3), (52, 5), (100, 50), (200, 100), (1000, 3), (40, 20), (60, 30), (1029, 514)]:258 c += [case("COMBIN", (n, k), float(mpmath.binomial(n, k)), tol=1e-13), case("COMBINA", (n, k), float(mpmath.binomial(n + k - 1, k)), tol=1e-13)]259 if k <= 60:260 c += [case("PERMUT", (n, k), float(mpmath.factorial(n) / mpmath.factorial(n - k)), tol=1e-13), case("PERMUTATIONA", (n, k), float(mpmath.mpf(n) ** k), tol=1e-13)]261 return collect(c)262263264def rounding():265 """Excel rounds the 15-significant-digit decimal reading of a number, half away from zero."""266 rnd = random.Random(20261121)267 values = [2.5, -2.5, 1.005, 0.285, 2.675, 1.115, 1.125, 0.615, 8.345, 5.015, 1.0049999999999999, 0.1 + 0.2, 1.15, 2.345, 1e-9, 0.5, 1.5, -0.5, 1234.5, 99.995,268 0.045, 1.255, 10.235, 3.0000000000000004, 0.499999999999999, 123456789.125, 4.35, 1.45, 7.0745, 0.00035, 1e21, 1e-300, 0.9999999999999999]269 for _ in range(60):270 mag = rnd.choice([1, 10, 100, 1000, 1e6, 1e-3])271 base = round(rnd.uniform(-1, 1) * mag, rnd.randint(0, 6))272 values.append(base)273 values.append(base + rnd.choice([0.5, 0.05, 0.005, 0.0005]) * rnd.choice([1, -1]))274 c = []275 for v in values:276 d15 = Decimal("%.15g" % v)277 for digits in [0, 1, 2, 3, -1, -2, 5]:278 q = Decimal(1).scaleb(-digits)279 for name, mode in [("ROUND", ROUND_HALF_UP), ("ROUNDUP", ROUND_UP), ("ROUNDDOWN", ROUND_DOWN), ("TRUNC", ROUND_DOWN)]:280 expected = float(d15.quantize(q, rounding=mode)) if abs(v) < 1e15 else v281 c.append({"f": f"={name}({v!r},{digits})", "e": expected, "kind": "num", "tol": 0.0})282 for n, d in [(7, 3), (-7, 3), (7, -3), (-7, -3), (5.5, 2), (-5.5, 2), (1e15, 7), (2 ** 52 + 1, 2), (123456789012, 97), (-1, 1e10), (17.25, -4.5)]:283 c.append({"f": f"=MOD({n!r},{d!r})", "e": float(n - d * math.floor(Decimal(n) / Decimal(d))), "kind": "num", "tol": 1e-15})284 c.append({"f": f"=QUOTIENT({n!r},{d!r})", "e": float(math.trunc(Decimal(n) / Decimal(d))), "kind": "num", "tol": 0.0})285 return c286287288def descriptive():289 c = []290 sets = {"D!A1:A12": X, "D!B1:B12": Y, "D!E1:E12": DUPS, "D!F1:F12": POS, "D!L1:L12": GROW, "D!K1:K12": X2}291 for ref, data in sets.items():292 a = np.array(data, dtype=float)293 n = len(a)294 c += [case("VAR", (), 0)]295 c.pop()296 def f(name, value, tol=1e-13):297 return {"f": f"={name}({ref})", "e": float(value), "kind": "num", "tol": tol}298 c += [f("VAR.S", a.var(ddof=1)), f("VAR", a.var(ddof=1)), f("VAR.P", a.var()), f("VARP", a.var()), f("STDEV.S", a.std(ddof=1)), f("STDEV", a.std(ddof=1)),299 f("STDEV.P", a.std()), f("STDEVP", a.std()), f("MEDIAN", np.median(a)), f("AVEDEV", np.mean(np.abs(a - a.mean()))), f("DEVSQ", ((a - a.mean()) ** 2).sum()),300 dict(f("SKEW", st.skew(a, bias=False)), abs=1e-14), dict(f("SKEW.P", st.skew(a, bias=True)), abs=1e-14), f("KURT", st.kurtosis(a, bias=False)), f("SUMSQ", (a * a).sum()),301 f("AVERAGEA", a.mean()), f("VARA", a.var(ddof=1)), f("STDEVPA", a.std()), f("PRODUCT", float(mpmath.fprod(data)), 1e-13)]302 if (a > 0).all():303 c += [f("GEOMEAN", st.gmean(a)), f("HARMEAN", st.hmean(a))]304 for k in [0, 0.05, 0.1, 0.25, 1 / 3, 0.5, 0.62, 0.75, 0.9, 0.99, 1]:305 c.append({"f": f"=PERCENTILE.INC({ref},{k!r})", "e": float(np.percentile(a, k * 100, method="linear")), "kind": "num", "tol": 1e-13})306 c.append({"f": f"=PERCENTILE({ref},{k!r})", "e": float(np.percentile(a, k * 100, method="linear")), "kind": "num", "tol": 1e-13})307 if 1 / (n + 1) <= k <= n / (n + 1):308 c.append({"f": f"=PERCENTILE.EXC({ref},{k!r})", "e": float(np.percentile(a, k * 100, method="weibull")), "kind": "num", "tol": 1e-13})309 for q in [0, 1, 2, 3, 4]:310 c.append({"f": f"=QUARTILE.INC({ref},{q})", "e": float(np.percentile(a, q * 25, method="linear")), "kind": "num", "tol": 1e-13})311 if 1 <= q <= 3:312 c.append({"f": f"=QUARTILE.EXC({ref},{q})", "e": float(np.percentile(a, q * 25, method="weibull")), "kind": "num", "tol": 1e-13})313 for p in [0.1, 0.2, 0.34, 0.5]:314 c.append({"f": f"=TRIMMEAN({ref},{p})", "e": float(st.trim_mean(a, int(n * p / 2) / n)), "kind": "num", "tol": 1e-13})315 for x in sorted(set(data))[:6]:316 c.append({"f": f"=RANK.EQ({x!r},{ref})", "e": float(st.rankdata(-a, method="min")[data.index(x)]), "kind": "num", "tol": 0.0})317 c.append({"f": f"=RANK.AVG({x!r},{ref},1)", "e": float(st.rankdata(a, method="average")[data.index(x)]), "kind": "num", "tol": 0.0})318 c.append({"f": f"=PERCENTRANK.INC({ref},{x!r},12)", "e": math.floor((st.rankdata(a, method="min")[data.index(x)] - 1) / (n - 1) * 1e12 + 1e-6) / 1e12, "kind": "num", "tol": 1e-11})319 pairs = [("D!B1:B12", Y, "D!A1:A12", X), ("D!F1:F12", POS, "D!K1:K12", X2), ("D!L1:L12", GROW, "D!F1:F12", POS), ("D!E1:E12", DUPS, "D!B1:B12", Y)]320 for ry, y, rx, x in pairs:321 y, x = np.array(y, dtype=float), np.array(x, dtype=float)322 lr = st.linregress(x, y)323 n = len(x)324 resid = y - (lr.intercept + lr.slope * x)325326 def g(name, value, args=None, tol=1e-12):327 return {"f": f"={name}({args or (ry + ',' + rx)})", "e": float(value), "kind": "num", "tol": tol}328 c += [g("CORREL", lr.rvalue), g("PEARSON", lr.rvalue), g("RSQ", lr.rvalue ** 2), g("SLOPE", lr.slope), g("INTERCEPT", lr.intercept),329 g("COVARIANCE.P", np.cov(x, y, bias=True)[0, 1]), g("COVAR", np.cov(x, y, bias=True)[0, 1]), g("COVARIANCE.S", np.cov(x, y)[0, 1]),330 g("STEYX", math.sqrt((resid ** 2).sum() / (n - 2))), g("FORECAST", lr.intercept + lr.slope * 6.5, f"6.5,{ry},{rx}"),331 g("FORECAST.LINEAR", lr.intercept - lr.slope * 2, f"-2,{ry},{rx}"), g("SUMPRODUCT", (x * y).sum()), g("SUMX2MY2", (y * y - x * x).sum()),332 g("SUMX2PY2", (y * y + x * x).sum()), g("SUMXMY2", ((y - x) ** 2).sum())]333 # tests334 for tails in [1, 2]:335 c += [g("T.TEST", st.ttest_rel(y, x).pvalue / (2 / tails), f"{ry},{rx},{tails},1"), g("T.TEST", st.ttest_ind(y, x).pvalue / (2 / tails), f"{ry},{rx},{tails},2"),336 g("T.TEST", st.ttest_ind(y, x, equal_var=False).pvalue / (2 / tails), f"{ry},{rx},{tails},3"), g("TTEST", st.ttest_ind(y, x).pvalue / (2 / tails), f"{ry},{rx},{tails},2")]337 fstat = y.var(ddof=1) / x.var(ddof=1)338 pf = 2 * min(st.f.cdf(fstat, n - 1, n - 1), st.f.sf(fstat, n - 1, n - 1))339 c += [g("F.TEST", pf), g("FTEST", pf)]340 for mu, sigma in [(4, None), (6, 3), (8, 2.2)]:341 s = sigma if sigma else x.std(ddof=1)342 p = st.norm.sf((x.mean() - mu) / (s / math.sqrt(n)))343 c.append(g("Z.TEST", p, f"{rx},{mu}" + (f",{sigma}" if sigma else "")))344 for alpha, sd, n in [(0.05, 2.5, 50), (0.1, 1, 2), (0.01, 12.5, 1000), (0.5, 0.3, 7), (1e-6, 1, 30)]:345 c += [case("CONFIDENCE.NORM", (alpha, sd, n), st.norm.isf(alpha / 2) * sd / math.sqrt(n)), case("CONFIDENCE", (alpha, sd, n), st.norm.isf(alpha / 2) * sd / math.sqrt(n)),346 case("CONFIDENCE.T", (alpha, sd, n), st.t.isf(alpha / 2, n - 1) * sd / math.sqrt(n))]347 obs = np.array([[58, 35], [11, 25], [10, 23]], dtype=float)348 exp = np.array([[45.35, 47.65], [17.56, 18.44], [16.09, 16.91]])349 chi = ((obs - exp) ** 2 / exp).sum()350 c += [{"f": "=CHISQ.TEST(D!AK1:AL3,D!AM1:AN3)", "e": float(st.chi2.sf(chi, 2)), "kind": "num", "tol": 1e-12},351 {"f": "=CHITEST(D!AK1:AL3,D!AM1:AN3)", "e": float(st.chi2.sf(chi, 2)), "kind": "num", "tol": 1e-12},352 {"f": "=CHISQ.TEST(D!AK1:AK3,D!AM1:AM3)", "e": float(st.chi2.sf(((obs[:, 0] - exp[:, 0]) ** 2 / exp[:, 0]).sum(), 2)), "kind": "num", "tol": 1e-12}]353 return collect(c)354355356def regression():357 """LINEST / LOGEST / TREND / GROWTH against statsmodels OLS."""358 c = []359 y, x1, x2, grow, pos = (np.array(v, dtype=float) for v in (Y, X, X2, GROW, POS))360361 def block(prefix, model_y, cols, const, exp_coef):362 xm = np.column_stack(cols)363 design = sm.add_constant(xm) if const else xm364 res = sm.OLS(model_y, design).fit()365 k = xm.shape[1]366 params = list(res.params[1:] if const else res.params)367 bse = list(res.bse[1:] if const else res.bse)368 out = []369 for j in range(k):370 coef = params[k - 1 - j]371 out.append((1, j + 1, math.exp(coef) if exp_coef else coef))372 out.append((2, j + 1, bse[k - 1 - j]))373 if const:374 out.append((1, k + 1, math.exp(res.params[0]) if exp_coef else res.params[0]))375 out.append((2, k + 1, res.bse[0]))376 else:377 out.append((1, k + 1, 1.0 if exp_coef else 0.0))378 ss_res = float(res.ssr)379 ss_tot = float(((model_y - model_y.mean()) ** 2).sum()) if const else float((model_y ** 2).sum())380 df = len(model_y) - k - (1 if const else 0)381 out += [(3, 1, 1 - ss_res / ss_tot), (3, 2, math.sqrt(ss_res / df)), (4, 1, ((ss_tot - ss_res) / k) / (ss_res / df)), (4, 2, df), (5, 1, ss_tot - ss_res), (5, 2, ss_res)]382 return [{"f": f"=INDEX({prefix},{r},{col})", "e": float(v), "kind": "num", "tol": 1e-9, "abs": 1e-12} for r, col, v in out]383384 for const in [True, False]:385 flag = "TRUE" if const else "FALSE"386 c += block(f"LINEST(D!B1:B12,D!A1:A12,{flag},TRUE)", y, [x1], const, False)387 c += block(f"LINEST(D!B1:B12,D!J20:K31,{flag},TRUE)", y, [x1, x2], const, False)388 c += block(f"LINEST(D!F1:F12,D!K1:K12,{flag},TRUE)", pos, [x2], const, False)389 c += block(f"LOGEST(D!L1:L12,D!F1:F12,{flag},TRUE)", np.log(grow), [pos], const, True)390 c += block(f"LOGEST(D!L1:L12,D!J20:K31,{flag},TRUE)", np.log(grow), [x1, x2], const, True)391 res = sm.OLS(y, sm.add_constant(np.column_stack([x1, x2]))).fit()392 for i, (a, b) in enumerate([(1, 2), (3, 4), (5, 6)]):393 c.append({"f": f"=INDEX(TREND(D!B1:B12,D!J20:K31,{{1,2;3,4;5,6}}),{i + 1})", "e": float(res.params[0] + res.params[1] * a + res.params[2] * b), "kind": "num", "tol": 1e-10})394 lr = st.linregress(pos, np.log(grow))395 for i, v in enumerate([12, 13, 14]):396 c.append({"f": f"=INDEX(GROWTH(D!L1:L12,D!F1:F12,{{12;13;14}}),{i + 1})", "e": float(math.exp(lr.intercept + lr.slope * v)), "kind": "num", "tol": 1e-10})397 lr = st.linregress(x1, y)398 for i in [1, 6, 12]:399 c.append({"f": f"=INDEX(TREND(D!B1:B12,D!A1:A12),{i})", "e": float(lr.intercept + lr.slope * x1[i - 1]), "kind": "num", "tol": 1e-10})400 # matrices401 a = np.array([[2, 1, 0], [1, 3, 1], [0, 1, 4]], dtype=float)402 b = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 10]], dtype=float)403 for r, k in itertools.product(range(3), range(3)):404 c.append({"f": f"=INDEX(MINVERSE(D!G1:I3),{r + 1},{k + 1})", "e": float(np.linalg.inv(a)[r, k]), "kind": "num", "tol": 1e-12, "abs": 1e-15})405 c.append({"f": f"=INDEX(MINVERSE(D!G5:I7),{r + 1},{k + 1})", "e": float(np.linalg.inv(b)[r, k]), "kind": "num", "tol": 1e-11, "abs": 1e-13})406 c.append({"f": f"=INDEX(MMULT(D!G1:I3,D!G5:I7),{r + 1},{k + 1})", "e": float((a @ b)[r, k]), "kind": "num", "tol": 1e-14})407 c += [{"f": "=MDETERM(D!G1:I3)", "e": float(np.linalg.det(a)), "kind": "num", "tol": 1e-12}, {"f": "=MDETERM(D!G5:I7)", "e": float(np.linalg.det(b)), "kind": "num", "tol": 1e-11}]408 hilbert = [[1 / (i + j + 1) for j in range(5)] for i in range(5)]409 text = "{" + ";".join(",".join(repr(v) for v in row) for row in hilbert) + "}"410 hinv = mpmath.matrix(hilbert) ** -1411 c.append({"f": f"=MDETERM({text})", "e": float(mpmath.det(mpmath.matrix(hilbert))), "kind": "num", "tol": 1e-9})412 c.append({"f": f"=INDEX(MINVERSE({text}),3,3)", "e": float(hinv[2, 2]), "kind": "num", "tol": 1e-8})413 return c414415416def convert():417 """CONVERT against scipy.constants (exact SI definitions)."""418 ref = {419 "mass": {"g": K.gram, "kg": 1.0, "lbm": K.pound, "ozm": K.ounce, "grain": K.grain, "stone": K.stone, "ton": K.short_ton, "uk_ton": K.long_ton, "LTON": K.long_ton,420 "sg": K.slug, "cwt": 100 * K.pound, "shweight": 100 * K.pound, "uk_cwt": 112 * K.pound, "lcwt": 112 * K.pound, "hweight": 112 * K.pound, "brton": K.long_ton, "mg": 1e-6},421 "dist": {"m": 1.0, "mi": K.mile, "Nmi": K.nautical_mile, "in": K.inch, "ft": K.foot, "yd": K.yard, "ang": K.angstrom, "ly": K.light_year, "Picapt": K.point,422 "Pica": K.point, "pica": K.inch / 6, "survey_mi": K.survey_mile, "ell": 45 * K.inch, "km": 1e3, "um": 1e-6, "kly": 1e3 * K.light_year},423 "time": {"sec": 1.0, "s": 1.0, "mn": K.minute, "min": K.minute, "hr": K.hour, "day": K.day, "d": K.day, "yr": K.Julian_year, "ms": 1e-3},424 "pres": {"Pa": 1.0, "p": 1.0, "atm": K.atm, "at": K.atm, "Torr": K.torr, "psi": K.psi, "kPa": 1e3, "hPa": 1e2, "matm": 1e-3 * K.atm},425 "force": {"N": 1.0, "dyn": K.dyne, "dy": K.dyne, "lbf": K.pound_force, "pond": K.gram * K.g, "kN": 1e3},426 "energy": {"J": 1.0, "e": K.erg, "c": K.calorie_th, "cal": K.calorie_IT, "Wh": K.hour, "wh": K.hour, "kWh": 1e3 * K.hour, "HPh": K.hp * K.hour, "hh": K.hp * K.hour,427 "flb": K.foot * K.pound_force, "BTU": K.Btu_IT, "btu": K.Btu_IT, "kcal": 1e3 * K.calorie_IT, "kJ": 1e3},428 "power": {"W": 1.0, "w": 1.0, "HP": K.hp, "h": K.hp, "PS": 75 * K.g, "kW": 1e3},429 "mag": {"T": 1.0, "ga": 1e-4, "mT": 1e-3},430 "vol": {"l": 1.0, "L": 1.0, "lt": 1.0, "gal": K.gallon / K.liter, "uk_gal": K.gallon_imp / K.liter, "oz": K.fluid_ounce / K.liter, "barrel": K.bbl / K.liter,431 "qt": K.gallon / K.liter / 4, "pt": K.gallon / K.liter / 8, "us_pt": K.gallon / K.liter / 8, "cup": K.gallon / K.liter / 16, "tbs": K.gallon / K.liter / 256,432 "tsp": K.gallon / K.liter / 768, "uk_qt": K.gallon_imp / K.liter / 4, "uk_pt": K.gallon_imp / K.liter / 8, "tspm": 0.005, "m3": 1e3, "ft3": K.foot ** 3 * 1e3,433 "in3": K.inch ** 3 * 1e3, "yd3": K.yard ** 3 * 1e3, "mi3": K.mile ** 3 * 1e3, "Nmi3": K.nautical_mile ** 3 * 1e3, "ang3": K.angstrom ** 3 * 1e3,434 "ly3": K.light_year ** 3 * 1e3, "Picapt3": K.point ** 3 * 1e3, "Pica3": K.point ** 3 * 1e3, "GRT": 100 * K.foot ** 3 * 1e3, "regton": 100 * K.foot ** 3 * 1e3,435 "MTON": 40 * K.foot ** 3 * 1e3, "ml": 1e-3, "cm3": 1e-3, "km3": 1e12, "bushel": 2150.42 * K.inch ** 3 * 1e3},436 "area": {"m2": 1.0, "ha": K.hectare, "ar": 100.0, "uk_acre": K.acre, "us_acre": 43560 * (1200 / 3937) ** 2, "ft2": K.foot ** 2, "in2": K.inch ** 2, "yd2": K.yard ** 2,437 "mi2": K.mile ** 2, "Nmi2": K.nautical_mile ** 2, "ang2": K.angstrom ** 2, "ly2": K.light_year ** 2, "Picapt2": K.point ** 2, "Pica2": K.point ** 2,438 "Morgen": 2500.0, "km2": 1e6, "cm2": 1e-4},439 "info": {"bit": 1.0, "byte": 8.0, "kbit": 1e3, "kibit": 1024.0, "Mibyte": 8.0 * 2 ** 20, "Gbyte": 8e9},440 "speed": {"m/s": 1.0, "m/sec": 1.0, "m/h": 1 / 3600, "m/hr": 1 / 3600, "kn": K.knot, "mph": K.mph, "admkn": 6080 * K.foot / 3600, "km/h": K.kmh, "km/hr": K.kmh},441 }442 c = []443 for units in ref.values():444 names = list(units)445 for i, a in enumerate(names):446 for b in (names[0], names[(i + 3) % len(names)], names[-1]):447 c.append(case("CONVERT", (2.5, a, b), 2.5 * units[a] / units[b], tol=2e-12))448 temps = {"C": lambda k: k - 273.15, "F": lambda k: k * 9 / 5 - 459.67, "K": lambda k: k, "Rank": lambda k: k * 9 / 5, "Reau": lambda k: (k - 273.15) * 0.8}449 to_k = {"C": lambda v: v + 273.15, "F": lambda v: (v + 459.67) * 5 / 9, "K": lambda v: v, "Rank": lambda v: v * 5 / 9, "Reau": lambda v: v * 1.25 + 273.15}450 for a in temps:451 for b in temps:452 for v in [-40, 0, 36.6, 451]:453 c.append(case("CONVERT", (v, a, b), temps[b](to_k[a](v)), tol=1e-12, abs_tol=1e-11))454 return collect(c)455456457def complex_numbers():458 c = []459 zs = [3 + 4j, -2.5 - 1.5j, 1j, -1j, 5 + 0j, 0.5 - 0.25j, -3 + 0j, 1 + 1j, 12.5 + 0.001j, -0.75 + 2j]460461 def text(z):462 re, im = repr(float(z.real)), repr(float(abs(z.imag)))463 if z.imag == 0:464 return re465 return f"{re}{'+' if z.imag > 0 else '-'}{im}i"466467 def cx(name, args, value):468 v = complex(value)469 return {"f": f"={name}({','.join(args)})", "e": f"{v.real!r}{'+' if v.imag >= 0 else '-'}{abs(v.imag)!r}i", "kind": "complex", "tol": 1e-13, "abs": 1e-15}470471 for z in zs:472 t = '"' + text(z) + '"'473 c += [case("IMABS", (text(z),), abs(z), tol=1e-15), case("IMREAL", (text(z),), z.real, tol=0.0), case("IMAGINARY", (text(z),), z.imag, tol=0.0),474 case("IMARGUMENT", (text(z),), cmath.phase(z), tol=1e-15)]475 c += [cx("IMSQRT", [t], cmath.sqrt(z)), cx("IMEXP", [t], cmath.exp(z)), cx("IMLN", [t], cmath.log(z)), cx("IMLOG10", [t], cmath.log10(z)),476 cx("IMLOG2", [t], cmath.log(z) / math.log(2)), cx("IMSIN", [t], cmath.sin(z)), cx("IMCOS", [t], cmath.cos(z)), cx("IMCONJUGATE", [t], z.conjugate())]477 for p in [2, 3, 0.5, -1, -2.5, 7]:478 c.append(cx("IMPOWER", [t, repr(p)], cmath.exp(p * cmath.log(z))))479 for w in [1 - 2j, 3 + 0j, -1j, 0.25 + 0.75j]:480 u = '"' + text(w) + '"'481 c += [cx("IMSUB", [t, u], z - w), cx("IMDIV", [t, u], z / w), cx("IMSUM", [t, u], z + w), cx("IMPRODUCT", [t, u], z * w),482 cx("IMSUM", [t, u, '"2+2i"'], z + w + 2 + 2j), cx("IMPRODUCT", [t, u, '"2+2i"'], z * w * (2 + 2j))]483 return collect(c)484485486if __name__ == "__main__":487 for i in range(12):488 D["cells"][f"J{20 + i}"] = D["cells"][f"A{1 + i}"]489 D["cells"][f"K{20 + i}"] = D["cells"][f"K{1 + i}"]490 total = 0491 for name, fn, sheets in [("scipy_distributions", distributions, []), ("scipy_discrete", discrete, []), ("scipy_special", special, []),492 ("decimal_rounding", rounding, []), ("numpy_descriptive", descriptive, [D]), ("statsmodels_regression", regression, [D]),493 ("scipy_convert", convert, []), ("cmath_complex", complex_numbers, [])]:494 cases = fn()495 seen, uniq = set(), []496 for cs in cases:497 if cs["f"] not in seen:498 seen.add(cs["f"])499 uniq.append(cs)500 write_fixture(name, name.split("_")[0], sheets, uniq)501 total += len(uniq)502 print("total", total)503