Weighted Average For Technician Efficiency

[reydar]

Hello,

I am attempting to design a report to track technician efficiency. I did some research on weighted averages but I guess I'm not that smart because the results don't look right to me. Perhaps you, being a smart person, would like to help me? Thanks in advance!

We have a mechanic shop. What I am looking to do is determine their efficiency on each task we assign, as well as a weighted average efficiency for the technician. I'd also like to generate a weighted average for all technicians who report to a foreman. And lastly I'd like to generate an efficiency for the entire shop.

Please check out the attached sheet and let me know if this is possible.

Thanks again!

[6StringJazzer]

You want to calculate an average percentage, which is a little different than a weighted average problem, and actually easier. You have the right idea: For the second tech, the average you have calculated is 454%, which is out of whack because one job was 500% but it was only a 1-hour job. If you calculate the percentage using the sums of the numbers, it is the same idea as a weighted average but easier to calculate.

To calculate an overall average for a tech, using the second one as an example:

=SUM(E23:E34)/SUM(D23:D34)

This gives 107% (instead of 454%).

To calculate the average for a foreman, you would have to do the same thing, adding all of the Planned Hours and dividing by the sum of the Actual Hours for all the techs under that foreman.

[joeu2004]

You want to calculate an average percentage, which is a little different than a weighted average problem

Actually, we can calculate the weighted average of percentages. I demonstrate that in I35, which has the formula:

=SUMPRODUCT(G23:G34,D23:D34) / SUM(D23:D34)

The key with weighted averages is to determine what to use for weights: planned hours (column E) or actual hours (column D).

The answer is: actual hours (column D).

This can be seen by understanding the calculation of the average using the raw data, to wit: (total planned hours) / (total actual hours).

Since "total actual hours" is the denominator for the "raw" average, it must be the denominator for the weighted average.

-----

That said, I agree with 6StringJazzer: given the "raw" data, it is easier to calculate the average directly, not relying on weights.

And it might be easier to maintain those averages by expanding the ranges to include non-numeric data above and below each region to be averaged. Thus, the formulas update automagically if we use Insert and Delete to update the numeric data.

For example, see the formulas in H3, H6 and H17.

Also note that the displayed percentages are rounded, based on the cell format. For example, the displayed shop average in H3 as 102%; but it is actually 102.399553571429%.

If we want to round the actual value to an "integer" percentage, the formula would be =ROUND(SUM(E5:E53)/SUM(D5:D53), 2).

We round to 2 decimal places because a percentage like 123.4567% is the decimal number 1.234567.

Similarly, we would round to 4 decimal places if we want to round to 2 "percentage decimal places"; that is, 123.46%.

[reydar]

Thanks smart people. That's exactly what I was looking for!

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.