This function returns the correlation coefficient of a two-dimensional random variable with values in the cell ranges array1 and array2. Use the correlation coefficient to determine the relationship between two properties.
For example, you can examine the relationship between the number of website visits and online orders.
Syntax
CORREL(array1; array2)
Arguments
- array1 (required): A cell range of values.
- array2 (required): A second cell range of values.
Background
Is there any correlation between two variables? This question often arises when analyzing or interpreting data. To answer it, you can use correlation analysis.
The correlation coefficient measures the relationship between two properties, producing a value between -1 (perfect negative correlation) and 1 (perfect positive correlation). The sign indicates the direction of the correlation.
Correlation analysis is a key method for determining the linear relationship between two variables (e.g., website visits and online orders).
The formula for the correlation coefficient is:
where:
- −1≤Pxy≤1−1≤Pxy≤1
- Cov(X,Y) is the covariance between variables X and Y
- σX and σY are the standard deviations of X and Y
Interpretation
The following guidelines apply to the correlation coefficient:
- < 0.3: Weak correlation
- 0.3 – 0.5: Moderate correlation
- 0.5 – 0.7: Distinct correlation
- 0.7 – 0.9: Strong correlation
- > 0.9: Very strong correlation
Example
A software company sells all its products through its website. The company sends newsletters to inform customers about new products and drive traffic to the site.
Last year, online orders increased significantly. Management wants to determine whether this growth resulted from marketing efforts or increased website visits.
Using CORREL(), the company analyzes the relationship between website visits and online orders (see Figure below).
Figure below illustrates the dependency between website visits and orders without the correlation coefficient.
The calculated correlation coefficient of 0.89 indicates a strong positive correlation—meaning that as website visits rise (due to marketing), online orders also increase.