SMartin,

Thanks! That was helpful. And yes, I agree; I find the syntax I had
initially posted easier to read and modify - especially when I have upwards
of 8-10 codes to use in one formula!

The formula I wound up using is as follows:

=SUMPRODUCT(((B2:B8={1,5})*(D2:D8))*(B2:B8={1,5})* (F2:F8))/SUMPRODUCT((B2:B8={1,5})*(D2:D8))

Thanks for the second set of eyes and alternate syntax!

Later-
Ray

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


"smartin" wrote:

RayportingMonkey wrote:
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-

Hi Ray,

In your second attempt you forgot to weight duration in the numerator.
Other than that it is sound.

This also works, but I like your idea better:

=SUMPRODUCT((B2:B8=1)+(B2:B8=5),D2:D8,F2:F8)/SUMPRODUCT((B2:B8=1)+(B2:B8=5),D2:D8)

The + signs amount to logical ORs.

BTW you can't use full column references with SUMPRODUCT, which is why
your first attempt does not work.