Math - Calcualte Weighted Average

Hello Experts,

Math Question -  I need to calculate Weighted Average for the data shown below:

Employee                No of Days Worked                 Coils/Day Produced
Employee1                             13                                                  12.9
Employee2                           13                                                  11.1
Employee3                           13                                                  11.8
Employee4                           10                                                    7.5
Employee5                            10                                                   14.9
Employee6                         10                                                   10.5
Employee7                             9                                                   15.2
Employee8                        9                                                   11.9
Employee9                           9                                                   13.1
Employee10                          9                                                   14
Employee11                         9                                                  14.8
Employee12                          9                                                  14.3
Employee13                        8                                                  16.3
Employee14                    8                                                  15.3
Employee15                       8                                                  15.6
Employee16                      8                                                 12.9

I want to calculate weighted average for coils/day based on employee who worked more number of days...

any idea?

Who is Participating?
barry houdiniConnect With a Mentor Commented:
Sorry, my description was right but formula wrong, should be dividing by sum of days, i.e. column B


see attached

regards, barry
Not sure if i follow your question, your not just wanting 13/12.9 for employee one correct? Are you wanting a weighted average for x amount of employees, or x amount of employees that worked the same amount of days? etc?
barry houdiniCommented:
Assuming days in B2:B17 and Coils/Day in C2:C17 use this version


That will give you the average coils per day with SUMPRODUCT giving the total coils, divided by the sum of column C which is total days

regards, barry
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

mani_saiAuthor Commented:
I want weighted average for all the employee in the list..

weight is based on employee who worked more no of days...
The coils per day is in itself a type of weighted average.
If that is not what you want, please expand on what you want the final statistic to represent.
mani_saiAuthor Commented:
All Courses

From novice to tech pro — start learning today.