I am making a spreadsheet that essentially looks like this, where everything below the zeroes is manually inputted and everything above it is its opposite value.
I could simply transpose each column and then manually change each value, or go over every cell above the “diagonal” with “=-B2”, “=-B3” and so on like the example there, but I’m dealing with a fairly large spreadsheet and that would take too long. Is there a more efficient way to do it or a function I could apply to whole lines?
You can make your work easier if, before you start entering values, you enter a formula of the form =IF(COLUMN()>ROW();INDIRECT(ADDRESS(COLUMN();ROW()));0) in all cells of the future matrix. Now, by replacing 0 (the result of the formula) in any cell below the diagonal with the real value, you will get the corresponding value above the diagonal.
To avoid unintentionally deleting formulas at the top of the matrix, you can use this trick. Uncheck the "Protected" box from all cells in the matrix. Create a cell style in which this option is set (optionally, you can set the background color for these cells so that they are visually different from the input cells). Now apply the conditional format to the matrix, the formula COLUMN()>=ROW() and protect the sheet.
This is how it might look
ProtectMatrix.ods (16.8 KB)
-1* added
=IF(COLUMN()>ROW();-1*INDIRECT(ADDRESS(COLUMN();ROW()));0)Of course you’re right! Weak eyes, did not read correctly “=-B2”, “=-B3”, did not notice the minus sign
The main thing is the idea: the details are secondary.
Perfect, thank you.
I could simply transpose
you name it already,=TRANSPOSE(A1:E10)