• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding a suitable line

B

bines53

Active Member
Hello friends !

What do I need to add this function,=AGGREGATE(14,4,MMULT($A$1:$F$4,{1;1;1;1;1;1}),ROWS($H$1:H1)),To get a line corresponds, in four lines.
Column L, requested answers .


Thank you!
 

Attachments

Hi David,

Although I know this will not be accepted by you, but use below formula in K9 and copy down.

=INDEX(MATCH(LARGE(MMULT($A$1:$F$4,TRANSPOSE(COLUMN($A$1:$F$1)^0)),ROW($A$1:$A$4)),MMULT($A$1:$F$4,TRANSPOSE(COLUMN($A$1:$F$1)^0)),0),ROWS(K$9:K9))

Confirm with Ctrl+Shift+Enter. So I did avoid OFFSET, which can be used with SUBTOTAL here.:) ;)

Regards,
 
Here Non CSE Version. In N9 and copy down:

=LOOKUP(99^99,AGGREGATE(14,6,MMULT($A$1:$F$4,{1;1;1;1;1;1}),ROWS(N$9:N9))/(AGGREGATE(14,6,MMULT($A$1:$F$4,{1;1;1;1;1;1}),ROWS(N$9:N9))=MMULT($A$1:$F$4,{1;1;1;1;1;1})),ROW($A$1:$A$4))

Regards,
 
Hi Somendra,

Bringing an idea, with the first solution MATCH(LARGE ,
=MATCH(LARGE(MMULT($A$1:$F$4,{1;1;1;1;1;1}),ROWS($H$1:H1)),MMULT($A$1:$F$4,{1;1;1;1;1;1}),0)

and =MATCH(AGGREGATE(14,4,MMULT($A$1:$F$4,{1;1;1;1;1;1}),ROWS($H$1:H1)),MMULT($A$1:$F$4,{1;1;1;1;1;1}),0)

Thank you!
 
Hi Somendra,

I have, a different formula with AGGREGATE, that in the big data, any formula that post does not fit.

Thank you!
 
I found the solution !

=AGGREGATE(15,6,(ROW($A$1:$F$11))/(MMULT($A$1:$F$11,{1;1;1;1;1;1})=LARGE(MMULT(0+$A$1:$F$11,{1;1;1;1;1;1}),ROWS($H$1:H1))),COUNTIF($J$1:J1,J1))

Regards,

David
 
You must log in or register to reply here.
Back
Top