kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 922
- Office Version
- 365
Hi,
I have the following:
In column M, I am trying to calculate the productivity of each employer. The formula is multiplying each volume with the average handling time for each task and dividing with the productive hours of the employee for that day . A full day productive hour will be 7.5 hours (450 minutes). The formula also takes into consideration the following:
1. The formula will derive the correct productive hours to be used based on the input in column E, F and G.
2. If column E is 1 and column F is 0.5, then the formula will use 225 minutes as the productive hours (half a day equals to 450/2=225)
3. If column E is 1 and column F is also 1, then no productivity rate will be calculated since the staff is considered absent for the whole day
4. If column E is 1 and column G is 1, this means the employee did 1 hour OT which means his productive hours for the day will be 450+60=510 minutes
In table 2, I have the correct results for the productivity rate for each employee. All scenario is calculating correctly except for Staff1 where the productivity rate should be 69% instead of 6078%. Appreciate any assistance to let me know how to amend the formula to derive the correct result.
I have the following:
| Book1 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | L | M | N | |||
| 2 | Table 1 | ||||||||||||
| 3 | Task | Task1 | Task2 | Task3 | Task4 | Task5 | |||||||
| 4 | Average Handling Time | 10 | 10 | 10 | 10 | 10 | |||||||
| 5 | |||||||||||||
| 6 | Table 2 | ||||||||||||
| 7 | Staff Name | Work | Out Of Office | OT Hours | Volume | Volume | Volume | Volume | Volume | Productivity rate | Correct Result | ||
| 8 | Staff1 | 1 | 1 | 5 | 10 | 5 | 10 | 5 | 6078% | 69% | |||
| 9 | Staff2 | 1 | 0.5 | 5 | 10 | 5 | 10 | 5 | 156% | 156% | |||
| 10 | Staff3 | 1 | 1 | 5 | 10 | 5 | 10 | 5 | #DIV/0! | 0 | |||
| 11 | Staff4 | 1 | 1 | 1 | 5 | 10 | 5 | 10 | 5 | #DIV/0! | 0 | ||
Sheet1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| M8 | M8 | =(((H8*$H$4)+(I8*$I$4)+(J8*$J$4)+(K8*$K$4)+(L8*$L$4))/((E8-F8)*(450))+(G8*60)) |
| M9:M11 | M9 | =((H9*$H$4)+(I9*$I$4)+(J9*$J$4)+(K9*$K$4)+(L9*$L$4))/((E9-F9)*(450))+(G9*60) |
In column M, I am trying to calculate the productivity of each employer. The formula is multiplying each volume with the average handling time for each task and dividing with the productive hours of the employee for that day . A full day productive hour will be 7.5 hours (450 minutes). The formula also takes into consideration the following:
1. The formula will derive the correct productive hours to be used based on the input in column E, F and G.
2. If column E is 1 and column F is 0.5, then the formula will use 225 minutes as the productive hours (half a day equals to 450/2=225)
3. If column E is 1 and column F is also 1, then no productivity rate will be calculated since the staff is considered absent for the whole day
4. If column E is 1 and column G is 1, this means the employee did 1 hour OT which means his productive hours for the day will be 450+60=510 minutes
In table 2, I have the correct results for the productivity rate for each employee. All scenario is calculating correctly except for Staff1 where the productivity rate should be 69% instead of 6078%. Appreciate any assistance to let me know how to amend the formula to derive the correct result.
