Weighted Average Of Filtered Data

[Coach Hager]

How do I calculate the weighted average of filtered data? I want it to calculate for any filter in column A.

A B C
1 ITEM UNITS UNIT PRICE
2 TV 10 $100
3 RADIO 5 $20
4 RADIO 10 $15
5 TV 20 $120

When I filter by column A I want the weighted average to show in cell C6. Results should be as follows. When column A is filtered by "TV" weighted average in C6 should show $113.33. When the filter in column A is changed to "RADIO" I want cell c6 (weighted average) to show $16.67

 

[Krishnakumar]

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A5,ROW(A2:A5)-ROW(A2),,1)),B2:B5,C2:C5)/SUBTOTAL(9,B2:B5)

adjust the range.

 

[Coach Hager]

Thanks!

 

[amberfreed]

Hello,

Is there anyway to use this formula so that the range wouldn't have to be adjusted when using a filtered list? I would like only visible cells to calculate....

Thank you!!