CODEWITHSUNDEEP
arraysexcelformula
exussum
exussum

Reputation: 18550

Array Formula using Large Excel

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

Upvotes: 1

Views: 1215

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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),""))

Upvotes: 4

lori_m
lori_m

Reputation: 5567

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.

Upvotes: 0

Related Questions