What Is VAR.S in Google Sheets?
The VAR.S function in Google Sheets is a statistical function used to calculate the variance of a data sample. Basically, variance measures how much a set of data is spread out from the average value. This analysis helps make critical decisions based on the amount of variation in the data. Thus, with variance, we know how far each value is from the mean and every other number in the set. In finance, it is very useful to calculate volatility and market security.
Google Sheets has a few VAR functions to calculate a data set’s variance. The most important ones apart from the regular VAR in Google Sheets include VAR.P, which calculates the variance of an entire population, and VAR.S, which calculates the variance of a sample. The VAR assumes a sample variance by default. In the example below, consider you have the sample data in cells A1:A6. Use the following function to calculate the variance of the sample data =VAR.S(A1:A6).

Key Takeaways
- The VAR.S function in Google Sheets calculates the variance of a set of values that are a sample rather than the entire population.
- A higher variance indicates a greater variance of values from the mean, while a lower variance suggests less variability.
- The syntax of VAR.S is as follows:
- =VAR.S(value1, [value2, …])
- value1, value2, – individual values or ranges of cells
- You can include up to 254 arguments.
- The VAR.S function in Google Sheets is commonly used in financial research and data analysis besides quality control processes.
Syntax
The syntax of the VAR.S function in Google Sheets is shown below. Here, it has one mandatory parameter and one optional parameter:
=VAR.S(value1, [value2, …])
Arguments:
- value1 (mandatory):
The value or range for which you want to calculate the variance. It can be numbers, arrays, or references to cells containing numbers.
- value2, … (optional):
The additional values or ranges for the calculation of the variance. The function can take a maximum of 254 arguments.
How To Use VAR.S function in Google Sheets?
Manually Entering VAR in Google Sheets
To use VAR.S function in Google Sheets effectively, follow these instructions explained with a simple example:
Step 1: Enter the required values in a spreadsheet as shown below. Here, we have the salary details of a few employees as a sample for a large organization.

Step 2: Select an empty cell where you want to enter the formula. Type the equals sign (=) to begin the formula, enter the VAR.S function, and open the braces.

Step 3: Specify the range of the sample for which you want to calculate the variance. Then, close the bracket.

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

By following these steps, you can easily calculate the variance of any sample in Google Sheets.
From the Google Sheets Menu
You can insert the VAR.S function from the Google Sheets menu.
First, go to the “Insert” menu, and then go to “Function” and choose “Statistical.” Choose the VAR.S function and enter the required arguments.

Difference Between Sample and Population Variance
There are two types of variances in statistics: sample variance and population variance. Sample variance is used when analyzing a subset of the total dataset, basically a sample, while population variance is used for calculations on the entire dataset. Using Google Sheets, we can calculate both with separate functions, thereby offering high flexibility.
Examples
The VAR.S function calculates variance based on a sample. Let us look at some interesting examples of how to use VAR in Google Sheets.
Example #1
Let us look at the daily sales report for the sale of some branded toys at stores across the US. You must calculate the sample variances of the daily sales figures using VAR.S. Once you get the results for each location, you can quantify the variability in the daily sales across locations and compare and understand the volatility of the product’s performance.

Step 1: Apply the following formula in cell B12 to find the sample variance of Phoenix stores over 10 days.
Enter =VAR.S(B2:B11) in cell B12.

Step 2: For the other two cities, drag the formula across the row to cell C12 and D12.

The Dallas store shows the highest variance here, which indicates a more unpredictable performance than the other two.
The Phoenix store shows the most stable performance, while Lansing is entirely predictable, too.
Calculating the variance provides insights into the fluctuations in sales figures. The high shows that the product’s sales are inconsistent, with daily fluctuations. A low variance suggests stable sales patterns.
Example #2
Let us examine the details of ratings of employees in an organization based on various factors to decide their increments and promotions. Assuming that this is a sample, let’s calculate the variance.

Step 1: Let us calculate the variance in the rating.
Apply formula =VAR.S(B2:B9) in cell B10. Press Enter.

The lower variance indicates that the productivity of the employees is quite stable and can be somewhat predicted.
Calculating the variance allows you to assess whether the employees’ performance is variable or consistent. This helps the company decide on appropriate training and other strategies to improve performance.
Example #3
In this example, we use variance to analyze the performance of a portfolio of stocks. We use the VAR.S function to calculate the variance of the stock returns over a specific period. Look at the table below.

Step 1: The monthly returns for each stock for a period of six months are as shown above.
Now, let us calculate their variance. Use the following formula in cell B8.
=VAR(B2:B7)

Step 2: Now, drag the formula to cell C8 for the variance of Stock B.

Stock A has a higher variance at 0.13, which shows a greater fluctuation in returns. It indicates a higher risk, while Stock B offers more stable returns.
This information helps you gain an understanding of which is a riskier investment, with ups and downs.
Difference betwen VAR.S and VAR.P Functions
Variance describes the spread of a data distribution. It is used to measure the distance of the data points from the dataset’s mean.
There are two different formulas to calculate the variance of a data set.
VAR.S
- The VAR.S Function calculates the variance of a dataset if it is from a sample of a population.
- We use the VAR.S Function when the dataset represents a sample of a population. The findings can then be used to generalize the population. It happens most of the time as most populations are too large to calculate the variance, and a sample is often used. It ignores logical values and text in the sample.
- The VAR.P Function is used to calculate the variance of a dataset if it is from the entire population.
- We use the VAR.P Google Sheets formula when the dataset represents all the data points of the population we are researching on.
Important Things To Note
- VAR.S only evaluates numbers. It ignores empty cells, text, and logical values.
- However, it So, the function will ignore a logical value TRUE when it is in a range like A2:A10 but will consider the TRUE value when it is passed as an argument.
- VAR.S assumes that the arguments are a sample of data, not the entire population. For the entire population, use VAR.P.
- Arguments can either be numbers or names, arrays, or references that contain numbers.
- To evaluate logical values or text in references as zero, use the VARA function.
- The VAR.S function can be used to compare the spread of data between different samples.
Frequently Asked Questions (FAQs)
1. An important point to note is that VAR.S calculates the variance of a sample, not the entire population. Knowing the difference is essential because using VAR.S on an entire population can lead to unpredictable results.
2. You should use the VAR.P function in Google Sheets if your data represents the whole population.
3. The variance calculated using VAR.S measures the variation of the values from the dataset’s mean. It is important that you correctly interpret the result for optimal usage. A high variance means that data points are spread out widely, while a low one means they are close to the mean.
Calculating the standard deviation
The standard deviation calculates the spread of a set of values from the mean. It is another measure of the distribution of the statistical data. The variance is a measure of how the data points vary from the mean. In Google Sheets, we calculate the variance using the VAR.S function, while the standard deviation is calculated by taking the square root of the variance.
Empty cells, logical values, text, or error values supplied as part of an array are ignored. If they are supplied directly to the function, text representations of numbers and logical values are interpreted as numbers.
Download Template
This article must help understand VAR.S in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is VAR.S in Google Sheets. We learn its syntax and how to use VAR.S to find the variance with detailed examples. You can learn more from the following articles.
Leave a Reply