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

Thread Starter
Joined
Aug 3, 2000
Messages
245
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?
 
Joined
Aug 5, 2005
Messages
3,086
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.

[edit]
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]
 
Joined
Sep 4, 2003
Messages
4,916
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.

You can read more about this on Chip Pearson's website.

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

Rollin
 
Joined
Aug 5, 2005
Messages
3,086
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.
 
Joined
Oct 26, 2005
Messages
354
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.
 
Joined
Jul 1, 2005
Messages
8,546
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

Thread Starter
Joined
Aug 3, 2000
Messages
245
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.
 

sachit64

Thread Starter
Joined
Aug 3, 2000
Messages
245
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top