Solved

Excel formula to compute letter grade with both numbers and letters

Posted on 2013-11-12
5
292 Views
Last Modified: 2013-11-12
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
Comment
Question by:adembo
5 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 100 total points
ID: 39642837
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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 39642848
=IF(ISNUMBER(A2),IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D",)))),A2)
0
 
LVL 26

Accepted Solution

by:
pony10us earned 300 total points
ID: 39642859
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
 
LVL 2

Author Comment

by:adembo
ID: 39642895
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39642921
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now