The variance family uses raw moments with stored steps, and two tests switch it to a centered sum

FN-VAR.S-001: The variance family uses raw moments with stored steps, and two tests switch it to a centered sum

Excel differs from the mathematical answer · measured · recorded 2026-11-04

With RN(v) meaning v rounded to 64 bits on the x87 unit and then stored to binary64, S the running RN sum of the n values and Q the running RN sum of RN(x*x), the sample variance is RN(RN(Q - RN(RN(S*S)/n))/(n-1)) and the population variance RN(RN(RN(n*Q) - RN(S*S))/RN(n*n)). Excel then uses a centered sum instead, adding RN(d*d) with d = RN(x - mean) and dividing by n-1 or n, when either of two stored tests holds: RN(RN(RN(S*S)/n)/Q) > 0.99, or RN(variance*1000) <= |RN(S/n)|. The STDEV functions are the root of this, rounded to 64 bits and then stored. So the answer depends on which path a call takes: the first two VAR.S calls below differ by a few input steps in two arguments, the first passes the 0.99 test and is correctly rounded, and the second takes the raw moments and lands 76 units high. The next pair shows the size test: scaling the inputs by 4 leaves the first path.

Correct value: Exact rational variance of the binary64 inputs, correctly rounded.

Correct value: Exact rational variance of the binary64 inputs, correctly rounded.

Correct value: Exact rational variance of the binary64 inputs, correctly rounded.

Basis: All 14,679 recorded rows of the twelve functions match: VAR.S, VAR.P, VAR, VARP, VARA, VARPA, STDEV.S, STDEV.P, STDEV, STDEVP, STDEVA and STDEVPA. Six of those rows separate the stored steps from plain binary64. 910 fresh scalar variance readings (Excel build 20527) replay exactly in rational arithmetic. Four cancellation edges were bisected to adjacent inputs; sixteen triples chosen to separate the stored ratio from 0.99*Q and from a division by 0.99 all agree with the ratio, where 0.99*Q misses all 16 and the division 14. Seven of eight magnitude brackets reach adjacent inputs, and sixteen chosen roots all need the 64-bit root followed by a store.

Inferred: These tests were located with scalar arguments in separate cells; that the same edges hold for ranges and arrays is inferred, and their readings have not been taken. RN(variance*1000) <= |mean| is not separated from the rearrangement variance <= |mean|/1000. That a raw moment which overflows also takes the centered sum is inferred from recorded answers that stay finite.

Open: Whether the two tests keep the same edges for ranges and arrays.