How to use COVARIANCE.S Function in Excel

COVARIANCE.S function in excel is a statistical function. This function is used to find the sample co-variance of two data sets. For example it can be used to keep  check on how to assets are moving together. This is a worksheet function and can be entered into the cell in which count is required.

Syntax:= COVARIANCE.S(array1,array2)

The COVARIANCE.S function syntax has the following arguments:

  1. Array1 (required argument) – This is the first array of numeric values.
  2. Array2 (required argument) – This is the second array of numeric values.

Example: Let’s look at some Excel COVARIANCE.S function examples and explore how to use the COVARIANCE.S function as a worksheet function in Microsoft Excel:

Suppose we are given data about the weekly returns of Stock A and percentage of change in the market index (NASDAQ):

COVARIANCE.S Function - How to use COVARIANCE.S Function in Excel

Let’s use the function to understand if there is any covariance between the stock returns and NASDAQ returns.

Syntax:  =COVARIANCE.S(B2:B20,C2:C20)

Result: 0.000684099

COVARIANCE.S Function in Excel - How to use COVARIANCE.S Function in Excel

The result indicates that there exists a positive correlation between the two.

Note:

  • The arguments must either be numbers or be names, arrays, or references that contain numbers.
  • If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
  • If array1 and array2 have different numbers of data points, COVARIANCE.S returns the #N/A error value.
  • If either array1 or array2 is empty or contains only 1 data point each, COVARIANCE.S returns the #DIV/0! error value.

Add a Comment

Your email address will not be published. Required fields are marked *