+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Help with Text in formulas and IF statements

  1. #1
    Registered User
    Join Date
    06-22-2010
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    24

    Smile Help with Text in formulas and IF statements

    Hello Excel Wizards!

    I have been trying hard to keep from constantly asking this forum for help and I have learnt a lot on my own - but I am stuck with a couple of things atm.

    Firstly I have this code

    =(H13*0.5)+(K13*0.5)

    Nice and simple.. it calculates exam and coursework marks. Sometimes though people don't submit exams or courseworks and so NS (non submittal) is entered and it screws up the code. I tried to do some kind of IGNORE IF NS formula but failed miserably.

    Any ideas?

    Then.. I have this behemoth..

    =IF(AND(H13=0,K13=0),"",IF(AND(H13<39.49,K13<39.49),"RM",IF(H13<39.49,"RC",IF(K13<39.49,"RE",IF(H13="NS","NS",IF(K13="NS","NS",IF(H13="EX","EX",IF(K13="EX","EX", "" ))))))))

    This one basically looks at the final exam and coursework marks and determines whether the course code should be RE (resit exam) RC (resit coursework) RM (resit module(Both)) EX(exempted) NS (non submittal)

    I thought I had this finally figured out but say someone's mark was 50 and the other was NS, the final result is NS but I would prefer having the respective resit code (RE or RC being shown).

    This is just a bonus question for any genius' out there and I look forward to any help anyone can give.. or suggestions!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Help with Text in formulas and IF statements

    Try:

    Please Login or Register  to view this content.
    for the second item.. probably best to create a table of values.

    See attached...

    The table lists the lower bounds of each number range and then the LETTER combos in alphatic order.

    fill the table with the values you want returned when H13 is found in column A and K13 is found in Row 1.

    Then the formula in G14 finds the intersection and returns what's there:

    =INDEX($B$2:$F$6,MATCH(H13,$A$2:$A$6),MATCH(K13,$B$1:$F$1))
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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