weighted avg question...please help ASAP!

[anantathaker]

Hi guys,

So I have a dataset with columns structured like this:

Category Subcategory # of entries avg. price

Basically, within each category there are 5 or so subcategories. And for each subcategory, I have the number of entries and the average price.

I want to calculate the weighted avg price for a category, so the way I am doing that right now is by doing a weighted avg. Basically, I take each subcategory avg price and multiply by (subcategory # of entries / category # of entries). I then sum up these weighted subcategory avg prices to get a category avg price.

But there are lots of categories, and each has a different number of subcategories. So doing the manually takes a long time, in that I have to continually change ranges, etc. Is there a way to have a more automated formula? Can I use IF statements somehow? Anything would be helpful

 

[dave3009]

Could this work

=SUMIF($B$2:$B$500, "SubCatName", $D$2:$D$500)/COUNTIF($B$2:$B$500, "SubCatName")

HTH


Dave

EDIT: You said you had lots of sub cats so make a table of unique values then reference the cell containing Sub Category name instead of the sub category

 

[anantathaker]

did a modification but figured it out with SUMIF...thanks for tip!!

 

[dave3009]

:wink: