Hi everyone,
I have to calculate number of participants who attended 66% of available classes.
We use two different files to do that. From one file named BUHS (attached), we get total number of classes offered in that month for each event. From second file (CU), we calculate how many classes a participant went to. Finally, we compute average.
The way I do it now is:
File 1) BUH: Calculate total number of classes offered in a month.
1) Separate and create new tabs for each month.
2) Create pivot table using event name (row label), event date (column label) and total units (value).
3) Copy paste it special in other tab.
4) Insert column between the event name and the first attendance date and name it "classes".
5) For each event, insert the following formula into the cell in the classes column: =COUNTIF(RANGE,”>0”)
This gives me total number of classes. (If I can have Macro here)
File 2) CU: Calculate total number sessions attended by each participant
1) Separate and create new tabs for each month.
2) Create pivot table with client name (row label), service date (column label), units (value).
3) Highlight pivot table and do copy, paste special, value.
4) Move grand total next to participant name.
5) Insert column after client name and call it "event"
6) Use VLOOKUP to insert service type from the original downloaded data table (column K). (If we cannot get Macro for whole thing, can I have for this sheet also)
7) Insert data of total number of classes in column next to event type (Calculated in File 1)
8) Compute average and transform to percentage for each participant.
Thanks in advance.
Ajang
Bookmarks