0

I'm trying to calculate the average count of employees for a group of companies. I need to exclude months where a a company had no employees. Please verify that my approach is correct.

January February    March   April   May June    July    August  September   October November    December
10      10          10      10      10    10    10      10      10          10      10         10
30      30          30      30      30    30    30      30      30          30      30         30
                                                5       5       5           5       5          5
                                                                                               120

80      80          80      80      80    80    135     135     135         135     135        660

Total Employees=    1815                                        
Total Months=   31                                      


Avg Employees=  58.55

image of formulas

enter image description here

Antarr Byrd
  • 103

1 Answers1

1

Are you wanting straight average or weighted average?

If straight average, I think that your formula on row 8 for each column should be =sum(c2:c5)/c6. That would give you the monthly average. Excel ignores blank cells in its built in functions. So you could just use =average(c2:c5) and it would give you 20 for the average number of employees in January. Or you can do =average(c2:n5) and get the average of all months.

If you are trying to do it as a weighted average, then there are some issues. If you think about the data as occurrences, then a weighted average makes a little more sense. You have 12 months that had 10 employees. 12 months that had 30 employees, 6 months that had 5 employees, and one that had 120 employees. So there were 12+12+6+1 = 31 and there were 120+360+30+120 = 630 employees who worked during those 31 months. So the average is 630/31 = 20.32 employees.

Patrick A.
  • 86
  • I think what I am looking for is the weighted average. However it would not be possible for that to be 20.32 because the lowest value for any given month across all for companies is 40, January. In the calculation how ever the last 2 employers should not count against because that had not employee and business-wise did not exist. – Antarr Byrd Aug 05 '14 at 17:53
  • The average number of employees for Jan-June would be 40/2 or 20 employees. The average for July-Nov would be 45/3 or 15 employees. For Dec the average is 165/4 or 41.25 employees. The lower average months will bring that last month down in a hurry. That is why the average is so low. Weighted average won't change that unless you give a greater weight to the last month but that would have to be something arbitrary. – Patrick A. Aug 05 '14 at 18:10
  • The average from Jan-June would be 40. All of the companies are treated as a single organization, because they are owned by the same person. – Antarr Byrd Aug 05 '14 at 19:01
  • 1
    Ok. So you need to be able to say we had x number of employees working each month. In that case the weighted avg looks fine and the number seems reasonable. If you figured total number of employees divided by 12 you would get about 52 employees. – Patrick A. Aug 05 '14 at 19:31