CORREL in Excel: Paired Correlation, Alignment and Nonlinear Counterexamples

• Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official CORREL reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

=CORREL(array1, array2)

Both ranges are required. Microsoft states that text, logical values and empty reference cells are ignored, while genuine zero values are included. Its reference assigns #N/A to unequal data-point counts and #DIV/0! to an empty array or a zero standard deviation.

The examples below use complete numeric pairs. The general ignore rule does not establish every mismatched-blank/error pairing case; clean and align the paired records explicitly rather than independently compacting the columns.

Enter this table in A1:B5:

=CORREL(A2:A5,B2:B5)

Expected result: 0.6. Each mean is 2.5. The deviations and products are:

The products sum to 3. Each column’s squared deviations sum to 5. Microsoft’s equation therefore gives 3/SQRT(5*5)=0.6.

In C2 enter =-B2 and fill through C5. The new Y values are -2, -1, -4 and -3.

=CORREL(A2:A5,C2:C5)

Expected result: -0.6. Changing the sign of one variable reverses the direction of the linear association. It does not turn a moderate association into a perfect one.

In a separate D2:D5 helper, use =B2*10+7. Values 27, 17, 47 and 37 still correlate 0.6 with X. Positive rescaling and a constant offset leave this model’s coefficient unchanged, unlike covariance’s scale-dependent magnitude.

Keep X as 1,2,3,4 but independently sort Y to 1,2,3,4 in F2:F5. =CORREL(A2:A5,F2:F5) gives 1.

That is a different dataset: the original pair (1,2) has become (1,1), and every row’s identity may have changed. The increase from 0.6 to 1 is not improved data quality. Sort the whole record table together, and use a reliable shared key when combining separately collected measurements.

Reversing the order of both original columns together retains all pairs and still gives 0.6. Reordering pairs is different from reassigning partners.

In H2:H6 enter -2, -1, 0, 1 and 2. In I2:I6 enter their squares: 4, 1, 0, 1 and 4.

=CORREL(H2:H6,I2:I6)

Expected result: 0. The centered cross-products cancel, even though every Y value is exactly X squared. A zero coefficient does not establish independence or the absence of a nonlinear relationship. Inspect a scatter plot and the measurement context before drawing a conclusion.

A constant comparison range cannot produce a defined coefficient under the stated equation; the CORREL reference specifically documents #DIV/0! for that case. Do not replace this with zero and call it no association.

Correlation also does not identify a cause. The examples audit a statistic, not a causal study or a significance test. Very large magnitudes, mixed errors and numerical stability near a constant vector are not native-tested here.

PEARSON documents the same centered-product coefficient, while COVARIANCE.P retains the product of measurement units. AVERAGE helps expose the centers used in the calculation.

• Microsoft CORREL reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.