Excel Dynamic Eight-Row Average in a Calculated Table Column
Question details
The user needs to calculate the average of the latest eight rows in an Excel table calculated column using a range that expands as the formula fills down.
- Product
- Microsoft Excel table and calculated column formulas
- Device & OS
- not provided
- Scenario
- A calculated column must average the newest eight values from another table column without relying on fixed cell references.
- Observed behavior
- Fixed references can shift or fail to maintain the intended rolling eight-row range when the calculated column fills down.
Confirm the source values are inside an Excel table and note the table column name, such as N. Also identify the first data-row reference used by the calculated column, such as $O$2, before entering the formula.
Use INDEX and structured references for a rolling eight-row average
Use this method in a table calculated column when the average should update for every row and remain aligned as the table expands.
Structured references keep the formula tied to the table column, while INDEX identifies the current eight-row window. The IF condition leaves the result blank until at least eight preceding rows and the current row are available.
In Excel, click the first data cell in the table column where the average should appear. This column should be configured as a calculated column so the formula can fill down automatically.
Type =IF(ROW()-ROW($O$2)>=9,AVERAGE(INDEX([N],ROW()-8):INDEX([N],ROW())),"") and press Enter. Replace [N] with the actual source table column name and replace $O$2 with the first data cell of the calculated column.
If Excel does not fill the table column automatically, select the formula cell and use the fill handle or choose Table Design > Fill Down to copy the formula through the calculated column.
Check a row with at least eight available values. The result should average the current row and the seven rows immediately above it, while earlier rows should remain blank.
Build a rolling eight-row average in WPS Spreadsheets
WPS Spreadsheets can create a table calculated column with structured references and INDEX. Enter the rolling formula, fill it through the table, and keep the calculation aligned as new rows are added.
- 1. Open the workbook: Launch WPS Office, open the workbook in WPS Spreadsheets, and confirm that the source data is formatted as a table.
- 2. Select the calculated column: Click the first data cell in the output column and verify the source table column name, such as N, and the first output-cell reference, such as $O$2.
- 3. Enter the formula: Enter =IF(ROW()-ROW($O$2)>=9,AVERAGE(INDEX([N],ROW()-8):INDEX([N],ROW())),"") in the calculated column, replacing the example references with your table's actual names.
- 4. Fill and test the formula: Press Enter, fill the formula down the table if necessary, and confirm that the first results appear only after eight source values are available.

Frequently Asked Questions
Why does the formula return blank values at the top of the table?
The IF condition intentionally returns an empty string until the current row has enough preceding data for an eight-row average. This prevents incomplete averages from appearing.
Which part of the formula should be changed for another source column?
Replace [N] with the actual structured table column name that contains the values to average. Keep the column name inside the structured reference format, such as [Sales].
What does $O$2 represent in the formula?
$O$2 is the first data cell in the calculated output column. Replace it with the first output-cell reference in your own table so ROW()-ROW($O$2) correctly measures the current row position.
Can the formula calculate a different number of latest rows?
Yes. For a rolling window of a different size, adjust the ROW()-8 part to the desired starting offset and update the IF threshold so the formula waits until the required number of rows is available.
Will the formula update when new rows are added to the table?
Yes. When the formula is stored as a table calculated column, Excel or WPS Spreadsheets can extend it to new rows, and the structured reference continues to use the source table column.




