excelexcel-formulaexcel-2021

Is it possible to construct an array in excel using existing cell values

It is possible to construct an array in excel as follows: ={1,2;3,4}

Using this method I want to calculate the inverse of a 5 x 5 matrix of a series of elements in one row rather than distributed as in a true 5 x 5 matrix over 5 rows and 5 columns.

Is it possible to reference the cells in their current location to construct a 5 x 5 matrix within the MINVERSE function such as =MINVERSE(A1:A5;A6:A10...A21:25)?

Solution

  • Here is one alternative for Excel 2021 Users:

    enter image description here

    =MINVERSE(INDEX(A:A,TRANSPOSE(SEQUENCE(COUNTA(A:A)/5, 5))))

    Or Bit shorter:

    =MINVERSE(INDEX(A:A,SEQUENCE(,5,0,5)+SEQUENCE(5)))