I'm trying to say A = 4, B = 3, C = 2, D = 1; 100-90 = 4, 89-80 = 3, 79-70 = 2, 69-60 = 1, 59-0 = 0; 4.00-3.80 = 4, 3.79-3.6 =3.7, 3.59-3.40 = 3.5, 3.39-3.10 = 3.3, 3.09-2.80 = 3.0, 2.79-2.60 - 2.7, 2.59-2.40 = 2.5, 2.39-2.10 = 2.3, 2.09-1.80 = 2.0, 1.79-1.60 = 1.7, 1.59-1.40 = 1.5.
So far I've gotten this below. It stops working once I try to do in-between 100-90.
=IF(A2="A","4",IF(A2="B","3",IF(A2="C","2",IF(A2="D","1",IF(A2<>"100-90","4",IF(A2<>"89-80","3"))))))
Welcome to the forum.
This would be easiest done with a lookup table. I've created two tables on the left - these could be in hidden columns or on a separate worksheet:
Excel 2016 (Windows) 32 bit
A B A C D C D B
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi, thank you so much for helping me with this so quickly! I'm sorry for the simple question, but when I plug in the formulas, I notice that the table tables have differnent numbers in the "F2" cells (i.e. 4 and =If(ISTEXT...). I'm not sure where to plug the information exactly and how to use it.
Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ok, I uploaded the attachment. To make sense of your formula, I did a little research to better understand and designed something similar. Unfortunately, it stops working when I try to enter specific numbers such as : 4.00, 3.00, etc. You will in the attachment.
You can't have grade lookups and number lookups in the same lookup table - that's the first problem.
Secondly, number lookups need to be in ascending order.
Thirdly, you do not need to list every number, only the boundaries.
Fourthly, you cannot mix whole numbers and decimals in the lookup in this way.
I think there are probably better ways of doing this where the numbers are concerned.
Are you a teacher?
Last edited by AliGW; 07-20-2019 at 11:59 AM.
I need you to explain to me in WORDS what you are trying to do.
You have at least three distinct grading systems going on here - why?
I see. No, I'm not a teacher. However I'm a mentor trying to help college students get into grad school for Physical Therapy. In order for these students to apply to these schools, they must apply through a company called PTCAS (Phsyical Therapy Centralized Application Service). One of the requirements is to send your transcripts to PTCAS and they want you to enter your grades into their system as well. It costs money to complete this application. So, I'm trying to design a GPA calculator to help students save time and money by checking their GPA before they send it. They might see that their GPA is too low and decide not to send it. So, in order to standardize grades and ensure everyone is on a equal playing field, PTCAS associated transcript grades with points (A = 4, 4.0 = 4, 100 = 4, B = 3, 89 = 3, 3.79 = 3, etc). Some people's transcripts have letter grades and others have number grades. I'm designing a calculator that associate the correct PTCAS point with their letter or number grade on their transcript. I'm not allowed to post links yet, but if you want to go PTCAS grading chart you have to type in ptcas dot org forward slash GPA forward slash. Hopefully this makes sense.
Last edited by rldrummer40; 07-20-2019 at 12:15 PM. Reason: typo
OK - that helps.
It's as I thought initially, and the first solution I offered you should work.
Can you please provide me with a set of sample grades that you want converting? I will then set it up for you.
Thanks so much for your help! Is this what need?
A = 4, B = 3, C = 2, D = 1; 100-90 = 4, 89-80 = 3, 79-70 = 2, 69-60 = 1, 59-0 = 0; 4.00-3.80 = 4, 3.79-3.6 =3.7, 3.59-3.40 = 3.5, 3.39-3.10 = 3.3, 3.09-2.80 = 3.0, 2.79-2.60 - 2.7, 2.59-2.40 = 2.5, 2.39-2.10 = 2.3, 2.09-1.80 = 2.0, 1.79-1.60 = 1.7, 1.59-1.40 = 1.5
No.
Please give me a selection of grades that your students might find on their scripts. Forget about the grade boundaries for now.
Include 5 from each of the systems you are using.
In the adjacent column type the grade or number you expect this to convert to.
All of this manually - no attempted formulae.
FYI I shall be going offline shortly.
sorry for the delay, I had to change locations and couldn't login on my phone. I'll be able to respond a lot faster as I did previously. I think this is what you're asking for.
If that's not how you wanted it, please let me know.
Building on Ali's idea:
1. Letter grade range in A2:B5
2. Ten point scale in A6:B10
3. Formula for GPA is: =IF(ISTEXT(D2),INDEX(B$2:B$5,MATCH(D2,A$2:A$5,0)),IF(D2>4,INDEX(B$6:B$10,MATCH(D2,A$6:A$10)),ROUNDDOWN(D2,1)))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I will give this a try. Thank you!
how much would it cost to pay someone to do this?
Pay someone to do what?
The help here comes for free.
How did you get on with JeteMc's suggestion? Is there a problem with his solution? If so, what is it? Can you attach a sample workbook showing the issues and one of us can try to iron it out?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks