Forum Discussion
Rankings
I have a spreadsheet ware I'm getting an average of twelve numbers. The average of these numbers fall into five categories. These categories are AA, A, B, C and D. The average between 100 to 95% is AA, 94% to 90% is A, 89% to 80% is B, 79% to 70% is C and anything below 70% is D
The last one I attempted was If AG7 <.69, "D", if AG7 >.70 or <.79, "C", if AG7 >.80 or <.89, "B", if AG7 > .90 or <.95, "A", if AG7 >.95, "AA"
It doesn't give me an error, but it doesn't give me a result.
14 Replies
= XLOOKUP( AVERAGE(numbers), {0.95;0.9;0.8;0.7;0}, {"AA";"A";"B";"C";"D"},, -1)I think this requires 2019 or later
No matter how complicated the formula, it is always possible to make it worse!
= LET( thresholds, {0.95;0.9;0.8;0.7;0}, grades, {"AA";"A";"B";"C";"D"}, markGrade, BYROW(numbers, LAMBDA(marks, LET( averageMark, AVERAGE(marks), grade, XLOOKUP(averageMark, thresholds, grades, ,-1), TEXT(averageMark, "00%") & grade ) ) ), MID(markGrade,{1,4},3) )Requires 365
No matter how complicated the formula, it is always possible to make it worse!
And who better to do so than our resident specialist in complicated formulae, PeterBartholomew1 ? (smiley face)
Personally, I always find it preferable to simplify by means of a table. It also gives the advantage of being able to tweak without changing the formula. So here, the formula =VLOOKUP(A2,C1:D6,2,1) in cell B2, refers to the value entered in A2, searches the table in C1:D6 to retrieve the corresponding letter.
