SOLVED

Please help with formulas referencing and analyzing data in second tab

Frequent Visitor

image.png

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).

Screen Shot 2022-07-09 at 9.37.29 PM.png

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
best response confirmed by M1___ (Frequent Visitor)
Solution

@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.