Forum Discussion
Rankings
= 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.
VLOOKUP! Now, that's a function I haven't used for 10 years or more! I did consider the even older LOOKUP (array form) for this problem though. I have every sympathy for the principle of avoiding hard-wiring values into formula and I did start this formula with a table such as you show. I only F9-ed it to convert it to an array constant when I decided that it was truly constant at least for the lifetime of the workbook and that the formula could be written to ensure that the constant need only be entered once.
I did not seriously consider the possibility that the OP might adopt the formula, otherwise I would have converted it to a Lambda function to conceal the gruesome detail! The way in which my formulas grow is by combining larger and larger parts of the functionality of the workbook instead of breaking it down as is normal. The theory is that any given problem has an inherent level of complexity.; it is either incorporated within a single formula or it is distributed to a chain of formulas and the relationships between them.
In the present case, deriving the average is part of the problem, so I built that into the formula. The chances are that there will not be a single instance of the averaging in the workbook, so I made it apply to an array of averages. I then addressed the OP's problem of converting the average to a grade. Having done that, I thought it would be useful to display the average as well as the grade and did not wish to recalculate the average. Then I ran into the nested array limitation of Excel (a little better planning and I could have avoided it by only combing averages and grades at the end).
The logical end point of the process is that an entire worksheet and all the data objects contained therein should be produced by a single formula in A1 and spill out dynamically. Fortunately, even I recognise the fact that it is possible to have too much of a good thing!
