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
Best Answer
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.