The STDEV.S function
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula finds the sample standard deviation of the marks in B2:B30?
2. What does STDEV.S tell you about a set of numbers?
3. Your data is a sample of 30 students from a school of 900. Which function suits this?
4. Your range holds the test scores of every student in a class of 24, and you want to describe only that class. Which function suits this?
5. True or false: STDEV.S works on a range of numbers.
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=STDEV.S(B2:B30) | AThe formula is missing the equals sign at the start, so the spreadsheet treats it as plain text and does not calculate it. | |
| 2=STDEV.P(B2:B30) | B=STDEV.S(C2:C21) | |
| 3Find the spread of test scores in C2:C21 when the class is only a sample of the whole year level | CThe range A2:A30 holds text such as student names, but STDEV.S needs numbers. It should point to the column of numbers, e.g. B2:B30. | |
| 4STDEV.S needs a range of numbers, so give it a cell range to measure | DReturns the sample standard deviation of the numbers in B2:B30, showing how spread out they are | |
| 5STDEV.S(B2:B30) | EReturns the standard deviation when B2:B30 holds the whole population, not just a sample | |
| 6=STDEV.S(A2:A30) | F=STDEV.S(D2:D40) |
C
Definitions
=STDEV.S(B2:B30)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=_______(B2:B30)
2.
=STDEV.S(______)
3.
=_______(C2:C25)
4.
=STDEV.S(D2:D__)
5.
=ROUND(_______(B2:B30),1)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
D1: Mean
B
E1: =AVERAGE(B2:B30)
C
D2: Spread
D
E2: =STDV.S(B2:B30)
Question 2
A
A1: Sample of 20 students
B
B2:B21 holds their scores
C
C1: =STDEV.P(B2:B21)
D
C2: =AVERAGE(B2:B21)
Question 3
A
A1: Data for every student
B
C1: =STDEV.S(B2:B29)
C
C2: =AVERAGE(B2:B29)
Question 4
A
=STDEV.S(C2,C25)
B
=MIN(C2:C25)
C
=MAX(C2:C25)
Question 5
A
A1: Spread of scores
B
B1: Score
C
D1: =STDEV.S(B2-B30)
D
D2: =AVERAGE(B2:B30)
Answer Key
The STDEV.S function
Section A: Quick Questions
Q1
A — =STDEV.S(B2:B30)
Q2
A — How spread out the values are
Q3
B — STDEV.S
Q4
A — STDEV.P
Q5
B — True
Section B: Match the Pairs
Q1
=STDEV.S(B2:B30) → Returns the sample standard deviation of the numbers in B2:B30, showing how spread out they are
Q2
=STDEV.P(B2:B30) → Returns the standard deviation when B2:B30 holds the whole population, not just a sample
Q3
Find the spread of test scores in C2:C21 when the class is only a sample of the whole year level → =STDEV.S(C2:C21)
Q4
STDEV.S needs a range of numbers, so give it a cell range to measure → =STDEV.S(D2:D40)
Q5
STDEV.S(B2:B30) → The formula is missing the equals sign at the start, so the spreadsheet treats it as plain text and does not calculate it.
Q6
=STDEV.S(A2:A30) → The range A2:A30 holds text such as student names, but STDEV.S needs numbers. It should point to the column of numbers, e.g. B2:B30.
Section C: Definitions
Q1
=STDEV.S(B2:B30) — Returns the sample standard deviation of the numbers in a range, showing how spread out they are. Use STDEV.P if the range is the whole population.Section D: Fill in the Blanks
Q1
=STDEV.S(B2:B30)Q2
=STDEV.S(B2:B30)Q3
=STDEV.P(C2:C25)Q4
=STDEV.S(D2:D40)Q5
=ROUND(STDEV.S(B2:B30),1)Section E: Spot the Error
Q1
D — The function name is misspelled. It should be STDEV.S, so the formula is =STDEV.S(B2:B30).
Q2
C — The 20 students are only a sample, so the formula needs STDEV.S, not STDEV.P. STDEV.P is for a whole population.
Q3
B — The data covers every student, which is the whole population. It should use STDEV.P, not STDEV.S.
Q4
A — A comma lists two separate values, so only C2 and C25 would be used. A colon is needed to select the whole range: =STDEV.S(C2:C25).
Q5
C — A range uses a colon, not a minus sign. B2-B30 subtracts two cells. It should be B2:B30.