Weighted Average

[MatthewFlinchem]

I am trying to weight out the average number of peices packed togethe
per purchase order to end up with an average of pieces packed togethe
that is accurate.

PO Units / Units packed Together
12,296 / 13
1,000 / 2
50,000 / 1

As you can see above I have a wide range of numbers. My data is colomn
"f" for pcs and "o" for ordered units. With thousands of rows of data.

Thanks

 

[Bernard Liengme]

With Units in column A and Pieces-together in B, I used
=SUMPRODUCT(B2:B4,A2:A4/B2:B4)/SUMPRODUCT(A2:A4/B2:B4)
to get a value of 1.23 for your data set.

I find it useful to solve a problem using a data set that lets me do the
math in my head.
Try solving:
10 units packed 2 to a box
6 units packed 1 to a box
4 units packed 4 to a box
Now list the contents of each box:
2,2,2,2,2,1,1,1,1,1,1,4 with an average of 1.67
Now try my formula - it seems to work!

NOTE: to expand the problem use something like
=SUMPRODUCT(B2:B1001,A2:A4/B2:B1001)/SUMPRODUCT(A2:A1001/B2:B1001)
but not
=SUMPRODUCT(B:B,A:A/B:B)/SUMPRODUCT(A:A/B:B) as SUMPRODUCT (or any array
function) will not work with references to a complete column.

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MatthewFlinchem"