Statistical
CORREL function: measure positive, negative, and zero linear correlation
Calculate the Pearson correlation coefficient (-1 to +1) between two paired metrics with CORREL, and understand why constant series return #DIV/0!.
Interpret the -1 to +1 correlation scale
CORREL(array1, array2) measures how closely two paired numeric ranges move along a straight line. Values near +1 indicate strong positive co-movement, values near -1 indicate strong inverse movement, and values near 0 indicate no linear relationship.
Because CORREL divides covariance by both standard deviations, the result is unitless: converting Celsius to Fahrenheit or euros to cents leaves the correlation unchanged.
| Ambient °C (A) | Chiller kWh (B) | Heater kWh (C) |
|---|---|---|
| 16 | 112 | 90 |
| 18 | 118 | 80 |
| 20 | 130 | 70 |
| 22 | 142 | 60 |
| 24 | 148 | 50 |
Compare chiller load vs heater load
Across the five days, Ambient °C and Chiller kWh move up together strongly with r = 192 / sqrt(40 * 936) = 0.9923. Meanwhile Heater kWh drops by exactly 5 kWh per degree, yielding a perfect negative correlation of -1.
=ROUND(CORREL(A2:A6, B2:B6), 4)Why flat data returns #DIV/0!
If every value in either range is identical (for example, Heater kWh stays at 70 every day), its standard deviation is zero. Dividing covariance by zero produces #DIV/0!.
=CORREL(A2:A6, C2:C6)Try the example
Use the original values or change them to test your understanding.