I spent a long time in excel playing around with this but just can't get it.
Consider the following scenario:
- A construction yard has a number of supervisors.
- Each supervisor has a different number of workers
- A supervisor may be responsible for more than one site
- Supervisors are regularly inspected using a checklist
- checklist results may be negative or positive depending on findings
The management want to output graphs to show which are their best/worst performing supervisors.
What is a fair way to work this out given that the number of workers is different and the number of sites can be different
e.g.
Code:
supervisor bob : 10 workers : 2 sites : 9 points : score=??how to calculate??
supervisor john : 10 workers : 1 site : 9 points : score=??how to calculate??
In the example above, "bob" should score higher because he has more worksites to look after.
kindly have a look at the attachment. i'm at a dead end.