Rankings

Occasional Contributor

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

@RTomac 

= 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!

 

image.png

= 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, @Peter Bartholomew ? (smiley face)

 

@RTomac 

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.

mathetes_0-1673649992196.png

 

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 

@RTomac 

 

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

@RTomac 

 

Here's a good link to an explanation (with examples) of how VLOOKUP works. There also are links to other variations on the theme. You'll find as you work more with Excel that these various methods of accessing data in tabular form will come in very handy for many applications.

@mathetes 

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 formula I will try it out tomorrow.

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 

@RTomac 

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.

S2160.png

@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.

@mathetes 

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.