Jan 13 2023 12:21 PM
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.
Jan 13 2023 12:51 PM
= 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
Jan 13 2023 01:34 PM
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
Jan 13 2023 02:47 PM
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, @Peter Bartholomew ? (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.
Jan 13 2023 04:15 PM
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
Jan 13 2023 04:25 PM
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....
Jan 13 2023 05:42 PM
Jan 13 2023 08:27 PM
Jan 13 2023 11:44 PM
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!
Jan 14 2023 02:23 AM
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)
)
)
Jan 16 2023 07:13 AM
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.@Peter Bartholomew
Jan 16 2023 07:19 AM
It is the Greek lower-case letter lambda. You can find it in the Character Map tool that comes with Windows. You can select it there and copy it, then paste it into your application.
Jan 16 2023 11:21 AM
@Hans Vogelaar @Peter Bartholomew
Is it also available to Mac users? And even if it is available, what advantage is there in using the character? I have been able to use LAMBDA to create complete user-defined functions, even nesting user-defined functions within user-defined functions. Haven't bothered with searching for the greek character, because it hasn't seemed necessary. That's why I'm asking.
Jan 16 2023 12:44 PM - edited Jan 16 2023 12:46 PM
For Excel I use Insert/Symbol or, more usually, Autocorrect which I have set to convert \lambda into λ. The \lambda also works without Autocorrect in the Word equation editor.
Why do I use it? I have found it helpful to be able to discriminate between arrays variables and Lambda variables, especially when they are being passed as parameters to another Lambda function.
Another place where identifying lambda functions is useful, is to discriminate between a built-in function and its lambda-ized equivalent, e.g.
= BYROW(array, SUMλ)
= BYCOL(array, SUMλ)
where SUMλ is defined to be
= LAMBDA(x, SUM(x))
Of course, if you already have naming conventions, it would be more important to stay consistent with those. For example Fn_SUM might suit you better and is comprised solely of keyboard characters.
I also introduced ϑ for thunks to remind myself that
arrayϑ
= Thunkλ(array)
where
Thunkλ
= LAMBDA(x, LAMBDA(x))
can be reversed using
array
= arrayϑ()
BTW. I also have borrowed the use of subscript characters x_2 from equation editor to create x₂ without clashing with the 14 billion Excel default cell references, none of which would I touch with a barge pole! Given that I have a problem of even convincing spreadsheet users that the formulas I offer are even anything to do with Excel, you could argue that the use of 'exotic' notation can only exacerbate the problem.