Weighted Average for Different Aggragates of Duplicate Entries?

[Guest]

Hi,

My Excel 2000 spreadsheet looks something like this:

Product Location Customer Units Revenue Weighted Average
Apples New York Customer 1 20 $100
Apples New York Customer 2 41 $180
Apples New York Customer 3 3 $33 xxx
Apples Los Angeles etc... etc... etc.
Apples Los Angeles
Apples Los Angeles xxx
Oranges New York
Oranges New York xxx
Oranges Seattle
Oranges Seattle xxx
Peaches Seattle xxx
Oranges San Fran xxx

Does anyone have a formula that I can copy straight down in the "weighted average column" that will recognize when to calculate the weighted average revenue of a particlar product by in a particular market.

For example, the first instance of "xxx" should show the weighted average revenue of apples in New York. The second instance of "xxx" should show the weighted average revenue of apples in Los Angeles. The third instance of "xxx" should show the weighted average revenue of oranges in New York.

My actual spreadhseet is about 2000 rows and has about 20 different markets and 10 different products.

Thanks so much for your help in advance. I really appreciate all the time and help you excel geniuses have provided.

 

[Bernie Deitrick]

Steve,

Assuming your values are in columns A to E, starting in row 2, in Cell F2,
use the formula (watch the line wrapping):

=IF((A2&B2)<>(A3&B3),SUMPRODUCT(($A$2:$A$2000=A2)*($B$2:$B$2000=B2)*($E$2:$E
$2000)*($D$2:$D$2000))/SUMPRODUCT(($A$2:$A$2000=A2)*($B$2:$B$2000=B2)*($D$2:
$D$2000)),"")

Copy down to match your data set. Change each instance of 2000 to match you
actual row count, or at least be higher than your actual row count.

HTH,
Bernie
MS Excel MVP

Hi,

My Excel 2000 spreadsheet looks something like this:

Product Location Customer Units Revenue Weighted Average
Apples New York Customer 1 20 $100
Apples New York Customer 2 41 $180
Apples New York Customer 3 3 $33 xxx
Apples Los Angeles etc... etc... etc.
Apples Los Angeles
Apples Los Angeles xxx
Oranges New York
Oranges New York xxx
Oranges Seattle
Oranges Seattle xxx
Peaches Seattle xxx
Oranges San Fran xxx

Does anyone have a formula that I can copy straight down in the "weighted

average column" that will recognize when to calculate the weighted average
revenue of a particlar product by in a particular market.

For example, the first instance of "xxx" should show the weighted average

revenue of apples in New York. The second instance of "xxx" should show the
weighted average revenue of apples in Los Angeles. The third instance of
"xxx" should show the weighted average revenue of oranges in New York.

My actual spreadhseet is about 2000 rows and has about 20 different

markets and 10 different products.

Thanks so much for your help in advance. I really appreciate all the time

and help you excel geniuses have provided.