Hi,

Try this formula instead

=SUMPRODUCT(((E15:E19=E21)+(E15:E19=E22))*(G15:G19 )*(F15:F19))/F21

Cell F21 holds 80. Also, please remember that in versions prior to Office
2007, one cannot give entire column/row references in the SUMPRODUCT()
function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RayportingMonkey" wrote in
message ...
I need to do a weighted average on multiple rows in a table by selecting a
"CODE" number, or in most cases numbers.

A B C D E F
1 x CODE x UNITS x DURATION
2 x 1 x 10 x 145.5
3 x 5 x 20 x 130.25
4 x 3 x 10 x 100.25
5 x 5 x 20 x 50.25
6 x 1 x 10 x 300
7 x 1 x 20 x 150.25
8 x 3 x 20 x 200

I need a formula that chooses records based on CODE (Col.B) and does a
weighted average of DURATION (Col.F) against the UNITS (Col.D).

So, as an example, let's say I need to calc a wavg for codes 1 and 5. The
formula would calc the following;

CODES UNITS DURATION
1 10 145.5
1 10 300
1 20 150.25
5 20 130.25
5 20 50.25
80 138.375

So the result would be 80 UNITS with an average DURATION of 138.375

I thought it would have been something like:
=SUMPRODUCT((B:B={1,5})*(F:F))/SUMPRODUCT((B:B={1,5})*(D:D))

Realizing that the non-numberic values could be screwing me up I tried:
=SUMPRODUCT((B2:B8={1,5})*(F2:F8))/SUMPRODUCT((B2:B8={1,5})*(D2:D8))

But the result was waaaay off.

What am I doing wrong?

Thanks in advance!

Later-
Ray

--
"Trying to make reports so easy... even a monkey could run ''em!"