How to Average Every Six Rows by Date in Excel
Question details
The user needs to calculate an average for consecutive six-row groups of data and place each result next to a matching date for an entire year, avoiding manual formula adjustments.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing high-frequency tracking data (such as measurements taken every 4 hours, equaling 6 rows per day) into a daily average beside a list of calendar dates.
- Observed behavior
- Using the standard drag-down autofill feature only advances the referenced data range by one row at a time, instead of jumping by the required six rows for the next daily average.
Ensure your source data is sorted chronologically and does not contain any unexpected blank rows. Identify the exact starting cell of your data column before applying the formulas.
Use the OFFSET Function to Dynamically Adjust the Range
The OFFSET function is highly effective for dynamically shifting a calculation range by a specified multiple of rows, allowing you to easily drag the formula down for the whole year.
By combining the AVERAGE function with OFFSET and ROW, you can instruct Excel to skip exactly six rows for every one cell you drag the formula down.
Identify the first cell containing the data you want to average. For this example, assume your numerical data starts in cell B2.
Click the cell beside your first date (e.g., D2) and enter the formula: =AVERAGE(OFFSET($B$2, (ROW(A1)-1)*6, 0, 6, 1)).
Press Enter to calculate the first daily average. Select the cell, click the small square at the bottom-right corner, and drag it down the column. The (ROW(A1)-1)*6 component will automatically increment, jumping the reference by 6 rows each time.
Use the INDEX Function for Better Performance on Large Data
The INDEX function provides a non-volatile alternative to OFFSET, which makes your workbook calculate faster if you have tens of thousands of rows.
Calculate Dynamic Averages Easily with WPS Office
WPS Spreadsheet fully supports advanced dynamic formulas like OFFSET and INDEX, allowing you to seamlessly summarize high-frequency datasets into daily averages. Enjoy a familiar interface and robust calculation features without any subscription fees.
- 1. Open Your Data: Launch WPS Spreadsheet and open your existing dataset containing the high-frequency measurements.
- 2. Enter the Formula: Click the target cell next to your date column and input the dynamic OFFSET or INDEX average formula.
- 3. Drag to Autofill: Hit Enter and use the fill handle to drag the formula down to automatically average all six-row intervals.
Frequently Asked Questions
Can I adapt this formula to average every 12 rows instead of 6?
Yes. To average a different number of rows, replace all instances of the number 6 in the OFFSET or INDEX formula with your desired number (e.g., 12). For OFFSET, update the multiplier and the height parameter.
Why does standard dragging not work for creating interval averages?
When you drag a standard AVERAGE formula down by one row, Excel automatically adjusts the relative cell references by exactly one row. To make the reference 'jump' by multiple rows, you must use mathematical adjustments combined with functions like OFFSET, INDEX, or INDIRECT.
What does ROW(A1) do in these formulas?
ROW(A1) acts as a counter that starts at 1. As you drag the formula down to the next row, it becomes ROW(A2) which equals 2, then ROW(A3) which equals 3, and so on. This incrementing number is multiplied by your interval size to calculate exactly how many rows to shift.
