Average every n number of rows in a spreadsheet

I have a long column of data that I want to make into a chart. The problem is that the column has over 50000 rows and processing it gets problematic. I looked for a way to average every Nth number of rows, in order to speed up things.

I found this really useful post for Excel at ExtendOffice. I am using Gnumeric, but things seem to work just fine. The formula is as follows:

=average(offset($A$2,(row()-row($B$2))*5,,5,))

There, A2 is the first cell from where you start the averaging procedure and B2 is the cell where you put the formula. Number 5 indicates that every 5 rows will be made into average.

In my case, I have a list of p-values, which I have already sorted by size (small to big). Here’s a short excerpt from my data, processed by it:



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s