Excel formula to compute letter grade with both numbers and letters

Hello, I am having what I hope is a simple fix for an Excel guru. I have a excel sheet attached that has a column of numbers that represent a percentage and then the formula that assigns the letter grade appropriate to the letter. That part works great. Problem is that I also have letters in the percentage column and that throws my formula off. If I create a formula all by itself, I can get it to display the S in the adjacent cell, but when I try to incorporate that into the same formula it doesn't work.

So if it has a S or an I in the A column, it should just put that same letter in Column B. Now its putting in an A in the B Column. Note the end of my formula that I am trying to add in the if it contains S then give it S, but that's obviously incorrect.

I would appreciate any suggestions.
Example.xlsx
LVL 2
ademboAsked:
Who is Participating?
 
pony10usConnect With a Mentor Commented:
Or:

=IF(A2="S","S",IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D",IF(A2>=50,"F",0))))))


I did notice that while playing with the formula I picked B9 to test with and it was comparing A2 to "S" instead of A9.  Just a little heads up.

Another thing to keep in mind is that you are limited to 7 nests (at least until Excel 2012) and you are currently doing 6 after I removed one.
0
 
NBVCConnect With a Mentor Commented:
Try:

=IF(ISNUMBER(A2)+0,IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D",IF(A2>=50,"F",0))))),A2)

or

=IF(ISNUMBER(A2)+0,LOOKUP(A2,{0,50,60,70,80,90},{0,"F","D","C","B","A"}),A2)
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
=IF(ISNUMBER(A2),IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D",)))),A2)
0
 
ademboAuthor Commented:
pony10us, so my formula was just backwards? So I have to have it look for the letter first? I am using Excel 2013 and probably just the copy and paste for the wrong cell in the formula. I copied it from my one Im working on, but certainly a spot to be mindful of. Thanks for noticing that.

I will award the points to all 3 as they all seem to work, but the majority to pony10us as that was the easiest one for me to maintain going forward. It did work!!
0
 
pony10usCommented:
Glad it works.  It follows what you had.  However any of the formulas given would work so glad you split up the points.
0
All Courses

From novice to tech pro — start learning today.