← VCE Spreadsheets, SQL & Visualisation

The STDEV.S function

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.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 22, VCE Data Analytics - U3O1 - Data Analytics — Module 86, VCE Data Analytics - U4O1 - Development and Evaluation — Module 27, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 17

Understanding The STDEV.S function in VCE Spreadsheets, SQL & Visualisation

What it does: Because the scores are only a sample, STDEV.S is the correct function for the range C2:C21. The function takes a range such as D2:D40 and uses the numbers in it to work out how spread out the data is. Standard deviation measures spread. The middle value is the median, found with MEDIAN. STDEV.P is for the whole population. If the range holds every value you care about, use STDEV.P. STDEV.S needs numbers in a range. It ignores text in the range, so the range should hold the numbers you want to measure. Every spreadsheet formula must start with =. Without it the cell shows plain text.

Common mistakes: The function name is misspelled. It should be STDEV.S, so the formula is =STDEV.S(B2:B30). The 20 students are only a sample, so the formula needs STDEV.S, not STDEV.P. STDEV.P is for a whole population.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add The STDEV.S function to a class →

This module's key terms are also in a printable crossword (Modules 21–34) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula finds the sample standard deviation of the marks in B2:B30?
=STDEV.S(B2:B30)=STDEV(B2,B30)
Matches
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.
Applying
Meanings
Term
=STDEV.S(B2:B30)
Meaning
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.
Practice
=STDEV.S(B2:B30)
Testing
Gaps
=(B2:B30)
Check Answer
Spots
3D2: Spread
4E2: =STDV.S(B2:B30)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Cosmic Owl' badge — Comprehension Guru, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which formula finds the sample standard deviation of the marks in B2:B30?
A. =STDEV.S(B2:B30) ✓
B. =STDEV(B2,B30)
STDEV.S takes a range such as B2:B30. Writing B2,B30 would only use two cells.
2. What does STDEV.S tell you about a set of numbers?
A. How spread out the values are ✓
B. The middle value of the data
Standard deviation measures spread. The middle value is the median, found with MEDIAN.
3. Your data is a sample of 30 students from a school of 900. Which function suits this?
A. STDEV.P
B. STDEV.S ✓
Use STDEV.S when the numbers are only a sample of a bigger group.
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?
A. STDEV.P ✓
B. STDEV.S
STDEV.P is for the whole population. If the range holds every value you care about, use STDEV.P.
5. True or false: STDEV.S works on a range of numbers.
A. False
B. True ✓
STDEV.S needs numbers in a range. It ignores text in the range, so the range should hold the numbers you want to measure.

Match the Pairs

=STDEV.S(B2:B30) → Returns the sample standard deviation of the numbers in B2:B30, showing how spread out they are
=STDEV.P(B2:B30) → Returns the standard deviation when B2:B30 holds the whole population, not just a sample
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)
STDEV.S needs a range of numbers, so give it a cell range to measure → =STDEV.S(D2:D40)
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.
=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.

Key Term

=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.

Fill in the Blanks

1. =STDEV.S(B2:B30)
Hint: Type the function name that returns the sample standard deviation
2. =STDEV.S(B2:B30)
Hint: Type the range of cells holding the numbers to measure
3. =STDEV.P(C2:C25)
Hint: Type the function used when the range is the whole population
4. =STDEV.S(D2:D40)
Hint: Type the last row number so the range covers the data
5. =ROUND(STDEV.S(B2:B30),1)
Hint: Type the function that finds the sample spread inside ROUND

Spot the Error

Question 1
A D1: Mean
B E1: =AVERAGE(B2:B30)
C D2: Spread
D E2: =STDV.S(B2:B30)
Line D — The function name is misspelled. It should be STDEV.S, so the formula is =STDEV.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)
Line C — The 20 students are only a sample, so the formula needs STDEV.S, not STDEV.P. STDEV.P is for a whole population.
Question 3
A A1: Data for every student
B C1: =STDEV.S(B2:B29)
C C2: =AVERAGE(B2:B29)
Line B — The data covers every student, which is the whole population. It should use STDEV.P, not STDEV.S.
Question 4
A =STDEV.S(C2,C25)
B =MIN(C2:C25)
C =MAX(C2:C25)
Line 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).
Question 5
A A1: Spread of scores
B B1: Score
C D1: =STDEV.S(B2-B30)
D D2: =AVERAGE(B2:B30)
Line C — A range uses a colon, not a minus sign. B2-B30 subtracts two cells. It should be B2:B30.

Student with a class code? Join your class →