Formula Errors

Excel Dynamic Eight-Row Average in a Calculated Table Column

Olivia MillerOlivia Miller Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the calculated column

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.

2
Enter the dynamic formula

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.

3
Fill 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.

4
Verify the rolling range

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.

Formula behavior: The formula returns a blank until the table has enough rows, then calculates a rolling average of the latest eight values in the referenced column.

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. 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. 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. 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. 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.
Creates a rolling average from the latest eight table rowsUses structured references instead of fragile fixed cell rangesSupports familiar Excel workbook workflows and .xlsx compatibilityProvides a lightweight spreadsheet interface for formula troubleshooting
microsoft office alternative - wps office

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.