# Office 365 >  >  How do I write an "If then" formula with multiple arguments using letters and numbers.

## rldrummer40

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"))))))

----------


## AliGW

Welcome to the forum.  :Smilie: 

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
C
D
E
F

*1*
A
4





*2*
B
3


A
4

*3*
C
2


1.9
2

*4*
D
1


C
2

*5*




2.3
2.3

*6*
1.4
1.5


3.4
3.5

*7*
1.6
1.7


3.5
3.5

*8*
1.8
2


4
4

*9*
2.1
2.3


D
1

*10*
2.4
2.5


B
3

*11*
2.6
2.7





*12*
2.8
3





*13*
3.1
3.3





*14*
3.4
3.5





*15*
3.6
3.7





*16*
3.8
4






Sheet: *Sheet1*



*Excel 2016 (Windows) 32 bit*

F

*2*
=IF(ISTEXT(E2),VLOOKUP(E2,$A$1:$B$4,2,0),VLOOKUP(E2,$A$6:$B$16,2,1))


Sheet: *Sheet1*

----------


## rldrummer40

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.

----------


## AliGW

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.

----------


## rldrummer40

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.

----------


## AliGW

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?

----------


## AliGW

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?

----------


## rldrummer40

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.

----------


## AliGW

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.

----------


## rldrummer40

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

----------


## AliGW

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.

----------


## AliGW

FYI I shall be going offline shortly.

----------


## rldrummer40

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.

----------


## rldrummer40

If that's not how you wanted it, please let me know.

----------


## JeteMc

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.

----------


## rldrummer40

I will give this a try.  Thank you!

----------


## rldrummer40

how much would it cost to pay someone to do this?

----------


## AliGW

Pay someone to do what?  :Confused: 

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?

----------

