I have a column that may contain X number of values. I am looking to get the average of the top Y values, based on the number of X values that are above 0. There are just two rules on how to come to this value.
- Y must represent 49% or less of the values greater than 0. If I have 11 total values that are above 0, then I can only take the top 5.39 values that I can take an average on.
- Y must be a whole number and rounded down. To take the last piece of criteria further, 5.39 would round down to 5, so I am essentially getting the average of the top 5 values.
I feel like I am getting close with different solutions but have struggled. For instance, the solution at Getting average of top 30% of the values in one column is close, however, it appears to find the values above the 0.7 number, and not necessary the top 30%.
Here is my data,
92.593
88.889
45.679
88.889
0
88.889
87.654
0
69.136
41.975
49.383
0
40.741
50.617
0
Here I have 11 values that are above 0. I know that using the criteria I mentioned above, I can only average the top 5 values. Those top 5 values average out to be 89.383.
I have tried something like this,
=AVERAGE(LARGE(A1:A14), ROW($B$1:$B2)))
Where B1 just has the value 1, and B2 has a formula that calculates the number of values I can have, with no such luck.
CodePudding user response：
I don't know your XL version, but this also works with older versions =AVERAGE(LARGE(IF(A1:A15>0,A1:A15),ROW(INDIRECT(B1&":"&B2))))
remember to confirm as array formula using CTRL SHIFT ENTER
I hope that's what you are looking for, bye.