# Solved is a Nested IF statement the answer?

October 18, 2012 at 12:24:42
Specs: Windows 7
 I am trying to create a formula that would tell me in cell J5 the highest grade acheived in G5, H5 & I5exampleG5 H5 I5 J5A C B Answer would be AAny ideas?

See More: is a Nested IF statement the answer?

October 23, 2012 at 13:17:19
 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.MIKEhttp://www.skeptic.com/

#1
October 18, 2012 at 14:15:45
 Try this in cell J5:="Highest Grade is: "&CHAR(MIN(CODE(G5),CODE(H5),CODE(I5)))MIKEhttp://www.skeptic.com/

Report •

#2
October 18, 2012 at 15:38:42
 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

Report •

#3
October 23, 2012 at 08:35:16
 Thanks for this reply, it's greatly appreciatedHow 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 cellsSorry to change the original question .......Many thanks for your help

Report •

Related Solutions

#4
October 23, 2012 at 09:00:17
 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 willnever be the highest grade.I'll work on a new formula that does not require a place holder.MIKEhttp://www.skeptic.com/

Report •

#5
October 23, 2012 at 12:46:27
 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-ENTERelse 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 I6MIKEhttp://www.skeptic.com/

Report •

#6
October 23, 2012 at 13:17:19
 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) where your_range can be anything from a single cell to an entire sheet.As written it will return an F if there are no grades in your_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.