Sumproduct Lookup with multiple critera?

[RayportingMonkey]

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))

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})*(D28))

But the result was waaaay off.

What am I doing wrong?

Thanks in advance!

Later-
Ray

 

[smartin]

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))

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})*(D28))

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),D28,F2:F8)/SUMPRODUCT((B2:B8=1)+(B2:B8=5),D28)

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.

 

[Ashish Mathur]

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]

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})*(D28))*(B2:B8={1,5})*(F2:F8))/SUMPRODUCT((B2:B8={1,5})*(D28))

Thanks for the second set of eyes and alternate syntax!

Later-
Ray

 

[smartin]

You're welcome!

Note the highlighted portion is redundant. Since you are multiplying
through, it adds no new logic: