Quartiles and bands

[microsoft]

I have a list of student grades.

I'd like to create a formula that compares a student's grade with the
results of the quartile function, and outputs the band in which that
student's grade falls.

For example, say the Quartile function returns 99, 80, 70, 59, 40 for index
numbers 4, 3, 2, 1 and 0. And a particular student's grade is 84. Then I
want the formula to return "top band" or something similar. Or if the
student's grade is 75, then the formula should return "2nd band," and so
on.

Ideas?

Thanks.

 

[Bernie Deitrick]

For grades in cells A1 to A50, array enter this formula (enter using
Ctrl-Shift-Enter) to return the band of the score in cell A1. Then copy
down to match your data. Watch the line wrapping introduced by your news
reader.

=INDEX({"4th Band";"3rd Band";"2nd Band";"1st Band";"Top
Score"},MATCH(A1,QUARTILE($A$1:$A$50,ROW(A$1:A$5)-1)))

HTH,
Bernie
MS Excel MVP