+ Reply to Thread
Results 1 to 1 of 1

Calculate %age of participants with 66% atttendance using two different files

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Miami, US
    MS-Off Ver
    Excel 2007
    Posts
    83

    Calculate %age of participants with 66% atttendance using two different files

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1