ONLYOFFICE CORREL: Correlation with Paired Data and Worked Checks
Calculate correlation in ONLYOFFICE with complete paired data, visible centered sums, unit checks and the official mixed-data example.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =CORREL(A2:A6,B2:B6) measures the direction and strength of a linear relationship between paired observations. For x values 1, 2, 3, 4, 5 and y values 2, 4, 5, 4, 5, the independently expected coefficient is approximately 0.7745966692.
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. Exact availability in individual Docs, Desktop and mobile builds is not established by this page.
- Checked: October 14, 2026, against the official CORREL reference. Examples were independently calculated and compared with the vendor screenshot; these cases were not tested in the native application.
Syntax and paired observations
=CORREL(array1, array2)
Both ranges are required. The official CORREL reference requires the same number of elements and says that text, logical values and empty cells are ignored, while numeric zeros are included. No optional confidence level, ranking method or missing-value replacement is provided.
Keep corresponding measurements together: row 2 in the first column must describe the same observation as row 2 in the second. Sorting one column without the other destroys that pairing. The complete numeric worksheet below avoids uncertainty about partly missing observations.
The mathematical coefficient ranges from −1 to 1 when defined. A positive value indicates a positive linear association; a negative value indicates a negative one. Its magnitude does not measure the slope, and a correlation by itself does not establish causation.
Build a complete five-observation worksheet
Create a blank sheet named PairedData. Put x, y, x deviation, y deviation, Product, x squared deviation and y squared deviation in A1:G1. Enter A2:B6 as follows:
| Row | A: x | B: y | Expected C | Expected D | Expected E | Expected F | Expected G |
|---|---|---|---|---|---|---|---|
| 2 | 1 | 2 | -2 | -2 | 4 | 4 | 4 |
| 3 | 2 | 4 | -1 | 0 | 0 | 1 | 0 |
| 4 | 3 | 5 | 0 | 1 | 0 | 0 | 1 |
| 5 | 4 | 4 | 1 | 0 | 0 | 1 | 0 |
| 6 | 5 | 5 | 2 | 1 | 2 | 4 | 1 |
In I2 enter =AVERAGE(A2:A6) and in I3 =AVERAGE(B2:B6). Expect 3 and 4. Then enter:
- C2:
=A2-$I$2 - D2:
=B2-$I$3 - E2:
=C2*D2 - F2:
=C2^2 - G2:
=D2^2
Fill C2:G2 through row 6. Put =SUM(E2:E6) in I5, =SUM(F2:F6) in I6 and =SUM(G2:G6) in I7. Expect 6, 10 and 6, respectively.
In I9 enter =CORREL(A2:A6,B2:B6). In I10 enter =I5/SQRT(I6*I7). Both calculations should be approximately 0.7745966692, because the centered product sum is divided by the square root of the two centered square sums.
Use the stored values in later formulas. Rounding a display to ten decimal places is different from replacing a calculated cell with that rounded text.
Advanced: changing units does not change correlation
Keep the PairedData sheet intact. On a separate sheet named Units, put Original x, Original y, Scaled y and Reversed y in A1:D1. Enter the same five x/y pairs in A2:B6. In C2 enter =1000*B2 and in D2 enter =-B2; fill both through row 6.
Put these formulas in F2:F4:
| Cell | Formula | Independently expected result |
|---|---|---|
| F2 | =CORREL(A2:A6,B2:B6) |
0.7745966692 |
| F3 | =CORREL(A2:A6,C2:C6) |
0.7745966692 |
| F4 | =CORREL(A2:A6,D2:D6) |
-0.7745966692 |
Multiplying y by a positive constant, such as converting kilograms to grams, preserves this dimensionless correlation. Reversing its sign reverses the coefficient. In contrast, a regression slope carries units and changes with scaling. Use SLOPE when the question is how much y changes per unit of x.
Reproduce the official mixed-data example
On another blank sheet, enter 78, 89, 45, 90 in A1:D1. Enter the literal text text, the literal text apples, then the numbers 3, 6 in A2:D2. Do not include quote characters in the two text cells.
Put =CORREL(A1:D1,A2:D2) in D3. ONLYOFFICE’s reference screenshot shows 1. The two complete numeric pairs, (45,3) and (90,6), have exactly positive correlation. This is a narrowly documented mixed-data example; it does not establish every behavior involving errors, mismatched ranges or other missing-data patterns.
Interpretation and troubleshooting
- Inspect a scatter plot or the actual pairs. A coefficient near zero can coexist with a strong curved relationship.
- Numeric zero is an observation. Do not replace an unknown measurement with zero merely to fill a gap.
- A constant series has no variation, so the mathematical correlation is undefined. The short function page does not enumerate its exact native error token or every insufficient-data case.
- Two distinct complete pairs necessarily produce a perfect positive or negative coefficient. That alone is weak evidence about a wider relationship.
- Extreme scales and nearly constant data can expose numerical sensitivity. Keep units sensible and compare independently calculated results with a tolerance.
PEARSON names the product-moment coefficient explicitly. RSQ squares that coefficient and removes its sign; AVERAGE supplies the means used in the transparent check above.