Share via

How to use formulas to average a person's productivity on weighted data

Anonymous
2010-12-11T00:14:03+00:00

Column 'A' is the number of units completed...'B' is the productivity rate at which the employee completed those units & 'C' is the percent of Units completed in that department out of the total units. So I want to determine what the true average of their productivity is by using the % of units as a way to weight their productivity percentage. Hope that makes sense

A B C D
Department Units Productivity % of work
RPL Paper 1300 120.00% 52.00%
THD Comm Paper 1200 98.00% 48.00%
Total Units 2500
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2010-12-11T00:43:48+00:00

    =sumproduct(productivity,units)/sum(units)

    which means

    =sumproduct(b2:b99,a2:a99)/sum(a2:a99)

    Make sure that the sum of total units is not included in a2:a... I would normally put the total into a header row to be flexible against additions.

    Regards,

    Bernd

    http://www.sulprobil.com/html/excel_forums.html

    I am not sure what you mean by true average, but here is how I think of this.

    Productivity is measured in units per time unit.

    Department A has produced UA units in TA time units with a productivity of PA=UA/TA.

    Department B had produced UB units in TB time units with a productivity of PB=UB/TB.

    Together department A and B has produced UA+UB units in TA+TB time units with an average productivity of P=(UA+UB)/(TA+TB).

    P = (UA+UB)/(UA/PA + UB/PB)

    With the number according to your layout the following formula will calculate P:

    =(A2+A3)/(A2/B2+A3/B3)

    and with the numbers in your example P will be 108.3271923%

    Hope this helps / Lars-Åke

    Was this answer helpful?

    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-12-11T02:02:11+00:00

    Kmill74 wrote:

    Thank you this worked

    What worked? Whose formula did you choose:  Bernd's or Lars-Ake?

    From the GUI indentation, it appears that you are responding "this worked" to Bernd.

    Certainly it "works" insofar as it produces a number.  But I agree with Lars-Ake:  Bernd's formula is not the correct average productivitly.

    Dovetailing Lars-Ake's explanation and Bernd's compact form, I believe the correct formula is:

    =SUM(A2:A3)/SUMPRODUCT(A2:A3,1/B2:B3)

    although SUM(A2:A3) can be replaced with A4, which is the sum.

    I think an analogy will make this clear to Bernd, at least, if not to you.

    Suppose I run 2 miles at 6 MPH and 2 miles at 4 MPH.  What's my average speed?

    In terms of Kmill's problem, miles covered is the "units produced", and MPH is the "productivity".  In each case, I covered 50% ("percent of work") of the total "units produced", 4 miles.

    Bernd's formula would have us compute SUMPRODUCT({6,4},{2,2})/SUM({2,2}), which is the same as simply SUMPRODUCT({6,4},{0.5,0.5}), resulting in 5 MPH.

    And you might even think that makes good sense.  I know I do ;-).

    But the fact is, the average speed ("productivity") is total distance (4 miles) divided by total time.  We all agree on that, right?

    So what's the total time?

    I covered 2 miles at 6 MPH in 20 minutes; and I covered 2 miles at 4 MPH in 30 minutes.  So total time is 50 minutes.

    Ergo, the correct average speed in MPH is 4 / (50/60), which is 4.8 MPH.

    I think this is exactly the derivation that Lars-Ake explained.

    In summary, we cannot take weighted averages of ratios ("productivity").

    PS:  If Kmill's "productivity" is "rate at which the employee completed those units", it is the units divided by time, as Lars-Ake asserted.  I find it odd to express that as a percentage instead of units per time (like MPH).  It is true that any number can be expressed as a percentage.  Log of 10 to base 2 -- LOG(10,2) -- is about 3.3219.  Sure, I could write that as 332.19%.  But I don't think that's meaningful.

    EDIT....  More to the point, "no one" would refer to 6 MPH as 600%.  (Famous last words).

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-12-11T01:19:36+00:00

    Thank you for your help

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-12-11T01:19:22+00:00

    Thank you this worked

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-12-11T00:27:31+00:00

    =sumproduct(productivity,units)/sum(units)

    which means

    =sumproduct(b2:b99,a2:a99)/sum(a2:a99)

    Make sure that the sum of total units is not included in a2:a... I would normally put the total into a header row to be flexible against additions.

    Regards,

    Bernd

    http://www.sulprobil.com/html/excel_forums.html

    Was this answer helpful?

    0 comments