Weighted Average

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MatthewFlinchem - 13 Jun 2006 12:24 GMT

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

Reply to this Message

Bernard Liengme - 13 Jun 2006 12:53 GMT

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

Signature

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

> I am trying to weight out the average number of peices packed together
> per purchase order to end up with an average of pieces packed together
[quoted text clipped - 9 lines]
>
> Thanks!

Reply to this Message