Solved

Excel formula to compute letter grade with both numbers and letters

Posted on 2013-11-12
5
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 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