Saturday, November 19, 2011

calculate average of every 5 cells in Excel

I have some data sampled at a rate of 1 sample per minutes. However, to use them as input for the following calculation, I have to convert them to the 5 minutes average. Simply put, I want to let B1=average(A1:A5), B2=average(A6:A10), B3=average(A11:A15), ... til B10=average(A46:A50).

I tried VBA code in Excel but didn't make it work. Then I tried Matlab-the code works but it's troublesome to copy/paste all the data between Excel and Matlab back and forth. Finally I figured out a simple way to do this in Excel, by using the OFFSET, ROW, and INDIRECT functions.

The formula to put in cell B1 is:

=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*5-4),0,0,5,1))


The Row(A1) returns the row number of the cell, which is '1' in this case. So the INDIRECT("A"&ROW(A1)) *5-4, 0,0,5,1) returns the cell A1 to A5. When dragged to cell B2, the INDIRECT("A"&ROW(A2)) *5-4, 0,0,5,1) returns the cell A6 to A10. So I can drag this formula all the way down to B10 to finish this task.

The file looks like this:

at
Labels: , , , , , , , ,

3 comments:

  1. jaylen watkinsNovember 22, 2011 at 9:42 PM

    Good description about Excell use.


    Sample Analysis

    ReplyDelete
  2. AnonymousAugust 12, 2012 at 4:52 AM

    Could somebody please explain how this can be applied to every 10 cells or every 20 cells? I can't follow the logic. Thanks

    ReplyDelete
  3. AnonymousSeptember 13, 2012 at 1:07 AM

    =AVERAGE(OFFSET(INDIRECT("A"&ROW(A3)*10-9);0;0;10;1))

    ReplyDelete

Any comments?

Subscribe to: Post Comments (Atom)

my-alpine and docker-compose.yml

 ``` version: '1' services:     man:       build: .       image: my-alpine:latest   ```  Dockerfile: ``` FROM alpine:latest ENV PYTH...