classifying scores by multiple criteria

[Berrett]

I have ~1000 students in Column A. They have a grade level in Column B (K, 1, 2 ... 11, 12). In Column C there is a test score.

I need to classify each student into a LOW, MEDIUM, or HIGH group based on certain cut-points. The difficulty is that the cut-points are different for each of the grade levels!

For example, in 1st Grade the cut points are "Low < 20", "Medium < 38", "High >38". In 4th Grade the cut points are "Low <65", "Medium < 86", "High > 86".

So, I can't use a standard lookup table for this. Any ideas how I can write a formula to accomplish this?

(BTW: We have users who are using the newest version of Excel on a Mac, so VBA cannot be used as a solution). Thanks!

 

[Berrett]

Well, with some trial and error, I got this one figured out. I used a very ugly formula to look at two different lookup tables (one for LOW and one for MEDIUM). The formula looked like this:

=IF(T3="BR","L",IF(VLOOKUP(I3,'cut points'!$E$14:$F$17,2,FALSE)>'data entry'!T3,"L",IF(VLOOKUP(I3,'cut points'!$H$14:$I$17,2,FALSE)>'data entry'!T3,"M","H")))

If the score wasn't LOW or MEDIUM, then it must be HIGH. It worked, although I don't really like the way it looks.

Thanks.