Hi.
I have an attendance chart with room for 250 students. The tutors in the hospital school program can record an encounter as a "Visit" ("V") or an hour or more tutored ("T", "TT", etc.). Sometimes these happen in combination over the course of a day, or there are multiple visits ("VT", "VV", etc.). An absence is recorded as a 0, but has a value in the code system (counts as a "T").
I need to track the total number of "V"s and "T"s for each student. These then get totaled for the whole student population. My current version (see AugAttendance[Total V] and AugAttendance[Total T]) have the attendance codes hardwired into the formulas and multiply counts of the appropriate codes by their "values."
There has to be a more graceful way. I can create a helper table (see HelperTable) that uses Index & Match functions to pull in appropriate values, but I'd need to do it for both T and then V entries. That seems cumbersome and more like a workaround than a real solution. I've been experimenting with some array formulas, but I can't quite get there.
Ideas? The file can be found at https://www.dropbox.com/s/kgsqsn20w3exh6v/Chandoo%20Example.xlsx
Please put a solution in the "Ideal Total" box, or near it if someone beats you there with something different! Thanks.
I have an attendance chart with room for 250 students. The tutors in the hospital school program can record an encounter as a "Visit" ("V") or an hour or more tutored ("T", "TT", etc.). Sometimes these happen in combination over the course of a day, or there are multiple visits ("VT", "VV", etc.). An absence is recorded as a 0, but has a value in the code system (counts as a "T").
I need to track the total number of "V"s and "T"s for each student. These then get totaled for the whole student population. My current version (see AugAttendance[Total V] and AugAttendance[Total T]) have the attendance codes hardwired into the formulas and multiply counts of the appropriate codes by their "values."
There has to be a more graceful way. I can create a helper table (see HelperTable) that uses Index & Match functions to pull in appropriate values, but I'd need to do it for both T and then V entries. That seems cumbersome and more like a workaround than a real solution. I've been experimenting with some array formulas, but I can't quite get there.
Ideas? The file can be found at https://www.dropbox.com/s/kgsqsn20w3exh6v/Chandoo%20Example.xlsx
Please put a solution in the "Ideal Total" box, or near it if someone beats you there with something different! Thanks.
