How long "IF" can work? -help

  • Thread starter Thread starter Fawaz
  • Start date Start date
F

Fawaz

Hi,

I'm using the "IF" command to automatically set my
students gred from A,B+,B,C+,C,D, and F. It's all work
well until this semester the institution revises the
gredding scheme to A,A-,B+,B,B-,C+,C,C-,D+,D, and F.

My problem is that I can't seems to program more than 8 (9
if including the final arguments) but i need to be able to
put 11 arguments in the formula.

Is 9 the final limit of the "IF" command in Excel 2000?

How to overcome this problem and how about Excel 2002/XP?

If anybody can clarify this? thank you.

Fawaz,
Malaysia.
 
Try using VLOOKUP() ?
its much easier to maintain
& you don't have to worry about IF() limits
(think the IF() limit is still 8)

Set up your grade reference table in say D1:E11,
for example:

0 F
40 D
45 D+
50 C-
55 C-
60 C+
65 B-
70 B
75 B+
80 A-
85 A

Name this range D1:E11 as say: GradeTable

To name the range, select D1:E11
Click inside the namebox next to the formula bar
(the one with the drop arrow just to the left)
Type: GradeTable
Press Enter

Now assuming the student marks are in col A, A2 downwards

put in B2: =VLOOKUP(A2,GradeTable,2,TRUE)
copy down col B as far as required

Col B will return the grades corresponding to the marks in col A
 
You can only nest 7 functions.

This is custom made for a lookup table. Put your grades and
thresholds in, say, columns A:B of Sheet2:

A B
1 0 F
2 60 D
3 67 D+
4 70 C-
5 73 C
....
11 93 A

Then if your score is in Sheet1, column A, use VLOOKUP() to return
the grade:

A1: 95
B1: =VLOOKUP(A1, Sheet2!A:B, 2, TRUE) ===> A

A2: 71
B2: =VLOOKUP(A2, Sheet2!A:B, 2, TRUE) ===> C-
 
Back
Top