# EXCEL: Formula not working

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### sachit64

I'm trying to calculate my GPA for each of my courses in university, so I devised a simple forumula which would give me a value based on what GPA I get for what percentage (from the universities website)

% Letter GP
90 - 100 A+ 10
85 - 89 A 9
80 - 84 A- 8
75 - 79 B+ 7
70 - 74 B 6
66 - 69 C+ 5
60 - 65 C 4
55 - 59 D+ 3
50 - 54 D 2
40 - 49 E 1
0 - 39 F 0

NOTE: My university is weird and uses a scale of 10. And I'll be replacing the GP value and putting the letter grade in another cell.

I created this formula:

=IF((AND(0<B8,B8<39)),0, IF((AND(40<B8,B8<49)),1, IF((AND(50<B8,B8<54)),2, IF((AND(55<B8,B8<59)),3, IF((AND(60<B8,B8<65)),4, IF((AND(66<B8,B8<69)),5, IF((AND(70<B8,B8<74)),6, IF((AND(75<B8,B8<79)),7, IF((AND(80<B8,B8<84)),8, IF((AND(85<B8,B8<89)),9, IF((AND(90<B8,B8<100)),10,"")))))))))))

B8 is highlighted blue, but it stops at IF((AND(75<B8,B8<79)) and it doesn't even show the 90-100 IF statement. It just cuts off.

Any idea?

#### cristobal03

There are probably more than 256 (or is it 255?) characters in your formula. I'm pretty sure formula length is limited to one of those two. Someone who's more familiar with Excel will have to verify that.

chris.

This could be accomplished more easily using a table of values and VLOOKUP, but I don't have time to draft an example of what I mean. If nobody else does, I might get a chance tomorrow.
[/edit]

#### Rollin_Again

Excel can only handle 7 nested IF statements. You will need to use some VBA to create a custom function instead.

You can also work around this problem by using "Defined Names" to reference different parts of the formula.

http://www.cpearson.com/excel/nested.htm

Rollin

#### cristobal03

Oh yeah, I forgot about that.

Disregard my previous post. But I am pretty sure there's a limit to formula length. It might be 1024 characters.

chris.

#### exegete

Or the better, no VBA solution is to use a lookup table and use formulas to look up the value, and then you have no limits (1,024 characters for formulas), and no need to worry about VBA.

#### bomb #21

I can't for the life of me remember where I heard about doing it as follows, but you might want to give it a whirl ...

(with score in A1) ...

=INDEX({"F";"E";"D";"D+";"C";"C+";"B";"B+";"A-";"A";"A+"},MATCH(A1,{0;40;50;55;60;66;70;75;80;85;90}))

for the letter, and

=INDEX({0;1;2;3;4;5;6;7;8;9;10},MATCH(A1,{0;40;50;55;60;66;70;75;80;85;90}))

for the 10 - 0 thing.

Are you sure the break point for C+ is 66 (i.e. not 65)?

#### sachit64

I appreciate all the help.

I skipped VBA, but that second math seems understandable, but how do I NAME formulas and then how would i use it? Think someone could make the example in a file and upload?

bomb #21... I honestly have no clue how I'd use that. And yes I'm sure the percentages are like that. They alternate to every X4 and then X5. No clue why.

#### bomb #21

See attached -- score goes in A1, results are in B1 and C1. The table in E:G is just for show/cross-reference.

#### Attachments

• 13.5 KB Views: 324

#### sachit64

Works perfectly thanks.

And after looking at how you did your cross reference table, I better understand why you asked that question. I think 66% is the equivelent of the 50% for this university, so they just raised it a percentage.

Thanks again.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On