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 & I5

example

G5 H5 I5 J5
A C B Answer would be A

Any ideas?


See More: is a Nested IF statement the answer?

Report •

✔ Best 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.

MIKE

http://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)))

MIKE

http://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

http://www.skeptic.com/


Report •

#3
October 23, 2012 at 08:35:16
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


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 will
never be the highest grade.

I'll work on a new formula that does not require a place holder.

MIKE

http://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-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

http://www.skeptic.com/


Report •

#6
October 23, 2012 at 13:17:19
✔ 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

http://www.skeptic.com/


Report •

#7
October 23, 2012 at 13:47:25
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.


Report •

Ask Question