Search code examples
excelexcel-formulaexcel-udfvba

Find average of top and bottom n percent of range with extra condition

Example: 

Part, Qty  
Book, 1  
Book, 2  
Book, 3  
Book, 4  
Book, 5  
Book, 6  
Book, 7  
Book, 8  
Book, 9  
Book, 10  
Pen, 4  
Pen, 7

I'm trying to calculate the book qty average of top 10% in the range B:B.
Tried getting it to work with an array formula based on:

{=AVERAGE(IF(B:B>PERCENTILE(B:B,0.9),B:B))}

When I was trying to get it working with an array formula I realized that I had a lot of data, and it takes a lot of time calculating when adding new data.

Can this be done with a user defined function in VBA to speed it up?

Solution

  • To isolate the values in column B that are relevant to 'book' on sorted Part data you can reference the cells in column B as:

    INDEX(B:B, MATCH("book", A:A, 0):INDEX(B:B, MATCH("book", A:A)

    To isolate the values in column B that are relevant to any part on unsorted Part data you can reference the cells in column B as:

    B2:INDEX(B:B, MATCH("zzz", A:A)

    These ranges will dynamically update when new values are added but the first is only accurate once the values in column A have been resorted.

    Standard Formula on Sorted

    In the following sample image the standard formulas in E4:F4 are:

    =PERCENTILE(INDEX(B:B, MATCH(D4,A:A, 0)):INDEX(B:B, MATCH(D4,A:A )), 0.9)
=AVERAGEIF(INDEX(B:B, MATCH(D4,A:A, 0)):INDEX(B:B, MATCH(D4,A:A  )), ">"&PERCENTILE(INDEX(B:B, MATCH(D4,A:A, 0)):INDEX(B:B, MATCH(D4,A:A )), 0.9))
'alternate standard formula for F4
=AVERAGEIF(INDEX(B:B, MATCH(D4,A:A, 0)):INDEX(B:B, MATCH(D4,A:A  )), ">"&E4)

    Fill down as necessary.

        Find average of top and bottom n percent of range with extra condition2
                Standard Formula on Sorted

    Array¹ Formula on Unsorted

    In the following sample image the array formulas in E10:F10 are:

    =PERCENTILE(IF(A$2:INDEX(A:A, MATCH("zzz",A:A ))=D10, B$2:INDEX(B:B, MATCH("zzz",A:A ))), 0.9)
=AVERAGEIFS(B:B,A:A, D10,B:B, ">"&PERCENTILE(IF(A$2:INDEX(A:A, MATCH("zzz",A:A ))=D10, B$2:INDEX(B:B, MATCH("zzz",A:A ))), 0.9))
'alternate standard formula for F10
=AVERAGEIFS(B:B,A:A, D10,B:B, ">"&E10)

        Find average of top and bottom n percent of range with extra condition
                Array Formula on Unsorted

    ¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.