Solved

Excel formula to compute letter grade with both numbers and letters

Posted on 2013-11-12
5
286 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 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

17 Experts available now in Live!

Get 1:1 Help Now