Forum Discussion
Rankings
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!
I wasn't happy with the nested array issue so this is a simplification
WorksheetFormula = OverallGradeλ(numbers) = LAMBDA(marks, LET( thresholds, {0.95;0.9;0.8;0.7;0}, grades, {"AA";"A";"B";"C";"D"}, averageMark, BYROW(marks, LAMBDA(marks1,AVERAGE(marks1))), grade, XLOOKUP(averageMark, thresholds, grades, ,-1), HSTACK(averageMark, grade) ) )Thanks for the new one but I figured out that somehow the cell formatting had turned the results white so I couldn't see it. I formatted all the cells for black and the first one is working great just needed to create a lookup table at the bottom of the sheet. Thought I figured out just one worked fine. Thanks again. Buy the way how and what is the upside down Y symbol? Can't seem to find it in any of the lists and have no idea how to create it on the keyboard.PeterBartholomew1
I like this one a lot but I still can't get it to work altough I'm using a calculated source compare with my table. mathetes
You need to say a bit more. Is it possible for you to post your spreadsheet on OneDrive or GoogleDrive, and then paste a link here that grants us edit access to your sheet?
If that's not possible, a snapshot (image) of your sheet showing the table, the number you're wanting to turn into a grade, etc....
- Got it to work. I set it up just like you had it then pasted it into the cell I need it in. Worked for one on to see if I can get it to work for the rest.
Thanks
TRUE 0 D
0.7 C
0.8 B
0.9 A #VALUE!
0.95 AA
1
