Excel

[Clar1ssaJB]

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?

[#2]

@Clar1ssaJB Is this do-able? Not sure I understand, but perhaps the attached file does what you need/want.

[#3]

@Riny_van_Eekelen 

Thanks for the reply, but Unfortunately, can't open the link. :frowning_face:

[#4]

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

[#5]

Thank you much appreciated for that. Sorts out the critical issue, but what if I had quality fail and critical fail. Critical is the worst and the end cell needs to pick the worst fail. Would I be able to amend the formula, to pick out the three - critical fail, quality fail and pass. So if no critical or quality then the only option is to pick pass in the end cell.
Does that make sense. Apologies, should have studied computers.

[#6]

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

[#7]

Thank you, I'll give it a go tomorrow.
Didn't expect a speedy response on a Sunday, but much appreciated.
Enjoy the rest of your Sunday. <3

[#8]

@Clar1ssaJB 

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λ)