What's the difference between VAR.S and VAR.P?

VAR.S calculates sample variance (dividing by n−1) and is used when your data is a subset of a larger population. VAR.P calculates population variance (dividing by n) and is used only when your data represents the entire population. In practice, VAR.S is more common because you almost always work with samples.

Why does my VAR.S formula return #DIV/0! when I only have one row of data?

VAR.S requires at least 2 values to compute variance. With only 1 value, the formula attempts to divide by n−1 = 0, which is mathematically undefined. Expand your range or ensure your dataset includes at least 2 numeric entries.

Can I calculate variance for only specific rows (like tickets from a particular agent)?

Yes. Use an array formula like =VAR.S(IF(D2:D7="Alex",F2:F7)) to filter by agent, then press Ctrl+Shift+Enter. Alternatively, in newer spreadsheets, use =VAR.S(FILTER(F2:F7,D2:D7="Alex")) with standard Enter.

Is variance the same as standard deviation?

No, but they're related. Variance is the average squared deviation from the mean, while standard deviation is the square root of variance. Standard deviation is usually easier to interpret because it's in the same units as your data. Use STDEV.S for standard deviation.

VAR.S function

VAR.S returns the sample variance of a dataset, measuring how spread values are around the mean using an unbiased n-1 denominator.

=VAR.S(number1, ...)

Generate a VAR.S formula

Describe what you need. The generator will reach for VAR.S where VAR.S is the right tool, and tell you when it is not.

Arguments

How VAR.S reads its arguments

Argument	Required	Description
number1	Required	Required. A number, cell reference, or range containing at least 2 numeric values. Text, logical values, and empty cells are ignored.
...	Repeating	Optional. Additional numbers, references, or ranges to include in the variance calculation. You may supply up to 254 arguments total.

Returns

A positive number (or zero if all values are identical), representing the sample variance.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Analyze customer satisfaction consistency across all tickets

Ticket ID	Priority	Opened	Closed	Agent	CSAT
TKT-001	High	2026-09-01	2026-09-02	Alex	4.8
TKT-002	Low	2026-09-01	2026-09-05	Blake	4.2
TKT-003	Medium	2026-09-02	2026-09-04	Alex	4.9
TKT-004	High	2026-09-03	2026-09-03	Charlie	3.5
TKT-005	Low	2026-09-04	2026-09-08	Blake	4.1
TKT-006	High	2026-09-05	2026-09-06	Alex	4.7

=VAR.S(F2:F7)

Result: 0.286

VAR.S calculates how the CSAT scores (4.8, 4.2, 4.9, 3.5, 4.1, 4.7) deviate from their mean of 4.37. The variance of 0.286 indicates relatively tight clustering—customer satisfaction is consistent across tickets, with scores rarely straying far from the average.

2. Measure variability in ticket resolution time across the team

Ticket ID	Opened	Closed	Days to Resolve
TKT-001	2026-09-01	2026-09-02	1
TKT-002	2026-09-01	2026-09-05	4
TKT-003	2026-09-02	2026-09-04	2
TKT-004	2026-09-03	2026-09-03	0
TKT-005	2026-09-04	2026-09-08	4
TKT-006	2026-09-05	2026-09-06	1

=VAR.S(G2:G7)

Result: 2.8

With resolution times ranging from 0 to 4 days and a mean of 2 days, the variance of 2.8 reveals significant inconsistency in how quickly tickets close. This high variance suggests process variability—some tickets resolve same-day while others take the full week, pointing to potential SLA or prioritization issues.

3. Compare CSAT variability for high-priority tickets only

Ticket ID	Priority	CSAT
TKT-001	High	4.8
TKT-004	High	3.5
TKT-006	High	4.7

=VAR.S(4.8,3.5,4.7)

Result: 0.523

High-priority tickets alone show a sample variance of 0.523—noticeably higher than the overall variance of 0.286. This signals that urgent tickets have less consistent satisfaction outcomes. The 3.5 score from TKT-004 is a significant outlier, suggesting that addressing high-priority ticket quality could improve overall satisfaction uniformity.

Common errors

Which VAR.S error are you seeing?

Error	Why it happens	How to fix it
#DIV/0!	VAR.S requires at least 2 numeric values to calculate variance. If you provide only 1 number, the formula tries to divide by n−1 = 0, which is undefined.	Verify your range includes at least 2 numeric values. For example, =VAR.S(F2:F7) requires cells F2 through F7 to contain at least 2 numbers. Remove filters or conditions that might have narrowed the data to a single value.
#VALUE!	Your range contains text that cannot be interpreted as a number, or you've mixed incompatible data types in a way that breaks numeric coercion. For instance, passing a text string like 'TKT-001' or a date formatted as text.	Ensure all values in your range are numeric. If you're including a date column, convert dates to numbers using DAYS() or similar. Remove any text columns from the range, or use a filter (array formula or FILTER function) to select only numeric columns.
#REF!	Your formula references a cell or range that has been deleted, moved, or is otherwise invalid. For example, if you write =VAR.S(A1:A10) and column A is deleted, the reference becomes broken.	Check that all ranges in your formula (e.g., F2:F7) still exist and point to the correct cells. Use the Name Manager or Edit Links dialog to repair broken references. If the data has been reorganized, update the range addresses in your formula.

Tips and when to use something else

VAR.S uses n−1 in the denominator (sample variance), while VAR.P uses n (population variance). Choose VAR.S when analyzing a sample of a larger population; use VAR.P only when your data represents the entire population.
Variance is in squared units of your data, making it hard to interpret directly. For a measure in original units, use STDEV.S (the square root of variance) instead when reporting to non-technical audiences.
VAR.S ignores logical values (TRUE/FALSE) and text. If your range includes mixed types, only numeric values are counted. This can silently change your denominator—verify the count with COUNTA to debug unexpected results.
For conditional variance (e.g., variance of CSAT only for high-priority tickets), use an array formula like =VAR.S(IF(B2:B7="High",F2:F7)) and press Ctrl+Shift+Enter, or use FILTER if available in your spreadsheet.

Related functions

STDEV.SSTDEV.S returns the sample standard deviation of numeric values, measuring how spread apart data points are from their mean.VAR.PVAR.P returns the population variance of a dataset, showing spread from the mean; use it when your data represents the entire population, not a sample.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.

Frequently asked questions

What's the difference between VAR.S and VAR.P?: VAR.S calculates sample variance (dividing by n−1) and is used when your data is a subset of a larger population. VAR.P calculates population variance (dividing by n) and is used only when your data represents the entire population. In practice, VAR.S is more common because you almost always work with samples.
Why does my VAR.S formula return #DIV/0! when I only have one row of data?: VAR.S requires at least 2 values to compute variance. With only 1 value, the formula attempts to divide by n−1 = 0, which is mathematically undefined. Expand your range or ensure your dataset includes at least 2 numeric entries.
Can I calculate variance for only specific rows (like tickets from a particular agent)?: Yes. Use an array formula like =VAR.S(IF(D2:D7="Alex",F2:F7)) to filter by agent, then press Ctrl+Shift+Enter. Alternatively, in newer spreadsheets, use =VAR.S(FILTER(F2:F7,D2:D7="Alex")) with standard Enter.
Is variance the same as standard deviation?: No, but they're related. Variance is the average squared deviation from the mean, while standard deviation is the square root of variance. Standard deviation is usually easier to interpret because it's in the same units as your data. Use STDEV.S for standard deviation.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17