Population spread used for a sample
STDEV.P divides by n, so it does not answer this task. Check the signed two-observation case.
Summarise a numeric sample without hiding missing data
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill G5:G7 separately for B2:B11. G5 counts numeric cells; G6 is their median or No observations when none exist; G7 is sample standard deviation or Need two observations when fewer than two exist. Referenced text, text numbers, blanks and booleans are ignored. Zeroes and signed measurements count.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5, G6, G7. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Keep the numeric count, median and sample standard deviation together; distinguish a missing sample from a measured zero.
THE QUICK ANSWER01
Keep the numeric count, median and sample standard deviation together; distinguish a missing sample from a measured zero.
G5
=COUNT(B2:B11)G6
=IF(COUNT(B2:B11)=0,"No observations",MEDIAN(B2:B11))G7
=IF(COUNT(B2:B11)<2,"Need two observations",STDEV.S(B2:B11))For the example data
G5 7G6 6G7 6.618876325292973
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Three outputs describe different properties
For the original seven numeric measurements, the median is 6. The count records sample size; STDEV.S describes dispersion around the mean in the same measurement units. No single output replaces the other two.
An explicit sample assumption
This exercise treats the rows as a sample of a larger process and asks for STDEV.S. When the rows are the complete population you intend to describe, STDEV.P answers that population question. Changing the function changes the denominator; it is not a formatting choice.
Supported source values
The ten source cells accept finite numbers from -1,000,000,000 through 1,000,000,000, text notes, blanks and referenced booleans. Only numeric cells are observations. The task does not convert text numbers, infer units or treat a formula error as a valid observation.
Read the full step-by-step walkthrough
- Count actual numeric observations
Enter G5. Numeric zero belongs to the sample. Text 8 and the pending note do not become measurements.
- Order the numeric values conceptually
The original numeric sample is 0, 2, 4, 6, 8, 10, 20. Its middle value is 6. MEDIAN handles ordering without rearranging the source.
- Keep an absent median explicit
Enter G6 with a COUNT guard so a range with no numeric cells returns No observations.
- Use the sample denominator
Enter G7 with STDEV.S. Sample variance divides the squared deviations by n minus one before taking the square root. STDEV.P uses a different denominator.
- Separate insufficient data from zero spread
One observation cannot establish sample standard deviation. Two or more equal numbers can legitimately produce zero. The guard preserves that distinction.
Things to watch
Missing data becomes zero
No observations and Need two observations carry different information from numeric zero.
Text numbers are coerced
The declared rule ignores referenced text and booleans. Convert source values deliberately in a separate task if that is required.
About this example
Practice calculates in the local browser spreadsheet engine. Enter each report formula separately; filling down does not create the other metrics. Starter workbooks retain the original ten-row sample; current-data downloads retain current values and formulas. Native Excel and Google Sheets recalculation/edit/save remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.