Forum Discussion

M1___'s avatar
M1___
Copper Contributor
Jul 10, 2022
Solved

Please help with formulas referencing and analyzing data in second tab

I'd like to be able to do analysis on our work schedule to assess for fairness of distribution of shifts, so I'd like help in developing formulas to achieve an example output table. Please see a simplified example of my work schedule format and shift names. In my real schedule, there are over 70 employees and 30 different shift names with shifts and employees being constantly added and removed. I have this schedule in a tab called "Schedule", and I have a second tab called "Summary". I plan to continuously add in the schedule to this table as time goes on, so I'd like to be able to analyze this schedule in a way that tolerates updated data. In the "Summary" tab, I'd like to have data that be able to assess for each staff member, 1. the number of each shifts worked, 2. percentage of morning shifts, evening shifts, and 3rd shifts, 3. percentage of certain groups of shifts (e.g percentage of Ab1+Cd2 shifts).

Would you please help me figure out formulas for the cells in what in my summary table is B2:B10 that reference the schedule in the "Schedule" tab? Feel free to give any suggestions for improving the summary table. 

 

Thank you so much in advance!

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    M1___ 

    I would do it with Power Query and Power Pivot. Are you familiar with these built-in Excel tools?

    Please find and example in the attached file.

Resources