VAR in Google Sheets

What Is VAR in Google Sheets?

The VAR function in Google Sheets calculates the variance of a population based on a set of values. It does so by dividing the sum of squared differences of the values by the number of data points minus one. Variance is a statistical measurement used to find how far each value is from the set’s mean and every other number. Financial analysts and traders use it to calculate volatility and market security.

Investors can calculate the variance and find the risk an investment carries to find if it is profitable. You can also compare the relative performance of each asset in a portfolio and get the best asset allocated. In the example below, we have some temperature values of a city, and we calculate their variance.

VAR-in-Google-Sheets-Definition
Key Takeaways
  1. Variance is the variability of a data set that shows how far the different values are spread from the mean VAR in Google Sheets calculates the variance of a range of numbers.
  2. The syntax of the VAR function is as follows: =VAR(number1, number2, …). Here, number1, number2, etc., are the range/values to calculate the variance for.
  3. The square root of the variance is the standard deviation. Both these together give insights on the spread of values thereby helping find trends and take appropriate measures.
  4. For instance, to understand the variability of sales figures, calculating the variance can provide valuable insights. You can also use VAR in finance to find the volatility of a portfolio’s returns by calculating the variance of the investment portfolio.

Syntax

 The syntax of the VAR function in Google Sheets is as follows:

=VAR(value1, [value2, …])

Here,

  • value1 = Mandatory value whose variance is to be found.
  • value2, and so on: (Optional) Individual values or cell references representing the dataset whose variance should be calculated.

You can include multiple values or cell references.

How to use VAR function in Google Sheets?

Manually Entering VAR in Google Sheets

To use VAR in Google Sheets effectively, follow these stepwise instructions:

Step 1: Enter the set of values in a spreadsheet. Select an empty cell where you want to enter the formula. Here, it is cell A7. Type the equals sign (=) to begin the formula and enter the VAR function.

How-to-use-VAR-function-in-Google-Sheets-Step-1

Step 2: Open a bracket and specify the range or ranges of numbers for which you want to calculate the variance. Then, close the bracket.

How-to-use-VAR-function-in-Google-Sheets-Step-2

Step 3: Press Enter to calculate the variance in Google Sheets.

How-to-use-VAR-function-in-Google-Sheets-Step-3

By following these steps, you can easily calculate the variance of any set of numbers in Google Sheets.

From the Google Sheets Menu

You can easily insert the VAR function from the Google Sheets menu.

First, go to the “Insert” menu option. From here, go to “Function” and choose “Statistical.” Choose the VAR function and enter the required arguments.

From-the-Google-Sheets-Menu

Examples

Let us look at some examples of how to use VAR in Google Sheets.

Example #1 – Assess the consistency of students’ scores

Using VAR with test scores makes it easy to understand the variance of test scores, which can help access the spread of scores within a group. It also helps in understanding the diversity of the students’ performance. In this example, we have the scores of 10 students in a batch. Let us calculate the variance in their scores.

Step 1: Let us organize the scores in a table. Here, the scores are in column B, from B2 to B11.

Example-1-Step-1

Step 2: Click on an empty cell, B12, to display the variance result. Use the VAR function to calculate the variance of the scores.

=VAR(B2:B11)

Example-1-Step-2

Step 3: Press Enter. The result will give you the variance of the student’s scores. You must check the result to analyze the performance using variance.

Example-1-Step-3

A higher variance, as shown here, indicates more spread-out scores. A lower variance indicates that the scores are found to be more around the mean.

For example, let us change some of the scores and recheck the variance. Here, we raised all the marks to above 75. The variance lowers, as shown below.

Example-1-Step-3-1

Thus, it proves that the performance is more consistent in the second scenario and shows a level of uniformity in their performance.

This measure helps in evaluating the effectiveness of teaching or the difficulty level of the test.

Example #2 – Evaluate salary distribution within a department

Below is another example of the salaries of different department employees. The variance will help you understand how much salaries vary from the average in that department.

VAR-in-Google-Sheets-Example-2

Step 1: To calculate the variance, let us apply the VAR function in cell B12 as follows:

=VAR(B2:B11)

Example-2-Step-1

Step 2: The STDEV can be calculated as follows in cell B13.

Example-2-Step-2

A higher variance, as in this case, indicates a greater spread in salaries. The standard deviation value indicates that the salaries typically deviate from the average salary by about $1482.68.

It helps you understand salary dispersion and can be helpful in addressing salary disparities, planning budgets, or ensuring equitable compensation practices.

Example #3 – Analyze variation in monthly sales figures

Calculating the variance of sales data is useful for analyzing the consistency of sales performance or for determining its variability.

Step 1: In this example, we have the sales data in cells B2 to B7 for a semi-annual period.

Example-3-Step-1

Step 2: To calculate the variance, use the formula =VAR(B2:B7).

 Using the VAR function in Google Sheets, you can determine how much the sales figures deviate from the average. This provides valuable insights into how the sales are volatile over the six-month period.

Example-3-Step-2

Step 3: To facilitate the interpretation of the sales, let’s calculate the standard deviation using STDEV.

Example-3-Step-3

It provides the average deviation from the mean sales.

You can similarly calculate the standard deviation and variance for different time periods, which helps you make adjustments and improvements based on sales performance.

Example #4 – Measure fluctuation in stock levels over time

Let us use the VAR formula in Google Sheets to calculate the variance of some stock values over time and check the monthly stock values of a particular item over a year.

VAR-in-Google-Sheets-Example-4

Step 1: We input the data into Google Sheets, as shown above. Now, let us calculate the variance. Go to Cell B14 and enter the following formula

=VAR(B2:B13)

Example-4-Step-1

Step 2: Press Enter. We get the variance of the stock levels.

Example-4-Step-2

The result shows the variance of the stock levels. As discussed earlier, a higher variance means a greater fluctuation in stock levels, while a lower variance shows a lower fluctuation in stock levels. For a more in-depth analysis, let us calculate the standard deviation.

Step 3: Apply the function =STDEV(B2:B13) in cell B15.

Example-4-Step-3

The result indicates that stock levels typically deviate from the average by around 16.58 units.

Thus, you can monitor  your stock levels over time, which helps in inventory management.

Important Things to Note

  1. Arguments containing instead of numbers cause #VALUE! Errors.
  2. In Google Sheets, a maximum of 255 arguments corresponding to a sample or population can be supplied to VAR.
  3. Combine VAR in Google Sheets with other functions like AVERAGE and STDEV, which will help you gain more comprehensive insights.
  4. A value of zero means there is no variability. Here, all the numbers in the data set are the same. The bigger the result of variance, the more spread out the data.
  5. If the number of values supplied as value is not at least two, you get the #DIV/0!​ Error

Frequently Asked Questions (FAQs)

1. What are the different versions available for VAR in Google Sheets?

There are different versions of functions for variance depending on the type of dataset you are using.
VARA – To calculate variance while interpreting any text values as zero for a sample, we can use VARA.
VARP – To calculate variance for a entire population, use VARP.
VARPA – This is a useful version of VAR that calculates the variance based on an entire population while setting text to the value `0`.
VARA: It calculates the variance of a sample while setting text to the value `0`.
VAR.S – It is equivalent to the VAR function, but a newer version, that calculates a sample’s variance.
VAR.P – It is equivalent to the older VARP function and calculates the variance of an entire population.

2. What errors occur when using VAR in Google Sheets?

When using the VAR function or its equivalents in Google Sheets, you may encounter some common errors.
#DIV/0! Error: This error occurs if there are not enough values to calculate the variance. VAR and VAR.S require at least two values, while VAR.P requires at least one.
#VALUE! Error: Like with any other function, we get this error happens if the input range contains non-numeric values
#REF! Error: If the formularefers to cells that have been deleted, you may get this error. So, be careful in choosing the range.
#NUM! Error- It usually occurs if the function calculation exceeds the allowable range.

3. What are the uses of the VAR function in Google Sheets?

1. VAR is very useful in financial analysis, quality control, and other fields of research where understanding data variability is necessary.
2. In finance, you can use it to understand the variability of sales figures and to identify areas for improvement.
3. You can use VAR to analyze data in finance where the variance of an investment portfolio helps financial analysts assess the volatility of the portfolio’s returns.
4. In research, variance helps analyze experimental data and determine the reliability of results. It also helps make accurate conclusions about scientific advancements.

Download Template

This article must help understand VAR in Google Sheets with formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is VAR in Google Sheets. We learn how to use it to find the variance in Google Sheets with detailed examples. You can learn more from the following articles.

Table in Google Sheets

Split Cells in Google Sheets

NPER Function in Google Sheets

Reader Interactions

Leave a Reply

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