Normalising results from Survey data (looks easy but isn't !)
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
In the example above, "bob" should score higher because he has more worksites to look after.
supervisor bob : 10 workers : 2 sites : 9 points : score=??how to calculate??
supervisor john : 10 workers : 1 site : 9 points : score=??how to calculate??
kindly have a look at the attachment. i'm at a dead end.