Tivpine Anande
New Member
- Joined
- Aug 24, 2005
- Messages
- 45
Hi All,
I use Excel to grade my students. I am currently looking to merging two formula's in other to have one formula running through a row in a work sheet.
I have this formula IF(G10="",G$8&" ","") which ensures that all compulsory courses are taken by each student, what I have asked this formula to do for me is; if a cell is blank it should return the course code as 'Register' for short 'REG' i.e. REG - LAW 111. This formula in my remarks column 'IF(CD10="","DECEASED",IF(CD10=0,"VOLUNTARY WITHDRAWAL",IF(CD10<1,"PROBATION",IF(AND(CX10="",DH10=""),DM10,IF(CX10="","","RPT - "&CX10)&IF(DH10="","","REG - "&DH10))))) which clearly picks from two auxiliary columns to generate a Remarks column and in some instances a combination of both particularly if the student failed and needs to register a course i.e. RPT - LAW 122 REG - LAW 111.
Furthermore, in the new academic session, I have this formula IF(AND('100 Level'!G10="",AO10>40),,IF('100 Level'!G10="",AO$8&" ",IF(OR(AO10="",AO10="ABS",AO10<>39),"",AO10))) which looks at the previous session or course determines whether the student Failed (as in LAW 111...score 23%) or was ABS (Absent as in LAW 122) in either case the formula should return either of the two situations so I know that the student was absent at a course he or she should have taken or failed so that they re-register in the new academic session. Notice, the five row of compulsory courses and grades below...also notice that no blank cells:
LAW LAW SOC
111 GP 122 GP 113 GP
3 3 3
1. 43 1 ABS 0 43 1
2. 60 4 75 5 58 3
3. 45 2 48 2 46 2
4. 23 0 48 2 48 2
5. 46 2 55 3 57 3
The next row of examination grades includes both compulsory and elective courses (last three, POL 217, SOC 215 and POL 228)...here students are free to chose any two courses out of the three courses. For clarity all courses have a credit load of 3 in this instance...this formula, IF(BQ10="","",IF(BQ10="ABS",0,IF(BQ10<40,0,IF(BQ10<45,1,IF(BQ10<50,2,IF(BQ10<60,3,IF(BQ10<70,4,IF(BQ10<100,5,"")))))))) generates GP which is between 1-5.
LAW LAW SOC POL SOC POL
111 GP 122 GP 113 GP 217 GP 215 GP 228 GP
3 3 3 3 3 3
1. 43 1 ABS 0 43 1 40 1 46 2
2. 60 4 75 5 58 3 75 5 54 3
3. 45 2 48 2 46 2 52 3
4. 23 0 48 2 48 2 50 3 43 1
5. 46 2 55 3 57 3 51 3 53 3
The problem I am having is, if I have students who decide to take an alternative course (two out if three electives) it means I will have a blank cell within a row of compulsory courses, this formula ignores IF(AND('100 Level'!G10="",AO10>40),,IF('100 Level'!G10="",AO$8&" ",IF(OR(AO10="",AO10="ABS",AO10<>39),"",AO10)))blank cells but this formula IF(G10="",G$8&" ","") does not...it returns the blank cell course code (i.e. SOC 215 row 1) as a REG course.
I shall greatly appreciate any help I can get.
Warm Regards
Tivpine
I use Excel to grade my students. I am currently looking to merging two formula's in other to have one formula running through a row in a work sheet.
I have this formula IF(G10="",G$8&" ","") which ensures that all compulsory courses are taken by each student, what I have asked this formula to do for me is; if a cell is blank it should return the course code as 'Register' for short 'REG' i.e. REG - LAW 111. This formula in my remarks column 'IF(CD10="","DECEASED",IF(CD10=0,"VOLUNTARY WITHDRAWAL",IF(CD10<1,"PROBATION",IF(AND(CX10="",DH10=""),DM10,IF(CX10="","","RPT - "&CX10)&IF(DH10="","","REG - "&DH10))))) which clearly picks from two auxiliary columns to generate a Remarks column and in some instances a combination of both particularly if the student failed and needs to register a course i.e. RPT - LAW 122 REG - LAW 111.
Furthermore, in the new academic session, I have this formula IF(AND('100 Level'!G10="",AO10>40),,IF('100 Level'!G10="",AO$8&" ",IF(OR(AO10="",AO10="ABS",AO10<>39),"",AO10))) which looks at the previous session or course determines whether the student Failed (as in LAW 111...score 23%) or was ABS (Absent as in LAW 122) in either case the formula should return either of the two situations so I know that the student was absent at a course he or she should have taken or failed so that they re-register in the new academic session. Notice, the five row of compulsory courses and grades below...also notice that no blank cells:
LAW LAW SOC
111 GP 122 GP 113 GP
3 3 3
1. 43 1 ABS 0 43 1
2. 60 4 75 5 58 3
3. 45 2 48 2 46 2
4. 23 0 48 2 48 2
5. 46 2 55 3 57 3
The next row of examination grades includes both compulsory and elective courses (last three, POL 217, SOC 215 and POL 228)...here students are free to chose any two courses out of the three courses. For clarity all courses have a credit load of 3 in this instance...this formula, IF(BQ10="","",IF(BQ10="ABS",0,IF(BQ10<40,0,IF(BQ10<45,1,IF(BQ10<50,2,IF(BQ10<60,3,IF(BQ10<70,4,IF(BQ10<100,5,"")))))))) generates GP which is between 1-5.
LAW LAW SOC POL SOC POL
111 GP 122 GP 113 GP 217 GP 215 GP 228 GP
3 3 3 3 3 3
1. 43 1 ABS 0 43 1 40 1 46 2
2. 60 4 75 5 58 3 75 5 54 3
3. 45 2 48 2 46 2 52 3
4. 23 0 48 2 48 2 50 3 43 1
5. 46 2 55 3 57 3 51 3 53 3
The problem I am having is, if I have students who decide to take an alternative course (two out if three electives) it means I will have a blank cell within a row of compulsory courses, this formula ignores IF(AND('100 Level'!G10="",AO10>40),,IF('100 Level'!G10="",AO$8&" ",IF(OR(AO10="",AO10="ABS",AO10<>39),"",AO10)))blank cells but this formula IF(G10="",G$8&" ","") does not...it returns the blank cell course code (i.e. SOC 215 row 1) as a REG course.
I shall greatly appreciate any help I can get.
Warm Regards
Tivpine
