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!
Try:
for the second item.. probably best to create a table of values.=IF(AND(H13="NS",K13="NS"),"",IF(H13="NS",K13*0.5,IF(K13="NS",H13*0.5,H13*0.5+K13*0.5)))
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))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks