Two teams can have the same average delivery time and very different results. One finishes most jobs near the average; the other mixes quick jobs with long delays. Variance measures that spread. To calculate it correctly, first decide whether your numbers describe the entire population you care about or a sample used to estimate a larger population.
To calculate variance, find the mean, subtract it from each value, square each difference, add the squared differences and divide by the appropriate denominator. Divide by the population size for population variance, or by the sample size minus one for the usual sample variance estimate.
This guide works through both calculations using the same five values, then shows the Excel and Google Sheets formulas. It covers statistical variance. Budget variance, meaning the difference between actual and planned amounts, is a different calculation.
1. What variance measures
Variance describes how spread out numerical observations are around their mean. It uses squared deviations, so observations farther from the mean contribute more strongly. A variance of zero means that all values in the finite dataset are identical. A larger value means more spread when you compare comparable data measured on the same scale.
Why the average is not enough
Consider two sets: 5, 5, 5 and 1, 5, 9. Both have a mean of 5. The first has no spread, while the second has values four units below and above the mean. The average alone hides that difference. Variance gives a numerical summary of it.
The NIST handbook on measures of scale explains that squaring gives more weight to observations farther from the center. This is useful, but it also makes variance sensitive to extreme values. A single unusual observation can change the result substantially.
Our introduction to decision-making with data insights gives broader context for using numerical summaries. The calculation here is a foundation, not a complete decision model. Before interpreting it, ask what each observation represents and whether the comparison is meaningful.
State what one row represents
One row might be one delivery, one measurement or one independent test. If a row instead summarizes many observations, applying the simple formula to those summaries answers a different question. Make the observation unit explicit in the report. That small note can prevent a correct calculation from being mistaken for a statement about individual outcomes.
Variance is not a percentage
If the original measurements are minutes, variance is expressed in minutes squared. It is not an average delay in minutes and not a percentage difference. Taking the square root produces standard deviation, which returns to the original measurement units. Keep the two measures separate when writing a report.
2. Population variance versus sample variance
A population is the complete set of observations defined by your question. A sample is a subset used to learn about a larger population. The distinction is about the scope of the analysis, not whether the spreadsheet has many rows.
Define the question before the formula
If you want to describe all five deliveries completed by a shop on a particular day and you have all five, those observations are the population for that narrowly defined question. If those deliveries are a sample used to estimate variability across the shop’s future or wider deliveries, use the usual sample variance calculation and consider how representative the sample is.
Having every row in an exported file does not automatically mean you have the entire relevant population. The export may omit failed transactions, older records or observations from another location. Define the inclusion rules and check the source before selecting the denominator.
OpenStax’s explanation of spread distinguishes the population calculation, which divides by N, from the sample calculation, which divides by n minus one. Our page on where data comes from illustrates why provenance should be visible alongside analysis.
Why the sample denominator is smaller
When a sample mean is calculated from the same observations, the deviations around that mean are constrained: they add to zero. The usual sample variance uses n minus one to correct the downward bias that would arise from dividing those squared deviations by n when estimating population variance under the standard sampling assumptions.
This correction does not repair a biased sample, inconsistent measurements or missing records. Five convenient observations do not become representative because a formula uses the right denominator. Our data science learning overview discusses the broader skills behind sound analysis.
3. The formulas and symbols explained
For an unweighted finite population, the formula is:
Population variance: σ² = Σ(xᵢ − μ)² / N
For the usual unweighted sample variance estimate, the formula is:
Sample variance: s² = Σ(xᵢ − x̄)² / (n − 1)
The notation is compact, but each part has a simple role:
- xᵢ: one observation in the dataset.
- μ: the population mean.
- x̄: the sample mean.
- Σ: add the terms for all observations.
- N or n: the number of observations, for a population or sample respectively.
- σ² or s²: the resulting population or sample variance.
Use one consistent set of observations
The values used to calculate the mean must be the same values used for the deviations, squared deviations and count. If a spreadsheet formula skips text in one place while a manual count includes it, the result can be wrong even if the arithmetic looks tidy.
For a simple finite dataset, population variance can be calculated with at least one numerical observation; a single observation has variance zero. The usual sample variance needs at least two because n minus one must be positive. Software can impose its own input requirements, so check the function documentation rather than assuming every tool handles the one-value case identically.
When these formulas are not enough
The formulas above treat each observation equally. Weighted observations, frequency tables, complex survey designs and probability distributions need appropriate versions of the calculation. Do not put a frequency column into the same range as measured values and expect the ordinary function to infer the weights.
A collection tool also affects the data you receive. Our Rowform overview covers a form-based workflow, while our data privacy page addresses handling information responsibly. Whatever tool you use, keep measurement values separate from identifiers and descriptive text.
4. How to calculate variance step by step
Use five fictional measurements: 2, 4, 6, 8 and 10 minutes. The small values make every step easy to check. They are an illustration, not a claim about a real business.
- Count the observations. There are five.
- Find the mean. Add 2 + 4 + 6 + 8 + 10 = 30, then divide by 5. The mean is 6 minutes.
- Subtract the mean from each value. The deviations are −4, −2, 0, 2 and 4 minutes.
- Square each deviation. The squared deviations are 16, 4, 0, 4 and 16 minutes squared.
- Add the squared deviations. Their sum is 40 minutes squared.
- Choose the denominator. Divide by 5 for population variance or by 4 for sample variance.
Why not average the ordinary deviations?
The ordinary deviations add to zero: −4 − 2 + 0 + 2 + 4 = 0. Positive and negative differences cancel. Squaring prevents cancellation and gives more weight to larger differences. It is the squared deviations that enter the variance calculation, not the ordinary signed deviations.
The population result is 40 / 5 = 8 minutes squared. The sample result is 40 / 4 = 10 minutes squared. Neither is an arithmetic error. They answer different versions of the question about the same values.
Keep full precision while working and round the final reported value to a sensible level. For datasets with fractional means, rounding the mean early changes all later deviations. Write down the denominator and units with the answer so another reader can reproduce it.
For operational data, our guide to employee time-tracking tools can help identify how durations are collected. A duration used in a calculation still needs a consistent definition, such as elapsed time versus active work time.
5. A worked example you can check row by row
The table below preserves the intermediate calculations. Use it to check a manual answer or a spreadsheet formula. The mean is 6 for every row.
| Value, minutes | Deviation from 6 | Squared deviation, minutes² |
|---|---|---|
| 2 | −4 | 16 |
| 4 | −2 | 4 |
| 6 | 0 | 0 |
| 8 | 2 | 4 |
| 10 | 4 | 16 |
| Total: 30 | Total: 0 | Total: 40 |
On a small screen, swipe the table sideways. The key results are sum of squared deviations 40, population variance 8 and sample variance 10.
Compare spread without changing the mean
Now compare the original set with 4, 5, 6, 7 and 8. The second set also has mean 6, but its squared deviations are 4, 1, 0, 1 and 4. They sum to 10, giving population variance 2 and sample variance 2.5. The second set is more tightly grouped around the same average.
This comparison illustrates why a report should show the mean and a spread measure together. It does not explain why the values differ. Investigate whether the process, measurement method or mix of cases changed before drawing a business conclusion.
For presenting recurring metrics, our Databox overview covers a reporting tool. A dashboard should preserve the calculation’s scope and units instead of displaying a bare variance number without context.
Keep the comparison question visible
When a dashboard combines several sources, check that the measurement still means the same thing in every row. A delivery duration recorded by an employee may differ from a timestamp difference generated by an order system. Our guide to choosing ERP software covers the wider system choice. For this calculation, the priority is consistent inputs and an agreed scope, not the software brand.
Use an independent check
For an important analysis, calculate the answer a second way. A manual deviation table can be compared with a documented spreadsheet function. Confirm that both methods use exactly the same observations and sample/population choice. Agreement is reassuring, but it does not establish that the underlying records are complete or representative.
6. Variance formulas in Excel and Google Sheets
Put 2, 4, 6, 8 and 10 into cells A2 through A6, one numerical value per cell. Keep the heading outside that range. These examples assume ordinary unweighted observations and an English-language function format.
Excel
Use =VAR.P(A2:A6) for population variance, returning 8. Use =VAR.S(A2:A6) for sample variance, returning 10. The official VAR.P documentation and VAR.S documentation explain the population/sample distinction and argument handling.
For a transparent manual check, put =AVERAGE(A2:A6) in B1. In B2, enter =A2-$B$1 and fill through B6. In C2, enter =B2^2 and fill through C6. Then use =SUM(C2:C6)/COUNT(A2:A6) for the population result, or =SUM(C2:C6)/(COUNT(A2:A6)-1) for the sample result.
Google Sheets
Google’s documented functions are =VARP(A2:A6) for population variance and =VAR(A2:A6) for sample variance. They return 8 and 10 for this example. See the official VARP guide and VAR guide. The same mean/deviation helper columns can make the calculation visible.
Check your locale if a copied formula is rejected. Function naming or separators may differ. Also check the actual cell values: a number displayed as text may not be counted as you expect. Do not switch to a text-including function merely to make an error disappear; decide what the data should mean first.
Verify the range and count
Check that the selected range includes every intended observation and excludes totals, identifiers and another measurement column. Blank is not the same as zero. A filtered display does not establish which rows a formula uses, so verify the referenced range rather than relying on what is visible.
For automated reporting, the same discipline applies to AI-driven business workflows. Document the formula and input rules instead of accepting an unexplained generated answer.
7. How to interpret variance and standard deviation
Variance is most useful when the measurements, inclusion rules and units are comparable. There is no universal threshold that makes a variance high or low. Eight minutes squared may be meaningful for one process and irrelevant for another.
Take the square root for original units
For the five-value example, population standard deviation is √8, approximately 2.83 minutes. Sample standard deviation is √10, approximately 3.16 minutes. The variance results remain 8 and 10 minutes squared. Labeling variance as 2.83 would confuse two different measures.
If every measurement is converted from minutes to seconds, each value is multiplied by 60. The variance is multiplied by 60², or 3,600, while standard deviation is multiplied by 60. A bigger numerical variance after a unit conversion does not mean that the underlying process became more variable.
Compare like with like
Look at the mean, sample size, distribution and measurement method alongside variance. If one dataset combines several types of jobs and another contains only simple jobs, their spread may reflect the mix rather than a difference in performance. Graphs and grouped analysis can help explain what the single summary hides.
Our guides to AI forecasting tools and predictive analytics and business forecasting discuss wider analytical workflows. Descriptive variance alone does not provide a forecast interval, establish causation or prove that a process change worked.
For web measurements, our Statable Analytics overview gives context for a data-collection product. Before comparing periods, check whether collection rules or coverage changed. A statistic cannot compensate for incompatible inputs.
8. Common mistakes and limitations
The most frequent mistake is choosing the denominator after seeing which answer looks better. Decide the question and population/sample scope first. Keep that choice consistent when comparing groups or reporting repeated measurements.
Check data quality before removing outliers
An extreme value might be a typing error, a real unusual event or evidence of a different process. Investigate it. Do not delete it merely because variance becomes smaller without it. Record any exclusion rule and, when useful, report results with and without the questionable observation.
The NIST handbook explains why variance and standard deviation can be affected by heavy tails. Median absolute deviation or interquartile range may be useful alternatives for some datasets. Choosing an alternative should follow the analysis goal and data shape, not a desire to hide variation.
Do not confuse related business terms
Statistical variance is not accounting reconciliation and not actual-minus-budget variance. Our accounting reconciliation guide explains the separate task of matching records and investigating differences. Accounting software may report financial variances, but that label does not establish that the statistical formula applies.
Other errors include forgetting to square, using different ranges for the mean and count, mixing units and interpreting a small sample too confidently. In software work, testing tools can collect numerical results, but elapsed time, response time and error counts are different measures. Keep each analysis tied to a defined variable.
Separate automated calculation from interpretation
An automated tool can produce a mathematically correct variance while answering the wrong question. Our overview of AI testing tools gives context for automated workflows, but a numerical result still needs input and method checks. Record whether repeated runs measure the same conditions. Do not mix measurements from different environments without explaining the comparison.
For systems that take actions based on metrics, define who can approve changes and which decisions remain human-reviewed. Our enterprise access authority-map guide addresses that separate operational question. A high variance can justify investigation; it does not by itself authorize a staffing change, configuration change or financial action.
Make the answer reproducible
Save the source range, exclusions, formula, denominator and rounding rule. If the calculation influences a consequential decision, have another person review the assumptions as well as the arithmetic. A reproducible result is easier to check and explain than a number copied from a dashboard.
9. Variance calculation questions answered
What is the fastest way to calculate variance?
For a clean numerical range, use the documented population or sample function in your spreadsheet. In Excel, those are VAR.P and VAR.S; in Google Sheets, VARP and VAR. Before pressing Enter, define the scope and check the range. For learning or auditing, a mean/deviation/squared-deviation table makes the result easier to understand than an unexplained function output.
Why do I divide by n minus one?
The usual sample variance estimates population variance using a mean calculated from the same sample. That mean constrains the deviations, and the n minus one denominator corrects the downward bias under the usual sampling assumptions. It is not a rule for every use of the word variance. For a complete finite population you are describing, divide the sum of squared deviations by its size.
Can variance be negative?
For these ordinary formulas, no. Squared deviations are nonnegative, and the valid denominator is positive. Identical observations give zero. A negative reported result means you should inspect the formula, inputs or numerical method rather than interpret negative spread. A financial budget variance can be negative because it is a signed difference, but that is a different concept.
What if I have only one value?
A one-value finite population has variance zero because its only value equals its mean. The usual sample variance is undefined with one observation because the denominator is zero. Spreadsheet functions may have additional input requirements; Google’s VARP documentation, for example, states a minimum of two supplied values. Distinguish the mathematical definition from the particular software function’s behavior.
Should missing values be entered as zero?
Only when zero is the actual measurement. Missing means unknown or unavailable, while zero is a recorded value that affects the mean and spread. Replacing missing values with zero can create artificial variation or distort the average. Decide and document how missingness is handled, check the spreadsheet’s argument rules and report the number of observations used.
Is variance the same as standard deviation?
No. Standard deviation is the square root of variance. Variance uses squared units, while standard deviation uses the original units. For 2, 4, 6, 8 and 10 minutes, population variance is 8 minutes squared and population standard deviation is about 2.83 minutes. Always label which measure is reported and whether it describes a population or estimates from a sample.
Editorial note: Educational and software documentation checked October 12, 2026. Examples are fictional, unweighted calculations independently checked by arithmetic and a second calculation method. This guide does not cover complex sampling designs or make inferential claims about real data. Illustrations are AI-generated conceptual scenes, not factual charts or software screenshots.
