## 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 ( 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 numberIf 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)