How to use Excel COVARIANCE.P Function in Excel Office 365?
Tags: touseexcelcovariance, excel, excelfunctions
Published: 2021-08-30 02:21:37

---
title: How to use Excel COVARIANCE.P Function in Excel Office 365?
published: true
date: 2021-08-26 16:33:58 UTC
tags: ToUseExcelCOVARIANCE,Excel,ExcelFunctions
canonical_url: https://geekexcel.com/how-to-use-excel-covariance-p-function-in-excel-office-365/
---

In this article, you will learn how to use **Excel COVARIANCE.P Function In Excel Office 365** using its basic syntax, explanation, and examples. Let’s see them below!! Get the official version of **MS Excel** from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

## Covariance

The measuring of the relationship between the **two random variables** is called **covariance**. The **covariance value** can be a **negative or positive value**. A **negative value** means that **both variables** move in **opposite directions**. then a **positive covariance** means that the **two variables** move in the **same direction**.

## COVARIANCE.P Function

- Excel provides  **COVARIANCE.P ** to calculate the **covariance of population** easily.
- There is another version of this method named **COVARIANCE.S,** which is used to calculate the **covariance of the sample data set**.

## COVARIANCE.P Function Syntax

```
=COVARIANCE.P(array1,array2)
```

**Syntax Explanation:**

- **array1:** The values of the first variable.
- **array2:** The values of the second variable.

**NOTES:**

- These **arrays** can be put in **any order**.
- The **length of the two arrays** should be the **same**.
- If the **two arrays** are of **two different lengths** , then excel will show **#N/A error**.

## Example

- Firstly, you have to **create a sample data set** with **variable X** and another **variable Y**.

![](https://geekexcel.com/wp-content/uploads/2021/08/Sample-data-38.png)<figcaption>Sample data</figcaption>

- Then, you need to calculate the **covariances of this data** and see how these two variables affect each other.
- Now, you have to use the Excel **COVARAINCE.P function** formula given below.

```
=COVARIANCE.P(A3:A7,B3:B7)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Covariance.png)<figcaption>COVARAINCE.P function</figcaption>

- After that, this function returns a **value of 5.444444444**.

## Interpretation of Covariance

The **covariance value** from the above example we got is a **positive value**. It tells that **X and Y** move in the **same direction**. If **covariance** was a **negative value** , then the opposite would be **true**.

## How does COVARIANCE.P get calculated?

- Firstly, if you try to calculate the **covariance of the population** in Excel **manually**.
- Then, you need to calculate the **arithmetic mean of the X and Y variables** in the cell.
- Now, you have to use the **AVERAGE function** formula given below.

```
=AVERAGE(A3:A8)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Average.png)<figcaption>Average function</figcaption>

- After that, you need to **subtract the mean of X** from **each value of X** and do the **same for Y**.

```
=A3-$A$15
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Subtract-the-mean.png)<figcaption>Subtract the mean</figcaption>

- You have to calculate the **multiple X-mean X** and **Y-mean Y range**.

```
=D3*C3
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Multiple-mean-1024x648.png)<figcaption>Multiple mean</figcaption>

- Now, you need to calculate the **sum of the values** obtained by **multiplication**.

```
=SUM(E3:E8)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Sum-of-the-value-1024x586.png)<figcaption>Sum of the value</figcaption>

- Finally, you have to **divide** the obtained **sum** with a **number of observations**. In our case, it is 6.

```
=E9/COUNT(A3:A8)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Divide-the-sum-value-1024x506.png)<figcaption>Divide the sum of the value</figcaption>

**Check this too:** [How to use the IMTAN Function in Excel office 365?](https://geekexcel.com/how-to-use-the-imtan-function-in-excel-office-365/)

## Wrap-Up

Hope you understood how to **use the Excel COVARIANCE.P Function In Excel Office 365**. Please feel free to state your query or feedback for the above article. Thank you so much for Reading!! To learn more, check out **[Geek Excel](https://geekexcel.com/)!! **and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Read Ahead:**

- **[Excel Formulas to Count Cells that contain either X or Y!!](https://geekexcel.com/excel-formulas-to-count-cells-that-contain-either-x-or-y/)**
- **[Formulas to Get the Maximum Value with Variable Columns!!](https://geekexcel.com/excel-formulas-maximum-value-with-variable-columns/)**
- **[Excel Office 365 VBA Variables & Data Types – A Complete Guide!!](https://geekexcel.com/excel-office-365-vba-variables-data-types-a-complete-guide/)**
- **[Excel Formulas to Remove the Text By Variable Position ~ Quickly!!](https://geekexcel.com/excel-formulas-to-remove-the-text-by-variable-position/)**