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

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:

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

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:

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 installing Kutools for Excel, please do as this:

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

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:

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

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:

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

Download and free trial Kutools for Excel Now !

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

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?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase... 

---

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!

Read More... Free Download... Purchase...