Weighted Average Of Filtered Data

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Weighted Average Of Filtered Data

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

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
  •  

 

 
DMCA.com