Jul 06 2022 09:27 AM
I have columns using nbers as headers.
1,2,3 to 22. Each cell underneath will have a word - for example Pass, Quality or Critical fail. With a cell at the end which would show the worst grade. I.e Critical.
However, I want the end cell to show the worst grade as soon as I type in the word within the first 22 columns. So if cell 1 is a pass, cell 2 is a Quality fail and cell 3 is a critical fail - then the end cell would mimic cell 3.
Is this do-able?
Jul 06 2022 09:42 AM
@Clar1ssaJB Is this do-able? Not sure I understand, but perhaps the attached file does what you need/want.
Jul 14 2022 04:53 AM
Thanks for the reply, but Unfortunately, can't open the link. :frowning_face:
Jul 14 2022 05:10 AM
@Clar1ssaJB Did you press the arrow to download the file? Perhaps your system security prevents you from downloading.
Perhaps you can work off the screenshot below.
Jul 17 2022 05:44 AM
Jul 17 2022 06:30 AM - edited Jul 17 2022 06:44 AM
@Clar1ssaJB Perhaps a bit of a clumsy formula but this seems to work in W2 (and copy down as needed)
=IFERROR(IFS(SUM(--(A2:V2="Critical Fail")),"Critical Fail",SUM(--(A2:V2="Quality Fail")),"Quality Fail",SUM(--(A2:V2="Pass")),"Pass"),"-")
Can't think of anything smarter, just right now.
Jul 17 2022 06:46 AM
Jul 17 2022 09:32 AM
A reasonably standard approach that comes into its own with longer lists is to introduce them as a validation list and then to use the list to convert the text data into numeric values. That allows the MAX function to return the critical value before INDEX returns the text equivalent.
I chose to express the formula as a Lambda function
OverallGradeλ
= LAMBDA(recordGrade,
LET(
gradeIndex, IFERROR(XMATCH(recordGrade, gradeList), 0),
maxGrade, MAX(gradeIndex),
INDEX(gradeList, maxGrade)
)
);
because that makes it easier to process an entire array using BYROW
WorksheetFormula
= BYROW(grade, OverallGradeλ)