+ Reply to Thread
Results 1 to 3 of 3

Formula for average of 5 rows

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    76

    Formula for average of 5 rows

    I have the following problem, I would be grateful for any solutions...

    In column A, I have about 15000 rows worth of values, and what I need is the average of each 5 rows to appear in column B.

    For example;
    in cell B1, I need the average of A1-A5
    in cell B2, average A6-A10
    in cell B3, average of A11-A15
    in cell B4, average of A16-20

    And so on until each all of column A is averaged (in 5 row blocks) into col B.

    I thank you in advance for any assistance provided....

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula for average of 5 rows

    Put this in B1, then copy down as needed:

    =AVERAGE(OFFSET($A$1, (ROW()-1)*5, , 5, ))

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for average of 5 rows

    Here's another one that doesn't use volatile functions.

    =AVERAGE(INDEX(A$1:A$15000,ROWS(B$1:B1)*5-4):INDEX(A$1:A$15000,ROWS(B$1:B1)*5))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1