Hi,
I have the below worksheet that is used to record staff training attendance and I want to include a formula at D/E/F:9 that returns a compliance percentage based on all staff (Count A10:A20) per course, and takes into consideration a number of things....
A. The start date of the employee and the "weeks to complete from appointment" (row 8) - For example, staff member 7 started on 01/08/20 and "course 2" allows for 6 weeks for the training to be attended. Therefore although the cell is blank they will not impact the percentage until the 6 weeks is up. As "course 1" requires completion in 1 week and the cell is blank they will have an impact on the percentage because that timeframe has elapsed from their start date.
B. The frequency the course should be attended (row 7). For example, staff 5 is out of date for course 1 as the renewal period is 12 months, but is compliant for course 3 as the renewal period is 36 months.
C. Where staff are marked as N/A they do not affect the overall percentage/compliance of that course.
I previously used =COUNT(F10:F75)/(COUNTIF($A10:$A20,"*")-COUNTIF(F10:F20,"*N/A*")) to calculate total staff with a date of completion minus those marked as N/A - however, this no longer does what I want.
Any help would be greatly appreciated.
a
I have the below worksheet that is used to record staff training attendance and I want to include a formula at D/E/F:9 that returns a compliance percentage based on all staff (Count A10:A20) per course, and takes into consideration a number of things....
A. The start date of the employee and the "weeks to complete from appointment" (row 8) - For example, staff member 7 started on 01/08/20 and "course 2" allows for 6 weeks for the training to be attended. Therefore although the cell is blank they will not impact the percentage until the 6 weeks is up. As "course 1" requires completion in 1 week and the cell is blank they will have an impact on the percentage because that timeframe has elapsed from their start date.
B. The frequency the course should be attended (row 7). For example, staff 5 is out of date for course 1 as the renewal period is 12 months, but is compliant for course 3 as the renewal period is 36 months.
C. Where staff are marked as N/A they do not affect the overall percentage/compliance of that course.
I previously used =COUNT(F10:F75)/(COUNTIF($A10:$A20,"*")-COUNTIF(F10:F20,"*N/A*")) to calculate total staff with a date of completion minus those marked as N/A - however, this no longer does what I want.
Any help would be greatly appreciated.
