Statistical
Classify & Flag Scores by Quartile Tier (QUARTILE.INC & IQR)
Calculate inclusive quartile cutoffs and IQR, then classify scores into threshold bands with an explicit policy for ties. Bands do not necessarily contain equal numbers of observations.
Percentile thresholds do not guarantee equal-sized groups
QUARTILE.INC calculates the 25th, 50th and 75th percentile cutoffs. For the nine scores 10 through 90 in steps of 10, the cutoffs are 30, 50 and 70. Their difference, Q3 minus Q1, is the interquartile range: 40.
This example assigns exact boundary ties to the higher band. Scores at or above 70 are Band 4; at least 50 but below 70 are Band 3; at least 30 but below 50 are Band 2; lower scores are Band 1. Three of the nine original observations are in Band 4, so calling that band exactly the top 25% would be misleading.
All nine reference cells must contain numbers, and D2 must be numeric. This intentionally rejects missing values rather than silently changing the reference population. When all reference scores are equal, all cutoffs coincide, IQR is zero, and a matching score enters Band 4. Labels indicate position relative to the dataset, not an absolute performance assessment.
Classify the candidate score
Enter a score in D2. Test the highest cutoff first to implement the boundary policy.
=IF(COUNT($B$2:$B$10)<>9,"Complete numeric dataset",IF(NOT(ISNUMBER(D2)),"Enter numeric score",IFS(D2>=QUARTILE.INC($B$2:$B$10,3),"Band 4",D2>=QUARTILE.INC($B$2:$B$10,2),"Band 3",D2>=QUARTILE.INC($B$2:$B$10,1),"Band 2",TRUE,"Band 1")))Calculate each cutoff
Use quartile arguments 1, 2 and 3 for the lower quartile, median and upper quartile.
=IF(COUNT($B$2:$B$10)<>9,"Complete numeric dataset",QUARTILE.INC($B$2:$B$10,1))Measure interquartile spread
IQR measures the distance between the upper and lower quartiles.
=IF(COUNT($B$2:$B$10)<>9,"Complete numeric dataset",QUARTILE.INC($B$2:$B$10,3)-QUARTILE.INC($B$2:$B$10,1))Return a threshold flag
This returns TRUE or FALSE for the candidate. It does not itself apply a cell color; conditional formatting is a separate operation.
=IF(COUNT($B$2:$B$10)<>9,"Complete numeric dataset",IF(NOT(ISNUMBER(D2)),"Enter numeric score",D2>=QUARTILE.INC($B$2:$B$10,3)))Try the example
Use the original values or change them to test your understanding.