Definitely need someone's expert advice on how to calculate the percentage of units used.
I have 2 tables pulling information from Sharepoint.
One table (AttendeeList) includes the # of units allowed for a full week. (2 units per day)
The other table (DatabyDay) is the recorded attendance by day. Each entry in this table can be counted as 1 unit.
Consider that any date with data is an 'active date' or 2 possible units for attendance.
I need to calculate by week by person the percentage of units used. It's fairly simple if there are 5 full days each week.
for example: John is alloted 10 units per week. He attends morning and afternoons each day monday thru friday. He uses 100% of his alloted units.
But...if there are only 4 days that week...he only uses 8 units however...with only 4 days in the week it's only possible to use 8 units. So he still uses 100%.
Things get complicated when there are a different number of possible units.
I'm wondering if I need to add an additional calculation somewhere that will count the number of possible units in the week?
And although the majority are alloted 10 units...not all are. So I need to be able to calculate for those people as well.
I am attaching some sample data and a start of a pivot table in a workbook. I'm open to suggestions or ideas on how to create an accurate calculation. It doesn't have to be in a pivot table...I just started there.
Thank you for your time!
Bookmarks