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
| function   | excel   | delete-row   | average   | libreoffice-calc   2017-04-16 23:04 1 Answers

Answers ( 1 )

  1. 2017-04-16 23:04

    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)

◀ Go back