+ Reply to Thread
Results 1 to 11 of 11

Getting a vertical array containing the maximum values from each row of a 2D matrix

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Getting a vertical array containing the maximum values from each row of a 2D matrix

    Hi,

    I am looking for a formula to produce a vertical array containing the maximum values from each row in a 2D matrix.

    As an example, consider the following formula in cell A1, producting a 2D matrix:

    Please Login or Register  to view this content.
    1 2 3
    2 4 6
    3 6 9

    The vertical array containing the maximum from each row in the 2D matrix would look something like this:

    3
    6
    9

    The following formula works if it references the matrix formula's spill range with A1#:

    Please Login or Register  to view this content.
    But if the A1# references are replaced with the matrix formula itself, it will not work because of the INDEX(ROW(A1:A3)*COLUMN(A1:C1),1,1) part:

    Please Login or Register  to view this content.
    I would like to have a formula that is "self containing", meaning containing the formula producing the 2D matrix and which doesn't contain volatile functions like OFFSET. Any ideas?

    Best regards,
    Marbleking

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    Hi,

    =LARGE(MyArray,MOD(SMALL(SEQUENCE(ROWS(MyArray))*10^6+MATCH(MyArray,LARGE(MyArray,SEQUENCE(COUNT(MyArray))),0),SEQUENCE(ROWS(MyArray),,,COLUMNS(MyArray))),10^6))

    Replace MyArray with your desired construction, e.g.:

    =ROW($A$1:$A$3)*COLUMN($A$1:$C$1)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    Thanx, XOR LX! Your formula works like a charm! :-)

    Regards,
    Marbleking

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742
    You're welcome!

    Cheers

  5. #5
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    For older version
    Please Login or Register  to view this content.
    And there is restriction for big array or big number in the array
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    Another one

    =MOD(LARGE(A1#+ROW(A1#)*10^6,SORT(SEQUENCE(ROWS(A1#),,,COLUMNS(A1#)),,-1)),10^6)

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742
    @Bo_Ry

    Your formula is referring to the spilled range in A1. But I think the OP was wanting a general solution which could be applied to any array, including those which are generated in-formula, i.e. which are not necessarily references to actual worksheet ranges.
    Last edited by XOR LX; 08-15-2020 at 07:57 AM.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    @XOR LX Thanks.

    Maybe this

    =MOD(LARGE(MyArray+SEQUENCE(ROWS(MyArray))*10^6,SORT(SEQUENCE(ROWS(MyArray),,,COLUMNS(MyArray)),,-1)),10^6)

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    Very good! The system won't let me give you any more rep right now, but take this is a 'verbal' rep!

  10. #10
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    Thanks for all the good input, guys! I had been scouring the internet for any good tips on this problem, but only found C++, Java, Python solutions etc.

    Regards,
    Marbleking

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Getting a vertical array containing the maximum values from each row of a 2D matrix

    @XOR LX Thank you.
    I learn a lot from you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Matching values between array and matrix
    By arupaka in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-04-2019, 01:36 AM
  2. concatenate non-blank values in a matrix array
    By gilliamwibson in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-01-2015, 06:00 PM
  3. Replies: 1
    Last Post: 03-21-2015, 02:47 PM
  4. [SOLVED] Choose maximum value out of a number of values that match certain criterias from an array
    By Alderdeiry Mohamed in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-02-2015, 10:11 AM
  5. Replies: 7
    Last Post: 08-07-2013, 11:57 AM
  6. [SOLVED] find maximum of two values in an array with same lookup value
    By Andy M in forum Excel General
    Replies: 5
    Last Post: 05-13-2005, 01:06 PM
  7. [SOLVED] How to use an array or matrix to return text vs. numeric values
    By Ingrid in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-09-2005, 08:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1