Read the sample before acting on its summary
Keep sample size, percentile conventions and review decisions separate when interpreting measurement data.
| Source cell | Stored value | Numeric observation |
|---|---|---|
| B2 | 0 | Yes |
| B3 | 2 | Yes |
| B4 | 4 | Yes |
| B5 | 6 | Yes |
| B6 | 20 | Yes |
| B7 | text 200 | No |
| B8 | blank | No |
Keep the count beside the statistic
Suppose a measurement column contains numeric 0, 2, 4, 6 and 20, plus the text 200 and a blank. There are five numeric observations. The median is 4; converting the text into a number would change the sample. A measured zero belongs to the sample.
STDEV.S describes sample dispersion around the mean and uses n minus one in the variance denominator. A single observation cannot establish this sample spread. Multiple identical observations can have a valid spread of zero. Preserve an explicit insufficient-data state instead of substituting zero.
=IF(COUNT(B2:B11)<2,"Need two observations",STDEV.S(B2:B11))Name the percentile method and count the ties
For numeric 0, 2, 4, 6 and 20, PERCENTILE.INC at 0.9 interpolates at position 3.6 in the zero-based ordered sample. The result is 14.4, between 6 and 20. A percentile result need not appear in a source cell.
Counting values strictly above that threshold gives one observed value. With ties or a small sample, the observed percentage above a percentile need not equal the remaining nominal percentage. This sample statistic does not promise a future service level.
=PERCENTILE.INC(B2:B11,0.9)Use fences to request investigation
Under QUARTILE.INC, the five numeric values have Q1 of 2 and Q3 of 6. Their IQR is 4. The lower inner fence is 2-1.5*4 = -4 and the upper fence is 6+1.5*4 = 12. Only the measured 20 lies strictly outside. A value exactly on a fence remains inside the rule used in this course.
A review flag is a prompt to inspect context. A distant value can be a valid measurement, a recording problem or a different process. Retain the original records; the calculation does not decide which observations to delete. Other quartile conventions can produce different fences, so record the method.
Check the idea
Under the declared inclusive quartiles and strict 1.5-IQR fences, which original numeric measurement needs review?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.