Statistical

VAR.S Function in Excel

Estimates variance based on a sample.

Syntax

  • =VAR.S(number1, [number2], ...)

Arguments

  • number1 (required): First value or range
  • number2 (optional): Additional values or ranges

Examples

  • =VAR.S(A1:A100) - Sample variance - Result: Variance value

Excel VAR.S default behavior (sample variance)

  • VAR.S estimates variance using a sample — Excel divides by (n−1), not n, applying Bessel's correction by default.
  • Syntax: =VAR.S(number1, [number2], ...). Accepts up to 254 arguments or a single range like =VAR.S(A1:A100).
  • This is the default choice in analytics when data is a sample of a larger population (surveys, A/B tests, batches).
  • Population variance for the full dataset uses [VAR.P](/functions/var-p/) which divides by n instead of n−1.

VAR.S vs VAR.P — which to use

  • VAR.S — sample variance; use for customer surveys, quality spot-checks, and any partial dataset.
  • VAR.P — population variance; use only when the range is the entire population (all employees, all transactions ever).
  • In practice VAR.S is more common because real-world datasets are rarely complete populations.
  • Standard deviation: [STDEV.S](/functions/stdev-s/) = SQRT(VAR.S); [STDEV.P](/functions/stdev-p/) = SQRT(VAR.P).

Understanding n−1 in VAR.S

  • Sample variance corrects upward vs dividing by n so sample spread better estimates population spread.
  • With n=2, VAR.S and VAR.P differ most; difference shrinks as sample size grows.
  • Single value: VAR.S returns #DIV/0! because n−1 = 0 — need at least two numeric points.
  • Text and logical values are ignored; blank cells are ignored; zero is a valid numeric observation.

VAR.S worked examples

  • Survey responses: =VAR.S(B2:B51) for 50 customer ratings — interpret spread around the mean response.
  • Daily returns: =VAR.S(C2:C22) for 21 trading days — pair with AVERAGE for mean return context.
  • Compare groups: =VAR.S(IF(Region="East",Amount)) as array or FILTER subset in M365 for regional spread.
  • Portfolio risk sketch: variance of returns feeds volatility estimates; finance teams often use STDEV.S for same-unit output.

VAR.S with AVERAGE and STDEV.S

  • Report trio: =AVERAGE(range), =VAR.S(range), =STDEV.S(range) for mean, variance, and standard deviation.
  • Coefficient of variation: =STDEV.S/AVERAGE when comparing relative spread across different scales.
  • Quality control: track VAR.S of batch weights over time — rising variance may signal process drift.
  • Hypothesis testing: sample variance feeds t-tests and confidence intervals in external stats tools exported from Excel.

People also ask

  • What is the default for VAR.S in Excel? — Sample variance with (n−1) denominator; ignores text and blanks.
  • VAR.S vs VAR.P? — S for sample (n−1), P for entire population (n).
  • Why is VAR.S larger than VAR.P on the same data? — Dividing by n−1 instead of n inflates variance slightly.
  • VAR.S vs legacy VAR? — VAR.S is Excel 2010+ name; old VAR assumed sample variance similar to VAR.S.

Common errors

  • Use when data is a sample of larger population

Use cases

  • Sample analysis
  • Research statistics
  • Estimation

Frequently asked questions

  • When should I use VAR.S vs VAR.P? Use VAR.S for samples (survey data, test batches, random selections). Use VAR.P only when you have data for the ENTIRE population. In most real-world scenarios, you're working with samples, so VAR.S is more common.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use VAR.S

  • Sample analysis — common Statistical scenario for VAR.S.
  • Research statistics — common Statistical scenario for VAR.S.
  • Estimation — common Statistical scenario for VAR.S.

VAR.S in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • VAR.S syntax: =VAR.S(number1, [number2], ...)
  • number1 (required): First value or range
  • number2 (optional): Additional values or ranges
  • Confirm VAR.S arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm VAR.S arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • VAR.P (/functions/var-p/): Calculates variance based on the entire population.
  • STDEV.S (/functions/stdev-s/): Estimates standard deviation based on a sample.
  • AVERAGE (/functions/average/): Returns the average (arithmetic mean) of the arguments.

Errors to watch for

  • Use when data is a sample of larger population — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter VAR.S on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

VAR.S worked examples to copy

  • =VAR.S(A1:A100) — Sample variance. Expected result: Variance value.

VAR.S reference summary for crawlers and offline review

  • VAR.S belongs to the Statistical category in Excel. Estimates variance based on a sample.
  • Full syntax: =VAR.S(number1, [number2], ...). Open /functions/var-s/ for parameters, FAQs, and related pages.
  • Common mistakes: Use when data is a sample of larger population
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.