question related to Large Function

If you post a sample workbook it will be much easier to help you.

Hey dude. Does the attached help you? If not canyou clarify?

Count top 80.xlsx

cant see how counting top 80 would work if the cells add up to 100% then you could have 100 cells with 0.1 in or say 4 cells with 0.25 in
what do you mean by 100%
do you mean you have a range of cells that = say 160 so that is 100% of 160?
to get same results as above a simpler non array
=COUNTIF(A2:A20,">="&PERCENTILE(A2:A20,0.2))
=SUMIF(A2:A20,">"&PERCENTILE(A2:A20,0.2))

With an Pivot Table.

I used the file of TheCman81 for it.

See the attached file.

With an pivot table.

See the attached file.

for your example
in g5 filled down to g7
this array entered formula
=MATCH(1,IF(SUBTOTAL(9,OFFSET(INDEX($C:$C,MATCH(F5,$A:$A,0)),0,0,ROW(INDIRECT("1:"&COUNTIF($A:$A,F5)))))>=80,1),0)

Please see attached.

Also forgot to mention these are array formulas and need to be entered with Ctrl Shift Enter

Large Function v2.xlsx

hm i think i am not reading this correctly

so from this you want the largest x that ad up to >=80?
12.185
4.477
3.07
7.505
4.604
2.128
11.145
11.134
3.017
3.929
5.629
5.869
0.54
5.614
18.763
0.39
which would be
18.763
12.185
11.145
11.134
7.505
5.869
5.629
5.614
4.604

Hi,

So what's your expected result for the attached? 14?

Regards

Can you give the expected results for Division 1 2 & 3? That way we can determain what formula to use

Hello Keelin,

in your notes you say this:

I was hoping the large function could get around having to sort the data and do a cumulative calculation, as in the end the count would be the same

If you are saying that the count is the same however the data is sorted then I don't think I agree. If you were to sort in descending order of amounts for each division, for example, as Martin shows, then you only need the 9 largest items to get over 80%.

The way you are doing it in the attached is dependent on the order, are you sure that's the way you want to do it?

Hi All

Yes the results are indeed dependant on the data being sorted (So for the helper columns to work it has to be properly sorted - I had a major heart failure moment there thinking that the data that I used wasn't sorted properly.....but Pheeewwwwwww it is!) and stupid me forgot to sort the data in the sample spreadsheet I provided (thank-you very much Daddylonglegs for bringing that to my attention)

With the way the data is now structured its not easy to sort it at all which is why I would love to be able to enlist a formula to do this.

Have attached the spreadsheet with the data properly sorted, now the results show:

Division 1 8
Division 2 17
Division 3 24

which is (now) correct as per sorted data,

(oh and its <= 80%)

Thanks so much all,

Hi,

Try this array formula** in G4:

=MATCH(TRUE,MMULT(0+(ROW(INDIRECT("1:"&COUNTIF($A$4:$A$140,F4)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTIF($A$4:$A$140,F4))))),LARGE(IF($A$4:$A$140=F4,$C$4:$C$140),ROW(INDIRECT("1:"&COUNTIF($A$4:$A$140,F4)))))>80,0)-1

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

This part:

=MATCH(TRUE,MMULT(0+(ROW(INDIRECT("1:"&COUNTIF($A$4:$A$140,F4)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTIF($A$4:$A$140,F4))))),LARGE(IF($A$4:$A$140=F4,$C$4:$C$140),ROW(INDIRECT("1:"&COUNTIF($A$4:$A$140,F4)))))>80,0)-1

creates an array of integers from 1 up to the number of occurrences of the entry in F4 in the range A4:A140, e.g. for Division 1:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

We then have:

=MATCH(TRUE,MMULT(0+({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}>={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}),LARGE(IF($A$4:$A$140=F4,$C$4:$C$140),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}))>80,0)-1

This part:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}>={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}

will resolve to a 16-by-16 matrix consisting of Boolean TRUE/FALSE returns to the greater-than-or-equal-to comparison, i.e. (I'll just give the first three rows' worth and represent TRUE with T and FALSE with F):

{T,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F;T,T,F,F,F,F,F,F,F,F,F,F,F,F,F,F;T,T,T,F,F,F,F,F,F,F,F,F,F,F,F,F;...}

Adding zero to these Booleans converts them to their numerical equivalents (TRUE=1, FALSE=0):

{1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0;1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0;...}

The other matrix within the MMULT is generated from the part in brown:

=MATCH(TRUE,MMULT(0+({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}>={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}),LARGE(IF($A$4:$A$140=F4,$C$4:$C$140),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}))>80,0)-1

i.e.:

LARGE(IF($A$4:$A$140=F4,$C$4:$C$140),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16})

which generates an array, in descending order of size, of the values in column C corresponding to the required Division in column A:

{18.763;12.185;11.145;11.134;7.505;5.869;5.629;5.614;4.604;4.477;3.929;3.07;3.017;2.128;0.54;0.39}

If you know anything about matrix multiplication (which is precisely what MMULT is), then you'll know that the product of the above two matrices will produce precisely the cumulative totals you had when the sheet was ordered, i.e.:

{18.763;30.948;42.093;53.227;60.732;66.601;72.23;77.844;82.448;...etc.}

We now have:

=MATCH(TRUE,{18.763;30.948;42.093;53.227;60.732;66.601;72.23;77.844;82.448;...etc.})>80,0)-1

which I hope is relatively straightforward to understand.

Regards

so after all that you gave us wrong info! you said 80% none of your answers are at 80% they are below

Originally Posted by Keelin

UTTER GENIUS!!

Won't lie its going to take me the best part of tomorrow morning to get my head around it!!

Awesome work

You're welcome!

Maybe get to your local library and look for a book on matrix algebra!

Cheers