Excel 2007 : Help with Text in formulas and IF statements

**master.osgood** · 07-08-2010, 10:35 AM

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!

**NBVC** · 07-08-2010, 12:02 PM

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