+ Reply to Thread
Results 1 to 3 of 3

Formula for average of 5 rows

  1. #1
    Registered User
    Join Date
    NSW, Australia
    MS-Off Ver
    Excel 2010

    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
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365

    Re: Formula for average of 5 rows

    Put this in B1, then copy down as needed:

    =AVERAGE(OFFSET($A$1, (ROW()-1)*5, , 5, ))
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    MS-Off Ver
    2002, 2007:2013

    Re: Formula for average of 5 rows

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

    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)


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