What is Covariance Function in Google Sheets?
In statistics, the Covariance measures the relationship between two random variables and shows if they change together. Alternately, it defines the changes between the two variables, where a change in one variable is equal to a change in another variable. The covariance in Google Sheets is calculated using the COVAR function.
To calculate covariance in Google Sheets, you can use the COVAR function. In case we look at an example of the relationship between income and purchase at any shop. If the covariance is positive, it suggests that as income increases, sales tend to increase. If the value is nearer zero, it means there is no correlation. We use the function shown below to find the relationship between the datasets in Column A and Column B.
Key Takeaways
- The Covariance measures the relationship between two random variables and checks if they change in the same direction.
- The covariance in Google Sheets is calculated using the COVAR function.
- The syntax of the COVAR function is as follows:
=COVAR(data_y, data_x)
- The result of the function can be broken down into three categories: positive, negative, and zero covariance.
- Positive covariance indicates that both variables move in the same direction while a negative covariance means the variables are inversely related.
COVAR() Syntax
The syntax of the COVAR function is as follows:
=COVAR(data_y, data_x)
- data_y – the range of values or references that consists of dependent data.
- data_x – the range of values or references that consists of independent data.
How to Use COVAR Function in Google Sheets?
We can use the covariance to determine the relationship between two data sets. In Google Sheets, you can use the COVAR function as follows:
- Manually enter the COVAR function
- Through the menu bar
Let us look at how to do it manually.
Step 1: The first step is to prepare your data. Take a new spreadsheet. Enter the two sets of numerical data in two separate columns. Here, we take an example of the hours of study and grades of a student, and if the variables affect each other, and if so, how!
Label the columns as “Hours of study” for column A and “Grades” for column B.
Step 2: To calculate covariance in Google Sheets, we use the COVAR function. This function directly computes the covariance between two data sets.
Type =COVAR(A2:A7, B2:B7) in cell C2.
Step 3: Press Enter, and we get the covariance.
The COVAR function is very simple to use and does all the complex calculations of how much the two variables change together.
- Positive Covariance: If the result is positive, it indicates that as the hours of study increase, the grades tend to increase (they move in the same direction).
- Negative Covariance: If the result is negative, it indicates that as hours of study increase, the grades tend to decrease (they move in opposite directions).
- Zero or Near Zero Covariance: If the covariance is close to zero, it suggests that the two variables have no linear relationship.
As seen above, we have a positive covariance which shows that as the study hours increase, the grades also increase.
Examples
Let us look at some practical examples to understand the use of the COVAR function in Google Sheets.
Example #1 – Analyze Stocks of Tesla and Apple Over a Period of Time
In this example, let us compare the weekly stock values of Tesla and Apple for the time period from Jan 1st, 2024, to March 31st, 2024. Let us first prepare the data for the same. Follow these steps to prepare the data.
Step 1: To retrieve stock data using Google Sheets’ GOOGLEFINANCE function, we have the following syntax:
=GOOGLEFINANCE(“ticker”, “attribute”, “start_date”, “end_date”, “interval”).
The stock ticker symbol (e.g., “TSLA” for Tesla, “AAPL” for Apple).
Select cell A1 and type the following function for the stock details of Tesla:
=GOOGLEFINANCE(“TSLA”, “close”, DATE(2024, 1, 1), DATE(2024, 3, 31), “WEEKLY”).
Step 2: Select cell C1 and type the following function for the stock details of Apple.
=GOOGLEFINANCE(“AAPL”, “close”, DATE(2024, 1, 1), DATE(2024, 3, 31), “WEEKLY”).
Step 3: Choose the cell for the covariance result that should appear.
Type the following function in the cell.
=COVAR(B2:B14,D2:D14)
Press Enter.
If the covariance value is positive, it means the two data sets tend to move in the same direction. When one increases, the other increases as well (e.g., both stocks rise together).
If the covariance value is negative, it means the two data sets tend to move in opposite directions. When one increases, the other tends to decrease (e.g., when Tesla’s stock rises, Apple’s stock might fall).
A zero or near-zero covariance indicates that there is no linear relationship between the two variables.
Here, the positive covariance shows that, the two stocks move in the same direction, as expected!
Example #2 – Analyze if Posting More Frequently on Instagram Leads to Higher Engagement
In this example, let us compare some statistical values to find if posting more frequently on Instagram leads to higher engagement. It can be done using the COVAR function. The steps are listed below.
Step 1: Let us enter the details in a Google Sheet.
- Column A: Number of posts per week.
- Column B: Engagement which includes likes and comments. We collect the data over 10 weeks.
Let’s assume you collect data for 10 weeks.
Step 2: Now, we’ll use the COVAR function to calculate the covariance between posts per week and engagement.
=COVAR(A2:A11, B2:B11)
This will give you the covariance of the data, which shows the relationship between the number of posts and the engagement.
Step 3: If the covariance value is positive, it means that as you post more frequently, engagement tends to increase; if the covariance value is negative, it suggests that posting more frequently might lead to lower engagement. A covariance value of zero or near zero suggests that there is no linear relationship between posting frequency and engagement.
Example #3 – Check if High Temperature Leads to Higher Ice Cream Sales
Here, let us compare the values of temperatures and if it leads to higher ice cream sales using some data. It can be done using the COVAR function. The steps are listed below.
It can be done using the COVAR function. The steps are listed below.
Step 1: Let us enter the details in a Google Sheet.
Column A contains the temperature in Fahrenheit and Column B contains the sales of ice creams.
Step 2: Now, we’ll use the COVAR function to calculate the covariance between the two parameters.
=COVAR(A2:A8, B2:B8)
This will give you the covariance of the data, which shows the relationship between the temperature and the sale of ice creams.
If the covariance value is positive, it means that as you post more frequently, engagement tends to increase; if the covariance value is negative, it suggests that posting more frequently might lead to lower engagement. A covariance value of zero or near zero suggests that there is no linear relationship between posting frequency and engagement
Important Things to Note
- You can also use the COVARIANCE.P function to calculate population covariance, or the COVARIANCE.S function to calculate sample covariance.
- Covariance is useful because it helps you understand the direction of the relationship between two variables.
- If an array or reference contains values that are not numbers such as text, empty cells, or logical values, they are ignored.
- If the two array arguments have different numbers of data points, the COVAR function returns the #N/A error value.
Frequently Asked Questions (FAQs)
Covariance is a statistical measure that finds the degree to which two variables change together. It is used to find how two variables are related to each other and whether they decrease or increase together. Google Sheets calculates the covariance between two data sets and can be found using the COVAR function.
There are two different variations of this function. One is COVARIANCE.P, which returns the covariance for an entire population; the other is COVARIANCE.S, which provides the covariance for a sample.
Based on the output of the COVAR function, we can analyze the relationship between two data sets.
There are three types of outcomes: positive, negative, and zero covariance.
1. A positive covariance means that both variables in the datasets will simultaneously experience a general increase or decrease.
2. A negative covariance means that the two variables are inversely related. The variables share an inverse association.
3. If the covariance is near zero or zero, there is no linear relationship between the two variables; hence, there is no association between the changes in one variable and the changes in the other variable.
Download Template
This article must help understand Covariance Function in Google sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Covariance Function in Google Sheets. We learn how to use the COVAR function to find the covariance with examples. You can learn more from the following articles. –
Leave a Reply