Let’s say I have some data on columns B through E that I want to average as follows:
Do I have to get another column for the sum and then take the average of that column or is there a more direct approach? Because pulling =SUM(...) down 500'000 rows does take a while.
You can use a matrix formula. Think the data as column vectors, where you add vectors. If your data is from B1 to E99, then you can use AVERAGE(B1:B99+C1:C99+D1:D99+E1:E99), entered as matrix formula with CTRL+SHIFT+ENTER. But I cannot test, whether LibreOffice can handle 500’000 rows in this way.
“Pulling down” is the wrong way for filling so many rows. Other methods:
(1) In case the target column is adjacent to a data column, you can double-click the drag-fill-square. That will fill the target column as far as there are filled cells on the left side of it.
(2) First mark the target area and then fill it. For marking such large area use the name field, which is left-top of the table grid. Add the range address and then hit ENTER-key. If the formula is already in the clipboard, then you can simple paste it. For a new formula make sure the active cell (with the black border) is the first cell in the target area, then mark the target area via name field, then enter the formula into the input line and finish with ALT+ENTER.
CTRL+SHIFT+ENTER works well (don’t know about 500’000 because I cut off a few, but for 420’000, at least). Thanks a lot. However, I don’t quite get the method “(2)” … let’s say I select range M32:M499866 with the name box, what formula do I enter and confirm with ALT+ENTER to store in each cell the sum of the corresponding row’s B,C,D,E,F,G,J values?
=sum(B32:J32) The formula as you would write it in M32.
not quite, because we need to omit H,I, but I get the idea. thank you.