Scalable identity matrix

[mill]

Hi -

I have two ranges in Excel that contain vectors of numbers. While the two ranges/ vectors are the same length, there are multiple sets of such vectors of varying lengths. I'd like to develop a formula where I can use an identity matrix as part of the computation with dimensions equal to the length of the input vectors.

For instance, suppose I have a vector of numbers in Excel range A1:A4 and another in Excel range B1:B4. Currently, I have to manually enter the identity matrix, as in the following CSE formula:
{=A1:A4/TRANSPOSE(B1:B4)*{1,0,0,0;0,1,0,0;0,0,1,0;0,0,0,1}}

Please let me know if anyone has a solution. I am limited in that I cannot use VBA in the current operating environment. I would also like to avoid having to alter the spreadsheet to include metadata (i.e. creating an identity matrix in a new range and using the OFFSET() function) as well as using the MINVERSE() function as not all matrices are invertible and there appear to be some precision issues in Excel with its use (i.e. using `A1:A4*MINVERSE(A1:A4)` to create the identity matrix).

Thanks!

 

[MrExcel]

Here is a really late answer, but Excel 2013 now introduces the MUNIT(4) function to generate the 4x4 identity matrix.

{=A1:A4/TRANSPOSE(B1:B4)*MUNIT(4)} will work provided you never have to open the workbook in Excel 2010 or earlier...

 

[Domenic]

I'm not sure I fully understand, but maybe...?

=(A1:A4/TRANSPOSE(B1:B4))*((ROW(A1:A4)-ROW(A1))=(TRANSPOSE(ROW(B1:B4)-ROW(B1))))

 

[jmclav]

Really late answer but may be still useful for somebody. (I tested this in Excel 2010.)
Define a name for the identity matrix dimension, say "n". Give it a value, say 5.
Define a name for the identity matrix itself, say "I". Its value should be the formula:
=1*(ROW(OFFSET(Sheet1!$A$1,0,0,n,n))=COLUMN(OFFSET(Sheet1!$A$1,0,0,n,n)))

That's it! You can check it for instance by giving values to a 5x5 range array, say in cells A1 to E5; then selecting a different 5x5 range, say F1 to J5, typing "=A1:E5 - I" and CSEntering. The range F1 to J5 should appear populated wiht the proper values.

P.S:: I translated the function names from the spanish Excel version. Hopefully I did it right.