2

I have an empty matrix:

enter image description here

I would like to fill the diagonal of a matrix with zeros:

enter image description here

Is there any clever way to do it?

The matrix can be pretty large and it's tedious to do this manually. I could use some AutoHotkey scripts but I wonder whether there is some better way. I am aware of the question How can I dynamically format the diagonal cells in Google Spreadsheet? but I want out whether filling the diagonal with zeros can be done without scripting.

Improve this question
2
  • 1
    You could compare if (B$1 = $A2). If yes, then 0 else "". And then paste to all, after that copy and paste values only. – Aprian Mar 28 '14 at 1:51
  • Assuming that you always have identical sets of row and column labels. – MaryC.fromNZ Mar 28 '14 at 11:10
3

This formula in essence will do that.

Formula

=ARRAYFORMULA(IF(COLUMN(B1:S1)=ROW(A2:A19), 0, ""))

Explained

The IF statement, in combination with the ARRAYFORMULA, validates whether the column indices match the row indices. When they do, show a zero and in all other cases, just show nothing.

Note

Now that you have the solution you want, you're not able to make use of it by adding something in that range. If you do, it will destroy the ARRAYFORMULA. Making a print-out seems like the only benefit.

Improve this answer
1
  • 1
    Nice solution. And once the 0's are in, you can always copy/paste values so you can manipulate the rest of the range. I agree with your note, if you have other data in the range before you set the diagonal 0's, you're boned. – OnenOnlyWalter Aug 5 '14 at 0:31
0

The proposed formula seems to be working only if the top-left corner of the matrix is in A1. It would be nice to have something that also takes an already existing matrix and sets only the diagonal to 0, leaving all other elements unaltered.

So I put together this improvement:

=arrayformula(if((row(B14:L24)-row(B14)) = (COLUMN(B14:L24)-COLUMN(B14)),0, B14:L24))

which preserves the original matrix in B14:L24, and sets only the diagonal to 0.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.