Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Oct 2008
    Location
    USA
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Coach Hager; Oct 28th, 2008 at 12:20 AM.

  2. #2

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

    adjust the range.

  3. #3
    New Member
    Join Date
    Oct 2008
    Location
    USA
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weighted Average Of Filtered Data

    Thanks!

  4. #4
    New Member
    Join Date
    Mar 2008
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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