CORREL: Paired Linear Correlation in WPS Spreadsheet
Calculate CORREL from complete numeric pairs, with centered-sum arithmetic, positive and negative controls, nonlinear counterexample and documented errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
CORREL measures the linear association between paired numeric observations. Its coefficient is dimensionless and lies mathematically between −1 and 1 for valid nonconstant numeric data. A positive value indicates that larger values in one variable tend to accompany larger values in the other; a negative value indicates the opposite direction. Correlation alone does not establish causation.
Syntax and pairing
=CORREL(array1, array2)
Supply two equally sized sets of corresponding observations. The official lesson also demonstrates rectangular ranges, but the original controls here use aligned numeric columns so the intended pairings are unmistakable. Do not independently sort one column and then treat the resulting pairings as the original observations.
The lesson’s formula image defines the coefficient as the sum of centered cross-products divided by the square root of the product of the two centered sums of squares. No units remain because numerator and denominator have matching units.
Complete example with a nonperfect correlation
Put x values 1, 2, 3 in A2:A4 and corresponding y values 1, 2, 2 in B2:B4. Enter =CORREL(A2:A4,B2:B4). Independently:
- Mean x is 2; mean y is 5/3.
- The x deviations are −1, 0, 1; their squared sum is 2.
- The y deviations are −2/3, 1/3, 1/3; their squared sum is 2/3.
- The cross-product sum is 1.
- The coefficient is 1/sqrt(2×2/3) = sqrt(3)/2, approximately 0.866025403784.
This is a strong positive linear association for these three illustrative pairs, not a statistical significance result or a causal conclusion.
Original numeric controls
Replace A2:A4 and B2:B4 for each row below. All values are numbers.
| x values | y values | Expected coefficient |
|---|---|---|
| 1, 2, 3 | 3, 5, 7 | 1 |
| 1, 2, 3 | 7, 5, 3 | −1 |
| 1, 2, 3 | 1, 2, 2 | 0.866025403784 |
| −1, 0, 1 | 1, 0, 1 | 0 |
| 0, 1, 2 | 2, 4, 6 | 1 |
The zero-correlation control follows y=x². Its linear coefficient is zero even though y is completely determined by x in these three points. This is a useful reminder that no linear correlation does not mean no relationship.
Practical interpretation
Changing from x=1,2,3 with y=3,5,7 to a different positive linear scaling preserves perfect positive correlation. The coefficient describes alignment, not equality of units or magnitudes. A coefficient of 1 does not mean the two measured quantities have equal values.
Inspect the data and pairing, particularly outliers, before interpreting the coefficient. With only a few observations, an illustrative numerical result should not be treated as reliable evidence about a larger population. CORREL does not itself provide a confidence interval, p-value or model-validation decision.
Documented errors and missing data
The lesson specifies #N/A for unequal data-point counts and #DIV/0! when a set is empty or has zero standard deviation. For example, A2:A4 containing 2,2,2 and B2:B4 containing 1,2,3 exercises the documented zero-variation condition. These are documentation-based expectations, not newly observed WPS errors.
Text, logical values and blanks in arrays or references are described as ignored; zero is included. The lesson does not fully specify every mixed-type alignment detail, so these controls use complete numeric pairs. Do not remove missing values from one variable alone and silently change which observations belong together. Exact precision and extreme-value numerical stability remain untested.
Availability and evidence
The official WPS lesson is dated 2019-05-30 and explicitly labels its applicability WPS Office for Windows Personal. It does not identify a numeric release. This establishes documentation by that date, not the function’s original introduction or its last working version. Current build limits and equivalence across WPS clients remain unverified. The available evidence does not establish retirement or formal deprecation.
Checked October 18, 2026. The examples in this guide are original, independently calculated controls based on the lesson. They have not been run in WPS or another spreadsheet application. No screenshot or execution result is claimed.