Assign zero to diagonal cells without changing the matrix in Excel

Question

I want to implement LARGE() function without changing the matrix while assigning zero to diagonal elements which can be achieved through =IF(ROW()=COLUMN(),0,"") if the change of base matrix is allowed.

Answer 1

Use the MAP function to create a new array from your array. Use your IF logic in the LAMBDA formula. This creates an array where all the diagonals are zeros:

=MAP(A1:F6,LAMBDA(a,IF(ROW(a)=COLUMN(a),0,a)))

Put that inside your LARGE formula, so this would take the 10th largest value from the new array:

=LARGE(MAP(A8:F13,LAMBDA(a,IF(ROW(a)=COLUMN(a),0,a))),10)