Skip to main content

How to average every 5 rows or columns in Excel?

Author: Xiaoyang Last Modified: 2024-08-23

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


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:

a screenshot of using formula to calculate the average of every 5 rows

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

a screenshot showing all results

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:

a screenshot of using formula to calculate the average of every 5 columns


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.

After downloading and installing Kutools for Excel, please do as this:

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

a screenshot of enabling the Insert Page Break Every Row feature

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:

a screenshot of specifying the interval of rows

3. And then click Kutools Plus > PrintingPaging Subtotals, see screenshot:

a screenshot of enabling the Paging Subtotals feature of Kutools

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:

a screenshot of choosing Average as the paging subtotals

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

a screenshot showing the average of every 5 rows

Download and free trial Kutools for Excel 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?


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!