How to use Excel COVARIANCE.S function in Excel Spreadsheet?
Tags: touseexcelcovariance, excel, excelfunctions
Published: 2021-08-27 12:19:46

---
title: How to use Excel COVARIANCE.S function in Excel Spreadsheet?
published: true
date: 2021-08-26 16:33:55 UTC
tags: ToUseExcelCOVARIANCE,Excel,ExcelFunctions
canonical_url: https://geekexcel.com/how-to-use-excel-covariance-s-function-in-excel-spreadsheet/
---

In this article, you will learn how to use **Excel COVARIANCE.S Function In Excel Spreadsheet** 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.S Function

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

## COVARIANCE.S Function Syntax

```
=COVARIANCE.S(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-37.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.S function** formula given below.

```
=COVARIANCE.S(A3:A8,B3:B8)
```

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

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

## 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.S get calculated?

- Firstly, if you try to calculate the **covariance of the sample** in Excel **manually** , this is how you would do it.
- 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-function-1.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-mean-1024x826.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-X-mean-X-and-Y-mean-Y-range-1024x535.png)<figcaption>multiple X-mean X and Y-mean Y range</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-values-1024x546.png)<figcaption>Sum of the values</figcaption>

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

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

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

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

## Sum-Up

In the above short tutorial, we have illustrated **how to use the Excel COVARIANCE.S Function In Excel Spreadsheet**. Hope you like this article. 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/) **!!**

**Related Articles:**

- **[Excel Office 365 VBA Variables & Data Types – A Complete Guide!!](https://geekexcel.com/excel-office-365-vba-variables-data-types-a-complete-guide/)**
- **[Get the Current Worksheet Name Only in Excel Office 365!!](https://geekexcel.com/get-the-current-worksheet-name-only-in-excel-office-365/)**
- **[Formulas to Count Cells Either X or Y In Excel Office 365!!](https://geekexcel.com/formulas-to-count-cells-either-x-or-y-in-excel-office-365/)**
- **[Excel Formulas to Sum if Cells Contain Either X or Y!!](https://geekexcel.com/excel-formulas-to-sum-if-cells-contain-either-x-or-y/)**
- **[Excel Formulas to Sum the Values in the cells that contain both X and Y!!](https://geekexcel.com/excel-formulas-to-sum-the-values-in-the-cells-that-contain-both-x-and-y/)**