Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to average every 5 rows or columns in Excel?

In Excel, have you ever tried to average every 5 rows or columns, that is to say, you need to do these operations: =average (A1:A5), =average(A6:A10), =average(A11:A15),…of course, you can apply the Average function to get the average of every 5 cells every time, but, if there are hundreds and thousands cells in your list, this will be tedious. Today, I will talk about some formulas to solve this job quickly and time-saving.

Average every 5 rows or columns with formulas

Average every 5 rows or columns with Kutools for Excel


Insert page breaks every x rows:

Kutools for Excel’s Insert Page Break Every Row can help you to insert the page breaks into current worksheet every x Rows you need quickly and conveniently.

doc-sum-every-n-rows-7

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Average every 5 rows or columns with formulas


The following formula will help you to calculate the average of every 5 rows in the worksheet, please do as follows:

1. Enter this formula into a blank cell: =AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)) (A2 is the start value that you want to average from, and C2 is the cell that you put this formula, the number 5 indicates every 5 rows you want to average), and then press Enter key to get the result, see screenshot:

doc-average-every-5-rows-1

2. Then select this cell and drag the fill handle down over to the cells until the error value displayed. See screenshot:

doc-average-every-5-rows-2

Note: If you want to average every 5 columns in a row, you can apply this formula: =AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5)) (A1 is the start value that you want to average from, and A3 is the cell where you put this formula, the number 5 indicates every 5 columns you want to average), and then drag the fill handle to right side until the error value appeared, see screenshot:

doc-average-every-5-rows-3


Average every 5 rows or columns with Kutools for Excel

Kutools for Excel's Insert Page Breaks Every Row can help you to insert some page breaks for every n rows, and then average every n rows by applying the Paging Subtotals feature.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as this:

1. Click Enterprise > Printing > Insert Page Break Every Row, see screenshot:

doc-average-every-5-rows-4

2. In the Insert Page Break Every Row dialog box, specify the row number which you want to insert page breaks between, and the page breaks are inserted every 5 rows as following screenshot shown:

doc-average-every-5-rows-5

3. And then click Enterprise > Paging Subtotals, see screenshot:

doc-average-every-5-rows-6

4. In the Paging Subtotals dialog box, check the column header that you want to do some calculations, and then choose the function Average as you need, see screenshot:

doc-average-every-5-rows-7

5. And then, the average has been calculated every 5 rows, see screenshot:

doc-average-every-5-rows-8

Download and free trial Kutools for Excel Now !


Demo: Sum / Average every 5 rows or columns with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related articles:

How to average last 5 values of a column as new numbers entering?

How to average top or bottom 3 values in Excel?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    DrSpeed · 1 years ago
    Thanks heaps for this information. Very helpful for working with my data set.
  • To post as a guest, your comment is unpublished.
    George · 1 years ago
    This worked so well, i'm a little skeptical because I am dealing with huge chunks of data and its difficult to verify if i'm getting correct values
  • To post as a guest, your comment is unpublished.
    govind · 3 years ago
    Thanks a lot extend office you saved my lot of time...Will definitely love to contribute one day when i become rich.