STDEV.S

Calculates the standard deviation of a sample of data.

Syntax:

STDEV.S(numberOne, [numberTwo],…)

numberOne, [numberTwo],… are 1 to 255 arguments representing the numbers that you want to use. These arguments can be numbers, cell references or ranges.

Calculation for STDEV.S:

$\frac{\sum ( x - x ˉ ) ^{2}}{( n - 1 )}$

Example:

If numberOne, [numberTwo],… contains 1,1,1,1,2,2,2,2,2,2,3,3,4,4,4,4,5,5,5,5,5:

STDEV.S(1,1,1,1,2,2,2,2,2,2,3,3,4,4,4,4,5,5,5,5,5)

returns 1.516575089

If numberOne, [numberTwo],… contains 1,1,1,1,2,2,2,2,2,3,3,3,4,4,4,4,5,5,5,5,5:

STDEV.S(1,1,1,1,2,2,2,2,2,3,3,3,4,4,4,4,5,5,5,5,5)

returns 1.499206139

	A	B	C	D
1	1	1	1	1.499206139
2	1	2	2
3	2	2	2
4	3	3	3
5	4	4	4
6	4	5	5
7	5	5	5

Application:

Imagine a small company, "Tech Innovations," that wants to analyze the variability in the weekly sales of its top-selling product, the "Quantum Processor." The sales manager wants to understand how much the weekly sales deviate from the average. She decides to use the STDEV.S function to calculate the standard deviation of a sample of sales data from the last 8 weeks.

Here is the sample of the weekly sales data (in thousands of dollars):

	Week	Weekly Sales (in thousands)
	A	B
1	1	25
2	2	32
3	3	28
4	4	40
5	5	35
6	6	29
7	7	38
8	8	31

Calculation:

The STDEV.S function calculates the standard deviation of a sample. The formula for the sample standard deviation is:

$s = \frac{\sum _{i = 1}^{n} ( x _{i} - x ˉ ) ^{2}}{n - 1}$

Where:

$x_{i}$ is each individual value (weekly sales).
$\overset{x}{ˉ}$ is the sample mean (average weekly sales).
n is the number of data points in the sample (8 weeks).

Step 1: Calculate the average (mean) weekly sales ( $\overset{x}{ˉ}$ ).

$\overset{x}{ˉ}$ =(25+32+28+40+35+29+38+31)/8

$\overset{x}{ˉ}$ =258/8

$\overset{x}{ˉ}$ =32.25

Step 2: Calculate the squared difference of each data point from the mean.

(25−32.25)2=(−7.25)2=52.5625
(32−32.25)2=(−0.25)2=0.0625
(28−32.25)2=(−4.25)2=18.0625
(40−32.25)2=(7.75)2=60.0625
(35−32.25)2=(2.75)2=7.5625
(29−32.25)2=(−3.25)2=10.5625
(38−32.25)2=(5.75)2=33.0625
(31−32.25)2=(−1.25)2=1.5625

Step 3: Sum the squared differences.

∑( $x_{i} - \overset{x}{ˉ}$ )2=52.5625+0.0625+18.0625+60.0625+7.5625+10.5625+33.0625+1.5625=183.5

Step 4: Divide the sum by (n-1).

183.5/(8−1)=183.5/7≈26.214

Step 5: Take the square root of the result.

s= $26.214$ ≈5.12

Conclusion:

The STDEV.S calculation shows that the standard deviation of the weekly sales for the "Quantum Processor" is approximately 5.12. This value tells the sales manager that the typical weekly sales figure deviates from the average of 32.25 thousand by about 5.12 thousand. A lower standard deviation would indicate that the sales are more consistent, while a higher standard deviation would suggest greater variability and unpredictability in sales figures. In this case, the sales manager can use this information to understand the stability of the product's market performance.

Result for STDEV.S:

5.12