Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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
0
adembo
Asked:
adembo
3 Solutions
 
NBVCCommented:
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, SyedEngineerCommented:
=IF(ISNUMBER(A2),IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D",)))),A2)
0
 
pony10usCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now