Definition. 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.
Arguments
array1 (required). A cell range of values
array2 (required). A second cell range of values
Note
If an array or reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value 0 are included.
If array1 and array2 have a different number of data points, CORREL() returns the #N/A error. If either array1 or array2 is empty or if s (the standard deviation) of their values equals 0, CORREL() returns the #DIV/0! error.
Background. Is there any correlation between two variables? This question often comes up when data is analyzed or interpreted. To answer this question, you can use correlation analysis.
Using the correlation coefficient, you can determine the relation between two properties. The result is a value from 1 (perfect correlation) to –1 (the absolute contrary effect). The sign indicates the direction of the correlation.
The correlation analysis provides an important method for determining the linear correlation between two variables (in the example later in this section, these will be the website visits and the online orders).
The following formula calculates the equation:
where
–1 ≤Pxy ≤ 1
and where
Statements. The following apply to the correlation coefficient:
If the value is smaller than 0.3, the correlation between the two variables is minor.
If the value is from 0.3 through 0.5, the correlation is moderate.
If the value is from 0.5 through 0.7, the correlation is distinct.
If the value is from 0.7 through 0.9, the correlation is close.
If the value is greater than 0.9, the correlation is very close.
Example. A software company sells all of its products through its website. The company regularly sends out newsletters to inform existing and potential customers about new and updated products and to draw attention to its website.
Last year, the orders through the website significantly increased. The management wants to know the reason. Is the increase in sales attributable to marketing or to advertising? Did the increased website visits cause the increase in sales?
This means that the company wants to know the correlation between the website visits and the online orders. The CORREL() function provides the evidence (see Figure 12-26).
Note
If the result is a correlation coefficient close to +1, the correlation between the two variables is positive. This means that the greater the value of variable x (orders), the greater the value of variable y (website visits).
The correlation coefficient 0 indicates that the two variables are independent of each other.
The correlation coefficient –1 indicates a negative correlation between x (orders) and y (website visits).
Figure 12-27 shows the dependency between the website visits and the orders without the correlation coefficient.
The correlation coefficient of 0.89 indicates a positive, close correlation between the two variables. This means that if the number of website visits increases because of marketing activities, the number of online orders also increases.