I am trying to calculate productivity across multiple therapists, each having their own sheet with their individual productivity being calculated as such:
=IF(G61>0,G59/4/G61*100,0)
This gives me their productivity for the day assuming their hours worked (G61) is greater than 0. This is calculated for the week as well.
So each therapist has this formula, but i need to calculate the total discipline productivity, excluding therapists in the average when they are absent.
I tried this:
=SUM(JULIE!G62,MEGAN!G62,ASHLEY!G62,CATHY!G62,'PT PRN'!G62)/COUNTIF(JULIE!G62,MEGAN!G62,ASHLEY!G62,CATHY!G62,'PT PRN'!G62,">0")
which obviously is not working
I also tried:
=AVERAGEIF(JULIE!G62,MEGAN!G62,ASHLEY!G62,CATHY!G62,'PT PRN'!G62,">0")
BUT, Excel seems to not allow the AVERAGEIF function to work across multiple sheets.
So here i am, seeking a formula that will give me the discipline average productivity, not including 0. The issue im having is that if a staff is off, say we do not use a PRN for the whole week, that productivity is 0. This is fine, but when the average is calculated, i need it to NOT include them, so instead of dividing by 5, it would only divide by 4, since that staff was off they need not be included. When its dividing by 5, when someone is off, its not giving the accurate productivity and i am at a loss.
Sorry, i have been teaching myself how to use excel, and i definitely do not get paid to do this sort of work, but its something the director of rehabilitative services has been needing and it seems im the only one with any computer sense at this point.
Any help would be amazing, i cant seem to figure it out on my own.
=IF(G61>0,G59/4/G61*100,0)
This gives me their productivity for the day assuming their hours worked (G61) is greater than 0. This is calculated for the week as well.
So each therapist has this formula, but i need to calculate the total discipline productivity, excluding therapists in the average when they are absent.
I tried this:
=SUM(JULIE!G62,MEGAN!G62,ASHLEY!G62,CATHY!G62,'PT PRN'!G62)/COUNTIF(JULIE!G62,MEGAN!G62,ASHLEY!G62,CATHY!G62,'PT PRN'!G62,">0")
which obviously is not working
I also tried:
=AVERAGEIF(JULIE!G62,MEGAN!G62,ASHLEY!G62,CATHY!G62,'PT PRN'!G62,">0")
BUT, Excel seems to not allow the AVERAGEIF function to work across multiple sheets.
So here i am, seeking a formula that will give me the discipline average productivity, not including 0. The issue im having is that if a staff is off, say we do not use a PRN for the whole week, that productivity is 0. This is fine, but when the average is calculated, i need it to NOT include them, so instead of dividing by 5, it would only divide by 4, since that staff was off they need not be included. When its dividing by 5, when someone is off, its not giving the accurate productivity and i am at a loss.
Sorry, i have been teaching myself how to use excel, and i definitely do not get paid to do this sort of work, but its something the director of rehabilitative services has been needing and it seems im the only one with any computer sense at this point.
Any help would be amazing, i cant seem to figure it out on my own.
