Weighted Average for Different Aggragates of Duplicate Entries?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Steve said:
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.
 
Back
Top