Ask Your Question

average of sums [closed]

asked 2016-11-28 16:54:11 +0200

DiesNuts gravatar image

Let's say I have some data on columns B through E that I want to average as follows:

  • for each row: sum the four columns
  • then take the average such sum

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-03-05 00:24:05.564671

1 Answer

Sort by » oldest newest most voted

answered 2016-11-28 23:08:44 +0200

Regina gravatar image

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.

edit flag offensive delete link more


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?

DiesNuts gravatar imageDiesNuts ( 2016-11-29 12:18:27 +0200 )edit

=sum(B32:J32) The formula as you would write it in M32.

Regina gravatar imageRegina ( 2016-11-29 17:58:04 +0200 )edit

not quite, because we need to omit H,I, but I get the idea. thank you.

DiesNuts gravatar imageDiesNuts ( 2016-11-30 10:57:28 +0200 )edit

Question Tools

1 follower


Asked: 2016-11-28 16:54:11 +0200

Seen: 2,235 times

Last updated: Nov 28 '16