Average rate of working rostered days

[mamboq]

Hi,

Just wondering if anyone can help me with this statistical question.

An employee is rostered to work 95 days in a 6 month period, but actually works 90 days, meaning his average rate of working when rostered is 94.7% (90/95).

I have the following table in Excel range A1:C6 (comma delimited):

Employee,Days Rostered,Days Worked
1, 110, 108
2, 70, 55
3, 95, 90
4, 125, 125
5, 90, 88.5

(sorry, I don't know how to paste an excel table into a forum message!)

I need to determine the overall average rate of days worked out of days rostered. Initially, I found the rate for each individual employee, then averaged the rates, to arrive at an average rate of 93.9647% - the array formula I used was {=AVERAGE(B2:B6/C2:C6)}. Or, the same formula in a different form: =SUM(98.1818%, 78.5714%, 94.7368%, 100%, 98.3333%)/5

However, I was told that I should have used the formula =SUM(B2:B6)/SUM(C2:C6) which returns 95.2041%

I asked why the average formula was incorrect, but I wasn't told anything other than I should use the SUM/SUM formula above. Can anyone suggest which formula gives the correct average rate for days worked out of rostered days, and why the other formula is incorrect? I've been trying all day to remember my high school mathematics, but I can't understand why my AVERAGE formula is incorrect!

Tim

 

[DonkeyOte]

all to do with weighted averages

If you had an employee who was rostered to work 2 days and worked 1 day then that would generate an individual avg of 50% ...
If you had another employee rostered to work 365 days who worked 364 days then their individual % is 99.7%...

Were you to average the personal averages you get approx. 75%

This is obviously misleading at an aggregate level as you're giving equal weighting to both employees even though the 2nd employee had available 365 of the total of 367 days....

So weight them accordingly... in total 367 days available (2+365) of which 365 were worked (1+364)... so really you're % is: 365/367 => closer to 99.5%

 

[mamboq]

Thanks so much for the informative reply.

One thing that still confuses me a little is that the overall weighted average rate is being displayed with a histogram that shows the grouped, unweighted individual rates, which seems inconsistent to me. That is, the graph shows the number of employees with an individual rate between 0 and 10 percent, 10 and 20 percent and so on, as if they were all equally weighted.

Is the average value I calculated termed an 'arithmetic mean', as opposed to a weighted mean? Whenever I think of the term average or mean, I always used to think of it as being the sum of all data points divided by the count of all data points. I think I need to do some studying in relation to weighted averages - the example you gave was very clear, but the concept still seems a bit hazy to me.

Tim