How to Average the Last 10 Excel Cells Containing Data Instead of Days
Question details
Adjust a forecast or average formula to compute based on the last 10 actual data entries rather than the last 10 calendar days.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a rolling average or forecasting when data entry is discontinuous or has stopped entirely.
- Observed behavior
- The existing formula subtracts 10 days from TODAY(), which includes calendar days where no data was entered, leading to an inaccurate average.
Format your dataset as an official Excel Table (select data and press Ctrl+T). This ensures functions like ROWS and INDEX can dynamically reference the column length as you add new records.
Use INDEX and ROWS Functions to Locate the Latest Data
Dynamically find the starting date of the 10th-to-last table record using INDEX and ROWS, allowing AVERAGEIFS to average only the rows containing recent data.
Instead of relying on the TODAY() function minus 10 days, you can count the total rows in the table and step backward by 9. This gives you the precise starting threshold for your 10 most recent entries, regardless of calendar gaps.
Verify the name of your table (e.g., Table5) and the specific columns you need to evaluate, such as the [Date] column and the data column like [OUG(day)].
Select the cell where you want the average. Enter standard en-US formula using commas: =AVERAGEIFS(Table5[OUG(day)], Table5[Date], "<"&TODAY(), Table5[Date], ">="&INDEX(Table5[Date], ROWS(Table5[Date])-9))
If part of a larger forecast calculation, integrate it as needed. For example: =((B4-B6) / AVERAGEIFS(...)) + TODAY().
Press Enter. The ROWS function counts all data entries, and INDEX retrieves the exact date of the 10th record from the bottom.
Easily Manage Dynamic Formulas with WPS Spreadsheet
WPS Office fully supports complex arrays and table-based formulas like INDEX, ROWS, and AVERAGEIFS. Calculate dynamic averages effortlessly in a highly compatible and lightweight environment.
- 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx data file.
- 2. Format Data as a Table: Highlight your data area and press Ctrl+T to convert it into a dynamic table.
- 3. Insert the Dynamic Formula: Paste the INDEX and ROWS combination formula into your target cell to instantly calculate the exact rolling average.
Frequently Asked Questions
Can I use OFFSET instead of INDEX to average the last 10 rows?
Yes, you can use OFFSET with COUNT to determine the last 10 rows. However, OFFSET is a volatile function that recalculates every time any cell in the workbook changes, which can slow down performance. The INDEX function is non-volatile and generally recommended for better spreadsheet efficiency.
What happens if there are blank cells within the last 10 rows?
The INDEX and ROWS method physically counts the rows present in the table framework. If there are blank cells in the values column among those last 10 rows, AVERAGEIFS will skip the blanks, meaning it will average fewer than 10 numerical values. Ensure your table does not contain empty rows.
How do I modify the formula to average a different number of rows, like 5 or 30?
To average a different number of rows, adjust the subtraction value inside the ROWS function. To average the last N cells, subtract (N-1). For example, to average the last 5 rows, change -9 to -4. To average the last 30 rows, change it to -29.
