ONLYOFFICE COVARIANCE.P: Population Covariance, Units and Correlation
Calculate population covariance in ONLYOFFICE with centered-product worksheets, correlation checks, unit conversions and verified examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =COVARIANCE.P(A2:A5,B2:B5) returns an independently expected 3.5 for the four complete pairs below. It divides the sum of centered cross-products by n, the number of pairs in the population being described.
Availability and version history
- Introduced: The first introduction release and date are not established by the checked sources.
- Availability: Currently documented in ONLYOFFICE’s rolling function help; no final supported-version endpoint is established.
- Scope: Shared Spreadsheet Editor documentation, with the calculation divisor corroborated by an explicitly pinned official source-code revision. Individual client/build parity is unverified.
- Checked: October 14, 2026, against the official COVARIANCE.P reference, source and vendor screenshot. Example outputs were independently calculated; these cases were not tested in the native application.
Syntax and population meaning
=COVARIANCE.P(array1, array2)
Both ranges are required and must contain the same number of elements. The official reference explicitly identifies population covariance. It says text, logical values and empty cells are ignored while numeric zeros are included.
Use this descriptive population form when the listed pairs are the complete set you intend to describe. If the aim is to estimate covariance from a sample, COVARIANCE.S uses a different divisor. Calling one form does not decide whether your observations actually constitute a population or a representative sample.
The pinned official implementation corroborates division by the numeric-pair count. The examples use complete numeric data rather than relying on unspecified import behavior.
A complete centered-product worksheet
Create a blank sheet named PopulationPairs. Put x, y, Centered x, Centered y and Product in A1:E1. Enter these four observations in A2:B5:
| Row | A: x | B: y |
|---|---|---|
| 2 | 2 | 1 |
| 3 | 4 | 3 |
| 4 | 6 | 2 |
| 5 | 8 | 6 |
Use G for labels. Enter =AVERAGE(A2:A5) in H2 and =AVERAGE(B2:B5) in H3; expect 5 and 3. Enter =A2-$H$2 in C2, =B2-$H$3 in D2 and =C2*D2 in E2. Fill C2:E2 through row 5.
| Row | Expected C | Expected D | Expected E |
|---|---|---|---|
| 2 | -3 | -2 | 6 |
| 3 | -1 | 0 | 0 |
| 4 | 1 | -1 | -1 |
| 5 | 3 | 3 | 9 |
Put =SUM(E2:E5) in H5; expect 14. Put =H5/4 in H6 and =COVARIANCE.P(A2:A5,B2:B5) in H7; both mathematical targets are 3.5.
One product is negative because x is above its mean while y is below its mean. Covariance adds all centered products; it does not require every pair to move in the same direction.
Advanced: compare covariance with correlation
Put =STDEV.P(A2:A5) in H9 and =STDEV.P(B2:B5) in H10. Independent expected results are approximately 2.236067977 and 1.870828693. Enter =H7/(H9*H10) in H11 and =CORREL(A2:A5,B2:B5) in H12; both targets are approximately 0.836660027.
The population variances here are 5 and 3.5. Their standard deviations normalize the covariance, removing the units. Covariance 3.5 is not a correlation exceeding one and does not mean a 350% relationship.
Keep the same complete pairs in every component. Separately calculated standard deviations that include different missing-data subsets would not form the same normalization.
A unit conversion changes covariance but not this correlation
Leave A:H intact. Put Scaled y in J1 and =B2*60 in J2; fill through J5. If y was measured in hours, this column expresses minutes.
Put =COVARIANCE.P(A2:A5,J2:J5) in N2 and =CORREL(A2:A5,J2:J5) in N3. Expected results are 210 and approximately 0.836660027. Covariance becomes 60 times larger, while the positive rescaling leaves correlation unchanged.
In N4 enter =COVARIANCE.P(A2:A5,A2:A5); expect 5, the population variance of x. Using the same variable twice turns each centered product into a square. The result remains in squared x-units.
Reproduce the vendor’s negative example
On a new sheet named VendorPopulation, enter 12,65,178,45,98 across A1:E1 and 45,78,34,90,147 across A2:E2. Do not add a header row to this particular block.
Enter =COVARIANCE.P(A1:E1,A2:E2) in G2. The official screenshot displays -248.88. The two means are 79.6 and 78.8; the centered cross-product sum is -1244.4, yielding -248.88 after division by five.
A negative covariance indicates a negative average association in these pairs. Its magnitude depends on the measurement units, and the example does not establish causation or a reliable prediction rule.
Troubleshooting and related functions
Match corresponding observations by position and preserve zeros as real data. Equal range lengths alone are not enough if one column was independently sorted or shifted. For an unexpected result, inspect the centered products before rounding them.
The short function entry does not give a complete table of exact error codes, all degenerate datasets or every text/import boundary. The source review is scoped to its explicit commit and is not a native test or proof that every client build has identical edge behavior.
COVAR has the same population-style result for these numeric examples and remains currently documented. COVARIANCE.S supplies the sample form. CORREL is the normalized measure, while STDEV.P measures population spread for one variable.