Greater-than-or-equal counts ties
The requirement is strictly above the computed percentile. Values equal to it do not enter G6.
Compare a chosen percentile with an allowance
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Use numeric percentile G2 from 0 through 1 and allowance G3. G5 is PERCENTILE.INC of numeric B2:B11; G6 counts numeric observations strictly greater than G5; G7 is Review allowance only when G5 exceeds G3, otherwise Within allowance. With no numeric observations, all three outputs return No observations. Ignore referenced text, blanks and booleans; keep numeric zeroes.
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
Calculate an inclusive percentile, count observations strictly above it and apply an explicit allowance rule.
THE QUICK ANSWER01
Calculate an inclusive percentile, count observations strictly above it and apply an explicit allowance rule.
G5
=IF(COUNT(B2:B11)=0,"No observations",PERCENTILE.INC(B2:B11,$G$2))G6
=IF(COUNT(B2:B11)=0,"No observations",SUMPRODUCT(ISNUMBER(B2:B11)*(B2:B11>G5)))G7
=IF(ISNUMBER(G5),IF(G5>$G$3,"Review allowance","Within allowance"),G5)For the example data
G5 18.2G6 1G7 Review allowance
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
An interpolated threshold is not a row
The original percentile is 18.2 even though no source cell contains 18.2. PERCENTILE.INC places the requested fraction on the ordered observations and interpolates between neighbors when needed. The observed count above it is one.
Ties change the observed fraction
An interpolated percentile does not promise that exactly 10% of a finite sample will be strictly greater than its 90th percentile. Repeated values can make the observed count above a percentile much smaller. This report counts what is present rather than inferring a future percentage.
A declared comparison rule
The allowance decision compares the selected sample statistic with G3. It does not predict future measurements or establish a service guarantee. Choose the percentile and allowance to match the actual question before using this pattern outside the exercise.
Read the full step-by-step walkthrough
- Choose the fraction
G2 stores a numeric fraction: 0.9 requests the 90th percentile. Zero and one are valid endpoints for PERCENTILE.INC.
- Interpolate within the observed sample
With ten original observations, the zero-based position is (10-1)*0.9 = 8.1. Interpolate one tenth of the way from 18 to 20, giving 18.2.
- Count only numeric values strictly above it
Enter G6. The ISNUMBER mask excludes text numbers, booleans and blanks; the comparison uses greater-than, so ties do not count.
- Compare the chosen statistic with the allowance
Enter G7 separately. The original 18.2 exceeds the allowance of 18, producing Review allowance. Equality produces Within allowance.
- Keep absence distinct from meeting the allowance
No observations is carried through the report. A missing sample cannot establish that an allowance is met.
Things to watch
Text numbers enter the count
Keep the ISNUMBER mask in the same SUMPRODUCT expression as the comparison.
Percentile confused with a mean
A selected percentile is an ordered-position statistic. The skewed case separates it from AVERAGE.
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.