PEARSON

Returns the Pearson correlation coefficient of two sets of data.

Syntax:

PEARSON(x, y)

where x and y are ranges or arrays containing the two sets of data.

Any text or empty entries are ignored.

PEARSON calculates:

$\frac{\sum _{i} ( x _{i} - x ) ( y _{i} - y )}{\sum _{i} ( x _{i} - x ) ^{2} \sum _{i} ( y _{i} - y ) ^{2}}$

where $\overline{x}, \overline{y}$ are the averages of x,y.

Example:

PEARSON(A1:A30, B1:B30)

returns the Pearson correlation coefficient for the two sets of data in A1:A30 and B1:B30.

Application:

Employee Absenteeism vs. Productivity

A company's human resources department wants to investigate if there's a linear relationship between the number of days employees are absent from work and their monthly productivity scores. The productivity score is a metric from 0 to 100 based on completed tasks, quality of work, and efficiency. They collect data for 10 employees over a single month.

	Employee	Days Absent (X)	Productivity Score (Y)
	A	B	C
1	1	2	85
2	2	0	98
3	3	5	60
4	4	1	90
5	5	3	75
6	6	0	95
7	7	4	70
8	8	1	88
9	9	6	55
10	10	2	80

Step 2: Use the PEARSON function

The PEARSON function takes two ranges of data as its arguments: PEARSON(data_y, data_x).

In our example, the Productivity Score (Y) is the dependent data, and the Days Absent (X) is the independent data.

The formula would be:

PEARSON(C1:C10, B1:B10)

Step 3: Get the Result

The result will be -0.993525222.

Interpretation of the Result

The correlation coefficient is very close to -1, indicating a very strong negative linear relationship between days absent and productivity scores. The result still shows that as employee absenteeism increases, their productivity score tends to decrease in a predictable, linear manner. The function provides a quick and error-free way to find the correlation coefficient, saving you from the tedious manual calculations. This demonstrates how a function can be used in a business scenario to quickly analyze the relationship between two variables.

Result for PEARSON(C1:C10, B1:B10):

-0.993525222