# 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

If MARKS are GRADE

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.

Please help me to modify the formula. The current formula is

=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.

THANKS IN ADVANCE ..

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

RAVI

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

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.

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

5. Thanks Pals For The Help,

Ill Check Out Today And Get Back,,,

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1