-1

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?

Community
  • 1
  • 1
Ruben
  • 3
  • 4
  • Are you finalizing that with [Ctrl]+[Shift]+[Enter] rather than simply [Enter]? It is an **array formula**. Maybe this **standard** formula would be better `=AVERAGEIF(B:B, ">"&PERCENTILE(B:B,0.9))`. –  Dec 16 '15 at 11:09
  • Yes I'm finalizing with Ctrl+Shift+Enter. How can I use your formula and only find the value for only one part-type without using an array? – Ruben Dec 16 '15 at 11:23
  • Well, `=AVERAGEIFS(B:B, A:A, "book", B:B, ">"&PERCENTILE(B:B,0.9))` would be the easy way but that uses the percentile from the entire column of B not just the percentile from the book values of B. How many rows are you talking about and is column A sorted so that the parts are always together? –  Dec 16 '15 at 11:30
  • Right now it's about 2500 rows with 30-ish different "parts". Unfortunately they are not sorted. – Ruben Dec 16 '15 at 11:37
  • If they are not sorted then you need an array formula that references the full extent of the data; not as bad as full column references but not ideal. If they were sorted then you can pick up the row numbers of the first 'book' and the last 'book' and your standard formula calculations are minimized. –  Dec 16 '15 at 11:45
  • Yep, that is why I was looking for a solution using VBA. Someone out there can probably make an UDF with arguments like:`=MYFUNCTION(A:B,"book",10)` – Ruben Dec 16 '15 at 11:52
  • For 2500 rows, you don't need a UDF but you do need an array formula that dynamically updates the full extent of the data without referencing so much as a single row beyond that. –  Dec 16 '15 at 12:19

1 Answers1

1

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.