# Problem With If Multiple If For Alphablets

1. ## Problem With If Multiple If For Alphablets

Dear Friends,

I am having a problem with excel formulas and need your help in this regard. Though I have got most of the formula, I just need some help to further structure it.

I am making a grade sheet and need to generate GRADES from MARKS. In the MARK LIST the following numbers / alphabets can be seen

25,45,85,65,65,and numbers which are the marks obtained.

AB is marked wherever ABSENT is there.

and in some places instead of marks directly the GRADES are entered, like S, A, B, C, D, and F.

So in the file the MARKLIST and GRADELIST are side by side columns, and GRADESHEET

COLUMN is used to display the grades as per the formulas entered for the specific mark ranges which are as follows

less than 50 F
50-54 D
55-64 C
65-74 B
75-84 A
85 and above S

and wherever blanks are present the GRADESHEET should display BLANK,

wherever alphabets / Grades are alredy entered in marksheet the Gradesheet should display them as it is.. for.eg. if in marksheet AB is there same should be displayed in Grade sheet.

I have solved the numbers / numerical marks case, need your help with the Alphabets wherever the letters are used i.e A,S, D,C, or AB etc.

=IF(B21>84,"S",IF(B21>74,"A",IF(B21>64,"B",IF(B21>54,"C",IF(B21>49,"D",IF(AND(B21>0,B21<50),"F",B21))))))

I can also forward u the file as sample, for that kindly mail to (email deleted by mod) or file attached herewith as sample.

You can also mail me the solution on (email deleted by mod) or reply to this post as per your convenience.

RAVI  Register To Reply

2. Hi Ravi

Try in C4:

=IF(B4="","",IF(ISNUMBER(B4),LOOKUP(B4,{0,"F";50,"D";55,"C";65,"B";75,"A";85,"S"}),IF(OR(B4={"A","B","C","D","F","S","AB"}),B4,"Unknown")))

Copy down

HTH
lecxe  Register To Reply

3. Ravi,

I have deleted the email address from your post, in the interest of saving you some spam. You may repost it if you don't care.  Register To Reply

4. Try this:

=IF(B4="","",IF(ISTEXT(B4),B4,IF(B4>84,"S",IF(B4>74,"A",IF(B4>64,"B",IF(B4>54,"C",IF(B4>49,"D",IF(B4>0,"F"))))))))  Register To Reply

5. ## Thanks Pals For The Help,

Ill Check Out Today And Get Back,,,        Register To Reply

6. Best practice would be to have a table showing the lower bound of each band and the grade attached, e.g. in G4:H9

0 F
50 D
55 C
65 B
75 A
85 S

Then use this formula in C4 copied down

=IF(B4="","",IF(ISNUMBER(B4),LOOKUP(B4,G\$4:H\$9),B4))

or, without a table, with values hard-coded into the formula

=IF(B4="","",IF(ISNUMBER(B4), LOOKUP(B4,{0,50,55,65,75,85;"F","D","C","B","A","S"}),B4))  Register To Reply