CORREL function

CORREL returns the Pearson correlation coefficient between two data sets, ranging from -1 to 1 to indicate relationship strength.

=CORREL(array1, array2)

Generate a CORREL formula

Describe what you need. The generator will reach for CORREL where CORREL is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How CORREL reads its arguments
array1requiredarray2requiredCORREL →
ArgumentRequiredDescription
array1RequiredA numeric array or range representing the first data set. Must be the same size as array2 and contain only numbers.
array2RequiredA numeric array or range representing the second data set. Must be the same size as array1 and contain only numbers.

Returns

Returns a decimal number between -1 and 1, representing the strength and direction of linear correlation.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Correlation between plan cost and customer lifetime

CustomerPlanMRRSignupChurnDays Active
Acme IncPro5002024-01-152026-08-20943
Beta CoStarter992024-03-10250
Gamma LtdBusiness20002023-11-052025-06-30598
Delta CorpPro5002024-06-20650
EpsilonStarter992024-02-142026-02-01354
Zeta LabsBusiness20002023-08-30680
Theta IncPro5002024-05-012025-12-15588
Iota SystemsStarter992024-07-15125
=CORREL({500,99,2000,500,99,2000,500,99},{943,250,598,650,354,680,588,125})

Result: 0.786

This formula compares MRR (monthly recurring revenue) in column C against Days Active, calculated from signup and churn dates. The result of 0.786 indicates strong positive correlation: higher-plan customers tend to stay longer. This insight guides retention strategy toward protecting high-value accounts.

2. Correlation between signup month and plan tier

CustomerPlanSignup MonthPlan Tier
Acme IncPro12
Beta CoStarter31
Gamma LtdBusiness113
Delta CorpPro62
EpsilonStarter21
Zeta LabsBusiness83
Theta IncPro52
Iota SystemsStarter71
=CORREL({1,3,11,6,2,8,5,7},{2,1,3,2,1,3,2,1})

Result: 0.321

Signup month (1–12) is correlated against plan tier (1=Starter, 2=Pro, 3=Business). The result of 0.321 shows weak positive correlation, meaning signup timing has minimal influence on which plan customers choose. This helps determine whether seasonal campaigns should target specific tiers.

3. Correlation between MRR and account retention

CustomerMRRChurn DateStill Active
Acme Inc5002026-08-201
Beta Co990
Gamma Ltd20002025-06-300
Delta Corp5001
Epsilon992026-02-010
Zeta Labs20001
Theta Inc5002025-12-150
Iota Systems990
=CORREL({500,99,2000,500,99,2000,500,99},{1,0,0,1,0,1,0,0})

Result: 0.547

MRR is correlated against retention status (1=active, 0=churned). The 0.547 result indicates moderate positive correlation: customers on higher-value plans are more likely to stay. This reveals that lower-tier customers churn more frequently, suggesting they need targeted engagement or pricing adjustments.

Common errors

Which CORREL error are you seeing?
CORREL returned an error#VALUE!
Ensure both arrays contain only numbers. Convert dates to numeric values using DATE functions or helper columns. Extract numeric portions from text cells using VALUE() or numeric column references only.
#N/A
Verify that array1 and array2 contain equal row counts. Use COUNTA() on both ranges to confirm matching element counts. Check for hidden rows or misaligned range references, and trim ranges to the same length.
#DIV/0!
Check that your data actually varies across observations. If all MRR values equal 500, or all Active flags are 1, correlation cannot be calculated. Include data with natural variation, or reconsider whether correlation analysis applies to constant-value columns.
ErrorWhy it happensHow to fix it
#VALUE!One or both arrays contain non-numeric values such as text, dates, or mixed data types that CORREL cannot process numerically.Ensure both arrays contain only numbers. Convert dates to numeric values using DATE functions or helper columns. Extract numeric portions from text cells using VALUE() or numeric column references only.
#N/AThe two arrays have different lengths. CORREL requires both arrays to be exactly the same size to calculate the correlation coefficient.Verify that array1 and array2 contain equal row counts. Use COUNTA() on both ranges to confirm matching element counts. Check for hidden rows or misaligned range references, and trim ranges to the same length.
#DIV/0!One or both arrays contain identical values (zero variance or standard deviation), making correlation undefined mathematically.Check that your data actually varies across observations. If all MRR values equal 500, or all Active flags are 1, correlation cannot be calculated. Include data with natural variation, or reconsider whether correlation analysis applies to constant-value columns.

Tips and when to use something else

  • CORREL measures linear relationships only. Values near 0 suggest no linear pattern, but curved or stepwise relationships may still exist; use scatter plots to visualize patterns before interpreting results.
  • Always verify array sizes match using COUNTA before building your CORREL formula; mismatched lengths produce #N/A and halt your calculation.
  • Use COVARIANCE.P or COVARIANCE.S if you need the unnormalized joint variability of your data (e.g., for portfolio risk analysis), but prefer CORREL for interpreting relationship strength across different data sets.
  • For small samples (under 30 rows), correlation coefficients can be misleading; pair CORREL results with hypothesis testing or confidence intervals to assess statistical significance.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

What does a correlation of 0.8 mean?
A correlation of 0.8 indicates strong positive correlation: as one data set increases, the other tends to increase as well. The relationship is not perfect (1.0), but the trend is pronounced and likely statistically significant. In subscription billing, 0.8 between MRR and customer lifetime suggests that paying more strongly predicts longer tenure.
Can CORREL predict future values?
No. CORREL measures past relationships only; it does not forecast. To predict based on correlation, use regression functions like LINEST or SLOPE. CORREL is useful for identifying which variables move together, but actual forecasting requires statistical modeling with confidence intervals and residual analysis.
Why did my formula return a negative correlation?
Negative correlation means inverse movement: as one array increases, the other tends to decrease. For example, correlation between MRR and churn rate of −0.65 means higher-value customers churn less frequently. Negative correlations are equally meaningful; they simply indicate opposite directional trends.
How is CORREL different from COVARIANCE?
COVARIANCE measures joint variability in the original units (e.g., dollars²) and depends on scale, while CORREL normalizes the result to a unitless −1 to 1 range independent of magnitude. CORREL is easier to interpret and compare across different data sets. Use CORREL for understanding relationships; use COVARIANCE for specialized applications like portfolio variance.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17