I am trying to create a formula that would tell me in cell J5 the highest grade acheived in G5, H5 & I5 example

G5 H5 I5 J5

A C B Answer would be AAny ideas?

✔ Best Answer

Here is an update to the update,

this one lets you use A+, A, A- etc. etc.Remember it's an ARRAY formula, so you must use CTRL-SHIFT-ENTER

=INDEX({" ","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},MIN(IF(ISNUMBER(MATCH(G6:I6,{" ","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},0)),MATCH(G6:I6,{" ","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},0))))

It is now a verrrrry long formula, so Copy & Paste from here.

MIKE

Try this in cell J5: ="Highest Grade is: "&CHAR(MIN(CODE(G5),CODE(H5),CODE(I5)))

MIKE

Here's a modified version, the previous version got confused with upper

and lower case letters:=IF(AND(G6>"",H6>"",I6>""),"Highest Grade is: "&CHAR(MIN(INDEX(CODE(UPPER(G6:I6)),0,0))),"Missing A Grade")

This one also checks to make sure all three cells have a grade,

and does not care if they are upper or lower case letters.MIKE

Thanks for this reply, it's greatly appreciated How would you change the formula to allow for missing grades - there may only be 2 out of 3 grades filled in, but I still would want to know the highest grade showing in the 3 cells

Sorry to change the original question .......

Many thanks for your help

The simplest way is to use the letter X as a place holder,

since the usual grades run from A to F, the letter X will

never be the highest grade.I'll work on a new formula that does not require a place holder.

MIKE

Here is an updated formula. The usual letter grades are A, B, C, D, F so I have use them in this formula,

if your grading system uses something else,

the formula will have to be modified.It is an ARRAY Formula,

When you enter the formula in a cell you MUST use CTRL-SHIFT-ENTER

else it won't work.=INDEX({" ","A","B","C","D","F"},MIN(IF(ISNUMBER(MATCH(G6:I6,{" ","A","B","C","D","F"},0)),MATCH(G6:I6,{" ","A","B","C","D","F"},0))))

This will find the highest grade entered in cells G6, H6 or I6

MIKE

Here is an update to the update,

this one lets you use A+, A, A- etc. etc.Remember it's an ARRAY formula, so you must use CTRL-SHIFT-ENTER

=INDEX({" ","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},MIN(IF(ISNUMBER(MATCH(G6:I6,{" ","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},0)),MATCH(G6:I6,{" ","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},0))))

It is now a verrrrry long formula, so Copy & Paste from here.

MIKE

Here is another alternative, one that is not restricted to any specific range and is a bit easier to enter in a cell. It is a User Defined Function (UDF)

After pasting it into a Standard Module in the VBA editor, you can can use it in any cell by entering =maxGrade(

your_range) whereyour_rangecan be anything from a single cell to an entire sheet.As written it will return an

Fif there are no grades inyour_range. After all, how can you pass a student who never took any tests? ;-)That feature can be modified to fit your exact situation if need be.

Function maxGrade(ByVal target As Range) tempMax = 70 For Each cell In target If cell <> "" Then cellCode = Asc(UCase(cell)) If cellCode < tempMax Then tempMax = cellCode End If Next maxGrade = Chr(tempMax) End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History