## LibreOffice Calc: periodically average cells and delete redundant rows

Question

How can one periodically average cells in the 2nd column: `(B * n : B * n + 5)`, where n = 1, 2, 3...

Because I recently moved to a 5 minute timestamp, I would also like to delete 4 in every 5 rows. Ideally the newly calculated averages would substitute the former values.

``````25-3-2017 21:10:16  5.61        7.352     // (B1:B5)
25-3-2017 21:11:16  6.88
25-3-2017 21:12:16  7.91
25-3-2017 21:13:16  8.09
25-3-2017 21:14:16  8.27
25-3-2017 21:15:16  7.36        7.7       // (B6:B10)
25-3-2017 21:16:16  7.6
25-3-2017 21:17:16  8.27
25-3-2017 21:18:16  7.91
25-3-2017 21:19:16  7.36
``````

Because lots of Excel and LibreOffice problems have very similar solutions, I have added Excel to the tags.

Show source

## Answers to LibreOffice Calc: periodically average cells and delete redundant rows ( 1 )

1. In Excel you can use the following (Try to do it in LibreOffice)
In C1 write the formula:
`=AVERAGE(OFFSET(\$B1,0,0,5))`
In C2:
`=IF(MOD(ROW()-1,5)=0,AVERAGE(OFFSET(\$B2,0,0,5)),"")`
Offset will include B1:B5, B6:B10, B11:B15 each time you drag the formula
`MOD(ROW()-1,5)=0` will test the row number
If the remain of the division by 5 of Row()-1 =0 the formula will calculate the average
Drag the formula down
When finish copy the results, paste special values in another column
and after you can delete the rows with empty average
You can also sort by the average column and delete the empty (but first copy paste special values)