What Is VAR.S Excel Function?
The VAR.S Excel function, short for Variance of a Sample, is a statistical tool designed to calculate the variance of a data set. Unlike the VAR.P (Variance Population) function, which considers the entire population in its calculations, VAR.S focuses solely on a sample of the data. This makes it particularly useful when dealing with large datasets where examining the entire population may be impractical.
By using this function, professionals can analyze and understand the dispersion or spread of their sample dataset more accurately. The resulting value signifies how much each data point deviates from the mean, allowing for a better assessment of risk and uncertainty. As an essential component in data analysis, VAR.S empowers practitioners across various fields, such as finance, market research, and quality control, to make informed decisions based on more accurate measures of variability.
Assuming we have been provided with the financial figures pertaining to a business, as illustrated below:
Our primary goal is to ascertain and present the variance for the provided data, which can be located in cell B2.

To calculate the variance, you can employ the following formula: =VAR.S(A2:A8)
The resulting values are as follows:

Table of contents
Key Takeaways
- VAR.S Excel function is a statistical tool used to calculate the variance of a data set. It focuses on a sample of the data rather than the entire population, making it useful for large datasets.
- It helps professionals understand the spread of their sample dataset, allowing for a better assessment of risk and uncertainty.
- Professionals can assess risks associated with financial investments or gauge variation in manufacturing processes, enabling them to make informed decisions based on reliable statistical analysis.
- The VAR.S function is essential in data analysis and enables informed decision-making in fields like finance, market research, and quality control.
Syntax

The VAR.S function incorporates the following arguments:
Number 1 – This is a required argument. This parameter signifies the primary argument that corresponds to a sample extracted from a population.
Number 2,… This is an optional argument. In this scenario, the number of arguments can encompass a maximum of 254 values or arrays of values, guaranteeing the provision of at least two values to facilitate the optimal functioning of the function.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use VAR.S Function In Excel? (With Steps)
#1 – Access To VAR.S From The Excel Ribbon
Step 1: Choose the empty cell which will contain the result.
Step 2: Go to the Formulas tab and click it.
Step 3: Select the More Functions option from the menu.
Step 4: Select the Statistical option from the menu.
Step 5: Select the VAR.S option from the drop-down menu.

Step 6: A window called Function Arguments appears.
Step 7: As the number of arguments, enter the value in the Number 1 and Number 2.
Step 8: Select OK.

#2 – Enter The Worksheet Manually
Select an empty cell for the output.
Type =V( in the selected cell. Alternatively, type =V and double-click and then the VAR.S function from the list of suggestions shown by Excel.
Press the Enter key.
Examples
Example #1
Suppose we are provided with the monthly sales figures of a business for the entire year, as displayed below:
The objective is to determine and display the variance for the given data in cell B14. However, there might be confusion regarding whether to apply the VAR.P or VAR.S Excel function in the target cell.
In this particular example, the given data represents a sample of the entire dataset or population, indicating that we should utilize the VAR.S Excel function.

Step 1: Select cell B14 and input the VAR.S() function.
=VAR.S(B2:B13)

Step 2: Press Enter to execute the VAR.S Excel function in the target cell and obtain the required variance of the sample data, which is $1,30,00,000.00.

Example #2
Suppose we are provided with the monthly sales figures of a business for the past two years, as displayed below:
The objective is to determine and display the variance for the given data in cell B14. However, there might be confusion regarding whether to apply the VAR.P or VAR.S Excel function in the target cell.
In this particular example, the given data represents a sample of the entire dataset or population, indicating that we should utilize the VAR.S Excel function.
To calculate the variance, follow these steps:

Step 1: Select cell B14 and input the VAR.S() function.
=VAR.S(B2:B13, D2:D13)

Step 2: Press Enter to execute the VAR.S Excel function in the target cell and obtain the required variance of the sample data, which amounts to $21,31,386.33.

Example #3
The image below displays a sample dataset that includes a list of employees and their attendance data. Our objective is to determine and display the variance for this data in cell B8. However, there may be some confusion regarding whether to apply the VAR.P or VAR.S Excel function in the target cell.
In this specific example, the given data represents a sample of the entire dataset or population. Therefore, it is appropriate to utilize the VAR.S Excel function.
To calculate the variance, follow these steps:

Step 1: Select cell B8 and enter the VAR.S() function.
=VAR.S(B2:B7)

Step 2: Press Enter to execute the VAR.S Excel function in the target cell and obtain the required variance of the sample data, which values 400.

Important Things To Note
- The occurrence of the #DIV/0 error is triggered when we fail to provide at least two numeric values.
- The #VALUE! error arises when any of the values directly inputted into this function are text values that cannot be interpreted as numbers.
- The VAR.S function assumes that the arguments represent a sample of data rather than an entire population. In cases where the data represents the entire population, it is more appropriate to utilize the VAR.P function.
- To effectively evaluate logical values and text in references, it is recommended to employ the VARA function.
Frequently Asked Questions (FAQs)
Assuming that we have been provided with the price of items, as shown below:
To determine and present the variance for the given data, which can be found in cell B7.
To calculate the variance, you can utilize the following formula: =VAR.S(B2:B6)
The resulting values are as follows:
• Firstly, the VAR.S function assumes that the sample data provided is truly representative of the entire population. If the sample is biased or not properly selected, it can lead to inaccurate results.
• Additionally, this function requires numerical data and cannot handle text or non-numeric values. It also does not account for missing or incomplete data points, potentially skewing the results if not addressed beforehand.
• The VAR function assumes that the dataset represents an entire population, resulting in the numerator of its formula being divided by n (population size). On the other hand, the VAR.S function considers the dataset as a sample from a larger population, thus scaling down the division factor to n-1.
This distinction arises due to statistical principles – dividing by n-1 rather than n helps compensate for potential sampling errors in smaller samples and provides more accurate estimates for population variances.
Download Template
This article must help understand the VAR.S function in Excel examples. We can download the template here to use it instantly.
Recommended Articles
This has been a guide to VAR.S Excel. Here we discuss how to use VAR.S function in Excel with examples and downloadable excel template. You can learn more from the following articles –
Leave a Reply