# Thread: Weighted Average Of Filtered Data Thanks: 0 Likes: 0

1. ## Weighted Average Of Filtered Data

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

2. ## Re: Weighted Average Of Filtered Data

Hi,

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

Thanks!

4. ## Re: Weighted Average Of Filtered Data

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•