+ Reply to Thread
Results 1 to 2 of 2

Filtered Weighted Average Formula Explanation Help

  1. #1
    Registered User
    Join Date
    10-22-2020
    Location
    Atlanta, GA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1

    Filtered Weighted Average Formula Explanation Help

    Hi, I found a filtered weighted average formula on a different excel forum that seems to have worked with data. Here's the formula that I have inputted into my excel sheet: =SUMPRODUCT(SUBTOTAL(3,OFFSET(Z12:Z1548,ROW(Z12:Z1548)-ROW(Z12),,1)),Z12:Z1548,U12:U1548)/SUBTOTAL(9,U12:U1548)

    I don't fully understand the formula and am hoping someone could explain it to me. Much appreciated!
    Last edited by dwoolley00; 10-22-2020 at 04:21 PM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Filtered Weighted Average Formula Explanation Help

    we don't know what's in the ranges but in general terms

    SUBTOTAL(3,OFFSET(Z12:Z1548,ROW(Z12:Z1548)-ROW(Z12),,1))

    this is establishing which rows are visible, returning 1 if visible, 0 if not visible c/o COUNTA {SUBTOTAL - 3} calc per row

    the inner OFFSET is used to perform a row by row iteration
    - the ROW(< range >)-ROW(< range first row >) creates an array of integers from 0 to n-1 where n = total no. of rows in range
    - the 4th parameter in the OFFSET (1) stipulates that the range being iterated (first parameter) i.e. Z12:Z1548 to Z13:Z1549, Z14:Z1550 should be resized to one row in height - i.e. Z12, Z13, Z14 etc).
    so, given above, you could equally use OFFSET(Z12,ROW(Z12-Z1548)-ROW(Z12),)

    the resulting "binary" values are used as multiplier, within the SUMPRODUCT, against Z + U (same row) so filtered rows will return 0 and visible rows the product of Z + U

    the above results are then aggregated c/o outer SUMPRODUCT

    the 2nd SUBTOTAL is establishing the denominator - in this case the aggregate of all visible numeric values in Col U within the specified range.
    Last edited by XLent; 10-23-2020 at 08:47 AM. Reason: [height] is 4th parameter -- can't count!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Weighted Averages explanation
    By algl05ab in forum Excel General
    Replies: 1
    Last Post: 10-08-2020, 11:22 AM
  2. Replies: 2
    Last Post: 12-09-2019, 06:19 PM
  3. Replies: 4
    Last Post: 07-19-2019, 04:47 PM
  4. Weighted Average Function for Series Weighted by Increments of 1
    By kratsexcel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-05-2018, 11:38 AM
  5. Replies: 0
    Last Post: 02-15-2018, 03:04 AM
  6. formula for Weighted average?
    By uttam.mothe in forum Excel General
    Replies: 8
    Last Post: 02-16-2015, 08:23 AM
  7. Excel 2007 : Weighted Average Price - FILTERED
    By smudgepost in forum Excel General
    Replies: 4
    Last Post: 06-21-2010, 04:18 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1