+ Reply to Thread
Results 1 to 6 of 6

Problem With If Multiple If For Alphablets

  1. #1
    Registered User
    Join Date
    10-30-2007
    Location
    AURANGABAD
    Posts
    2

    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
    Attached Files Attached Files
    Last edited by shg; 10-30-2007 at 11:25 AM. Reason: delete email address

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373
    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. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663
    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. #4
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211
    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"))))))))
    Last edited by shg; 10-30-2007 at 11:41 AM. Reason: deleted quote

  5. #5
    Registered User
    Join Date
    10-30-2007
    Location
    AURANGABAD
    Posts
    2

    Wink

    Thanks Pals For The Help,

    Ill Check Out Today And Get Back,,,


  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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