formula for finding best GCSE grade

[hsn6]

Hi

hope someone can help, i was asked by maths teacher if i knew how to find best GCSE grade in spreadsheet, e.g. a student has sat a maths exam in Nov and then Jan, and maybe has a predicted grade. find the best grade from the 3 coloumns but could be even more coloumns depending on how many assessments the student has done. grades will be A-G, U, X.

thanks in advance

H

[Izandol]

You may try:
=IFERROR(CHAR(MIN(CODE(UPPER(A1:M1)))),"")
entered by pressing Ctrl+Shift+Enter

[hsn6]

Hi, i have attached a file to better explain what i mean, thank you

[estige]

Izandol's solution works like a charm, just use B2:D2 instead of A1:M1

[hsn6]

Sorry, do i enter the code =IFERROR(CHAR(MIN(CODE(UPPER(B2:D2)))),"") in cell E2?

i just tried this but get a blank cell, but when i click on fx it is showing formula result as correct, e.g. first row as A

thanks

[estige]

You probably forgot to confirm the formula with Control+Shift+Enter. (Not just Enter.)

[Izandol]

Yes it is entered in cell E2 but must be entered by Ctrl+Shift+Enter and not Enter alone. In the formula bar you will see:
{=IFERROR(CHAR(MIN(CODE(UPPER(B2:D2)))),"")}
if you have done this correctly.

[hsn6]

thanks Izandol and estige it was my mistake bt not Ctrl+Shift+Enter.

just want to check if i understand the code, CHAR = ABC, etc and min would be starting from A, upper = Z

is it possible for this to work if the grades coloumns are not next to each other and theres other coloumns inbetween that might have numbers but i dont want to use them.

[Izandol]

Your understanding is correct.

You may use:
=IFERROR(CHAR(MIN(CODE(UPPER(IF(ISNUMBER(--(B2:M2&"0")),"Z",B2:M2))))),"")
this will ignore blank cells and numbers.

[martindwilson]

this non array does the same thing as the first formula
=IFERROR(CHAR(MIN(INDEX(CODE(UPPER(B2:D2)),0))),"")

[estige]

Here is one possibility if you want to look up cells that are not in a range (e.g. col B, D and F in stead of B, C and D):
=IFERROR(CHAR(MIN(CODE(UPPER(B2)),CODE(UPPER(D2)),CODE(UPPER(F2)))),"")
(Still array)

[hsn6]

thanks to everyone for the help