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!"

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.

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!"

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.

You're welcome!

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

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