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.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
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?
Best Office Productivity Tools
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!
