Im trying to calculate an array formula based on the top values, Say the top 20% and find the cut off
At the moment my formula is similar to {=max(row(1:7)*--(sum(large(indirect(range),row(1:7))))
to find the maximum value that doesnt fit the formula, ie (1,2,3,0,0,0) then the max picks up the 3
Can someone give me some pointers on where im going wrong ?
Thanks
With this data in A1:A10
10
8
5
6
4
2
3
1
9
7
Sum everything in the top 20% (19)
=SUM((A1:A10>PERCENTILE(A1:A10,0.8))*(A1:A10))
Find the largest number in bottom 80% (8)
=MAX((A1:A10<=PERCENTILE(A1:A10,0.8))*(A1:A10))
Find the smallest number in the top 20% (9)
=MIN(IF(A1:A10>PERCENTILE(A1:A10,0.8),(A1:A10),""))
Perhaps you are wanting for sum(large(indirect(range),row(1:7))) to return an array of cumulative values starting from largest to smallest? You could try instead in your array formula:
MMULT(--(ROW(1:7)>=TRANSPOSE(ROW(1:7))),LARGE(A1:A7,ROW(1:7)))
and test if this sum is greater than 20% of the total.
