Hi everyone.
I just registered to ask the gurus here for some help.
I'm trying to make a system to record the attendance in some classes. These classes can either be in group (most common) or individual.
When a student misses a individual class and attend one in a group (exceptional) in a way to be compensated, then the time only counts as half.
For example: group duration is often 2h and if a student who doesn't belong to that group (because he has individual classes) attends it, then it only counts as 1h to him. The system must be able to "see" the student attended a group, but to not count his presence in the group.
The sum of hours each student has in a month it's separated by "IND" (for individual) and "GRP" (for group).
The problem I'm facing it's I can't seem to subtract the presence of that student (who has individual classes) by month.
If a student goes to a group class in September and another one in October, the system is counting twice the September one in October and vice-versa.
I'm going to show the formulas I currently have:
E11: =SUMIFS($AA11:$ND11;$AA$9:$ND$9;9;$AA$10:$ND$10;"IND")+SUMIF(AB11:HV11;"ci";AA11:HV11)
F11: =SUMIFS($AA11:$ND11;$AA$9:$ND$9;9;$AA$10:$ND$10;"GRP")-SUMIFS($AA11:$ND11;$AA$9:$ND$9;9;$AA$11:$ND$11;"ci")
AA11: =IF(OR(AB11="x";AB11="f";AB11="c");AA$8;IF(AB11="ci";AA$8*0,5;""))
x means full attendance; f means missed class; c means doesn't belong to that group but has group classes; ci means it's a student who has individual classes and compensated on a group one.
So, in E11 I'm trying to SUM if the individual student compensated on a group, in F11 i'm subtracting his presence from the group.
Please, see the images below. Thanks for any help.
AA11
E11
F11
I just registered to ask the gurus here for some help.
I'm trying to make a system to record the attendance in some classes. These classes can either be in group (most common) or individual.
When a student misses a individual class and attend one in a group (exceptional) in a way to be compensated, then the time only counts as half.
For example: group duration is often 2h and if a student who doesn't belong to that group (because he has individual classes) attends it, then it only counts as 1h to him. The system must be able to "see" the student attended a group, but to not count his presence in the group.
The sum of hours each student has in a month it's separated by "IND" (for individual) and "GRP" (for group).
The problem I'm facing it's I can't seem to subtract the presence of that student (who has individual classes) by month.
If a student goes to a group class in September and another one in October, the system is counting twice the September one in October and vice-versa.
I'm going to show the formulas I currently have:
E11: =SUMIFS($AA11:$ND11;$AA$9:$ND$9;9;$AA$10:$ND$10;"IND")+SUMIF(AB11:HV11;"ci";AA11:HV11)
F11: =SUMIFS($AA11:$ND11;$AA$9:$ND$9;9;$AA$10:$ND$10;"GRP")-SUMIFS($AA11:$ND11;$AA$9:$ND$9;9;$AA$11:$ND$11;"ci")
AA11: =IF(OR(AB11="x";AB11="f";AB11="c");AA$8;IF(AB11="ci";AA$8*0,5;""))
x means full attendance; f means missed class; c means doesn't belong to that group but has group classes; ci means it's a student who has individual classes and compensated on a group one.
So, in E11 I'm trying to SUM if the individual student compensated on a group, in F11 i'm subtracting his presence from the group.
Please, see the images below. Thanks for any help.
AA11
E11
F11
