What it does
A small standard deviation means the values sit close to the average; a large one means they are scattered. Two lists with the same average can behave very differently: one may consist of deliveries that always take 5 days, the other of deliveries that vary between 1 and 9 days. The average does not show the difference; STDEV.S does.
The .S stands for sample: if your data is part of a larger whole (the last 20 orders, 30 randomly chosen products), use STDEV.S. If your data is the whole population (all employees, the 12 months of a year), use STDEV.P. The difference between the two is noticeable in short lists and shrinks as the list grows.
To compare the spread of values of different sizes, divide the standard deviation by the average: =STDEV.S(B2:B20)/AVERAGE(B2:B20). This ratio, the coefficient of variation, reads as a percentage. Empty cells and text are left out; at least two numbers are needed.
Syntax
=STDEV.S(number1, [number2], ...)=STDSAPMA.S(sayı1; [sayı2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number1 | Required | The first number, cell or range. |
number2 | Optional | Optional further numbers or ranges, up to 255 arguments. |
Examples
1Consistency of two suppliers with the same average
The last six deliveries of both suppliers have the same average. The standard deviation shows which one is more predictable.
=AVERAGE(B2:B7)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Delivery | Supplier A | Supplier B | Supplier A | Supplier B | ||
| 2 | T-1 | 5 | 1 | Average | 5.00 | 5.00 | |
| 3 | T-2 | 5 | 9 | Standard deviation | 0.63 | 3.41 | |
| 4 | T-3 | 4 | 3 | ||||
| 5 | T-4 | 6 | 8 | ||||
| 6 | T-5 | 5 | 2 | ||||
| 7 | T-6 | 5 | 7 |
- F2
=AVERAGE(B2:B7) - G2
=AVERAGE(C2:C7) - F3
=STDEV.S(B2:B7) - G3
=STDEV.S(C2:C7)
Both suppliers average 5.00 days. A's standard deviation is 0.63 and B's is 3.41 days: A's deliveries take almost the same time every time.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2STDEV.S versus STDEV.P
The same five measurements are calculated with both functions. The measurements are a sample of a larger production run, so STDEV.S is the right choice; STDEV.P treats the same data as the whole population.
=STDEV.S(B2:B6)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sample | Weight (g) | STDEV.S | STDEV.P | |
| 2 | N-1 | 502 | 3.21 | 2.87 | |
| 3 | N-2 | 498 | |||
| 4 | N-3 | 505 | |||
| 5 | N-4 | 497 | |||
| 6 | N-5 | 501 |
- D2
=STDEV.S(B2:B6) - E2
=STDEV.P(B2:B6)
The sample deviation is 3.21 g and the population formula gives 2.87 g. In a five-value list the difference is noticeable; as the number of values grows, the two results move closer.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Compare the spread of sales of different sizes
Cable sales are much larger than socket sales, so their standard deviations cannot be compared directly. Dividing the standard deviation by the average expresses the spread as a percentage.
=AVERAGE(B2:B5)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Cable | Socket | Average | Standard Deviation | Variation | |
| 2 | January | 412000 | 18400 | 410,400 | 11,363 | 2.8% | |
| 3 | February | 398500 | 25100 | 20,250 | 4,513 | 22.3% | |
| 4 | March | 425300 | 14900 | ||||
| 5 | April | 405800 | 22600 |
- E2
=AVERAGE(B2:B5) - F2
=STDEV.S(B2:B5) - G2
=F2/E2 - E3
=AVERAGE(C2:C5) - F3
=STDEV.S(C2:C5) - G3
=F3/E3
Cable's standard deviation is 11,363 and the socket's 4,513; but as a ratio cable moves only 2.8% and sockets 22.3%. Socket sales are far more volatile in relative terms.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #DIV/0!
Cause: There are fewer than two numbers in the range; a sample standard deviation needs at least two values.
Fix: Check the range and how the numbers are stored; convert numbers stored as text.
- Result larger than expected
Cause: STDEV.S was used although you have the whole population; the sample formula makes the result slightly larger in short lists.
Fix: If you are measuring the whole population, use STDEV.P.
Good to know
- One standard deviation below and above the average gives the band where most values fall:
=AVERAGE(B2:B20)-STDEV.S(B2:B20)and=AVERAGE(B2:B20)+STDEV.S(B2:B20). - The standard deviation is in the same unit as the data: for times in days, the result is in days too.
In Nexell
Get Nexell to do it from a sentence
Make a summary showing the average and standard deviation of delivery times for two suppliers side by side
Convert to tableBring your delivery list into the tool and type this. Nexell builds the summary with real Excel formulas and shows it in the preview first; in the downloaded file, the summary updates as new delivery times are entered.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between STDEV.S and STDEV.P?
STDEV.S treats the data as a sample of a larger whole and divides by n-1; STDEV.P assumes the data is the whole population and divides by n. Use .S for samples and .P for whole populations.
How do I interpret a standard deviation?
It is the typical distance of the values from the average. With an average delivery time of 5 days and a standard deviation of half a day, deliveries are very consistent; with 3 days, the times vary over a wide range.