PlanMaker COVARIANCE.S: Sample Covariance, Paired Data and Duplicates
Rebuild PlanMaker sample covariance with n-minus-one calculations, contrast population covariance and see how duplicated records change the result.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
COVARIANCE.S returns sample covariance between two paired data sets. It differs from population covariance even on the same observations: the usual sample calculation divides the centered cross-product total by one less than the number of pairs.
Availability and version history
- Introduced: Documented by the PlanMaker 2018 manual; the first introduction release is not established.
- Availability: Documented in PlanMaker 2026; this is not a final working-version endpoint.
- Scope: The reference belongs to the 2026/NX manual family. Individual FreeOffice and mobile-build availability remains unverified.
- Checked: October 14, 2026, against the official function reference. Examples use vendor results and independent calculations; no native PlanMaker test was performed.
Syntax and documented limits
=COVARIANCE.S(Range1, Range2)
Both arguments are required references. The current PlanMaker entry specifies integers, says empty cells, text and logical values are ignored, and gives #N/A for different numbers of values. Use complete integer pairs for the following calculations.
The 2018 manual did not state the integer restriction; the sources do not establish fractional-input acceptance or the exact history of that wording. They also do not fully describe alignment when ignored entries appear in different positions.
The description uses the broad word “average,” but identifies the function as sample covariance. Its published example matches the standard n−1 calculation below, rather than division by n. This is a mathematical reconstruction, not a claim to have inspected PlanMaker’s implementation. Formulas use English names and commas.
Example: reconstruct the sample covariance
Create SamplePairs with x and y in A1:B1. Enter:
| Row | A: x | B: y |
|---|---|---|
| 2 | 2 | 1 |
| 3 | 4 | 3 |
| 4 | 6 | 2 |
| 5 | 8 | 6 |
Enter =COVARIANCE.S(A2:A5,B2:B5) in H2. The expected result is 14/3, approximately 4.666666667.
Put =AVERAGE(A2:A5) in H4 and =AVERAGE(B2:B5) in H5; expect 5 and 3. In C1:E1 enter dx, dy and Product. Put =A2-$H$4, =B2-$H$5 and =C2*D2 in C2:E2, then fill to row 5.
| Row | dx | dy | Product |
|---|---|---|---|
| 2 | -3 | -2 | 6 |
| 3 | -1 | 0 | 0 |
| 4 | 1 | -1 | -1 |
| 5 | 3 | 3 | 9 |
In H7 enter =SUM(E2:E5); expect 14. In H8 enter =H7/(4-1); expect 4.666666667, agreeing with H2 at the displayed precision.
The four paired observations determine both means, and the sample denominator is three. Do not round the means or cross-products before finishing the calculation.
Compare the two covariance conventions
On SamplePairs, add:
| Cell | Formula | Expected result |
|---|---|---|
| H10 | =COVARIANCE.P(A2:A5,B2:B5) |
3.5 |
| H11 | =COVAR(A2:A5,B2:B5) |
3.5 |
| H12 | =H10*4/3 |
4.666666667 |
| H13 | =COVARIANCE.S(B2:B5,A2:A5) |
4.666666667 |
Population covariance uses four in the denominator; sample covariance uses three. Both operate on the same centered cross-products. Swapping the complete paired series preserves either covariance.
A different denominator is not evidence of stronger association. Choose the convention appropriate to whether the data represent the population of interest or a sample used for estimation.
Duplicating rows changes the sample correction
Create another sheet named RepeatedPairs. Put x and y in A1:B1. Enter the original four pairs from SamplePairs twice, in the same order, in A2:B9:
| Row | x | y |
|---|---|---|
| 2 | 2 | 1 |
| 3 | 4 | 3 |
| 4 | 6 | 2 |
| 5 | 8 | 6 |
| 6 | 2 | 1 |
| 7 | 4 | 3 |
| 8 | 6 | 2 |
| 9 | 8 | 6 |
Enter =COVARIANCE.S(A2:A9,B2:B9) in D2; expect 4. Enter =COVARIANCE.P(A2:A9,B2:B9) in D3; expect 3.5.
The means remain 5 and 3. The centered product total doubles from 14 to 28. The sample result is 28/(8−1) = 4, while the population result is 28/8 = 3.5. Repeating records is not a way to obtain new independent observations; this control shows why a denominator-sensitive statistic can change when rows are duplicated.
Direction and unit scaling
On SamplePairs, put Negative y and x times 100 in J1:K1. Enter =-B2 in J2 and =100*A2 in K2; fill to row 5.
- M2:
=COVARIANCE.S(A2:A5,J2:J5)gives approximately −4.666666667. - M3:
=COVARIANCE.S(K2:K5,B2:B5)gives approximately 466.666666667. - M4:
=CORREL(A2:A5,B2:B5)gives approximately 0.836660027.
Covariance retains direction and the product of measurement units. It is not bounded between −1 and 1. Use the normalized correlation when the intended comparison is unitless linear association, while remembering that neither statistic establishes causation.
Reproduce the manual’s sample result
Create VendorSample with 2,5,3 in A1:A3 and 2,7,4 in B1:B3. Enter =COVARIANCE.S(A1:A3,B1:B3) in D1.
The centered cross-product total is 23/3. Dividing by 3−1 gives 23/6, approximately 3.833333333, rounding to SoftMaker’s 3.83333. Dividing by three instead would give the population result 2.555555556.
Small samples, compatibility and related functions
The ordinary sample formula needs at least two complete pairs. A one-pair denominator is zero, but the short official entry does not give the exact native error token for that boundary. Its explicit #N/A claim concerns different value counts; keep those cases distinct.
SoftMaker’s note that the dotted covariance names require Excel 2010 or later is about Excel. It does not establish a PlanMaker introduction version, a retirement date for COVAR or tested workbook interoperability.
- COVARIANCE.P for population covariance.
- COVAR for the older population name.
- CORREL for normalized association.
- AVERAGE for the centering step.