How to Create Rolling Average Dynamic Array Formulas in Excel
Question details
Generate a rolling average using dynamic array spill formulas without needing to manually copy and paste the formula across multiple rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a moving 12-month rolling average for a dataset, where a single formula automatically populates the required row sequence.
- Observed behavior
- The user requires an automated method to output a sequence of moving averages into an empty spill range using modern dynamic array functions.
Verify that your version of the spreadsheet application supports dynamic array functions (like MAP, LAMBDA, and SEQUENCE) and ensure the cells directly below your formula destination are completely empty to accommodate the spill area.
Use MAP, SEQUENCE, and INDEX to Calculate Rolling Averages
Combine Excel's dynamic array functions to create a seamless 12-period rolling average that spills into adjacent rows automatically.
Dynamic arrays eliminate the need for dragging formulas manually. By using the MAP function alongside SEQUENCE and LAMBDA, we can instruct the spreadsheet to calculate a moving average for each row dynamically.
This formula uses the INDEX function to define the start and end of the moving range based on the current row's position. If the row number is less than the desired rolling period, it returns a blank value.
Ensure your dataset is continuously organized in a single column. For easier formula management, name this data range 'Values' through the Name Box or Name Manager.
Select the top cell of your intended output column and input the following formula: =MAP(SEQUENCE(ROWS(Values)),LAMBDA(n,IF(n<12,"",AVERAGE(INDEX(Values,n-11):INDEX(Values,n)))))
To change the formula for a different timeframe (e.g., a 6-month average), change '12' to '6' in the IF statement, and change 'n-11' to 'n-5' in the INDEX function.
Press Enter to execute the formula. The results will automatically spill downward for every row that corresponds to your source data.
Master Advanced Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like MAP, SEQUENCE, and LAMBDA. It allows you to effortlessly manage complex datasets and rolling average formulas within a lightweight, highly compatible environment.
- 1. Install WPS Office: Download and install WPS Office, then launch the WPS Spreadsheet application.
- 2. Open Your Data: Open your existing workbook containing the dataset for your moving average.
- 3. Apply Dynamic Arrays: Select an empty cell and paste your MAP and SEQUENCE formula.
- 4. Generate Results: Press Enter to instantly generate the rolling average spill sequence.
Frequently Asked Questions
Why am I getting a #NAME? error when using this formula?
A #NAME? error typically occurs if your spreadsheet software does not support modern dynamic array functions like MAP, LAMBDA, or SEQUENCE. You need to use an updated version of Excel or WPS Office Spreadsheet that fully supports these new functions.
Can I apply this rolling average to horizontal columns instead of vertical rows?
Yes. To calculate horizontally, swap the ROWS function with COLUMNS in your SEQUENCE definition, and ensure your INDEX logic targets the column position rather than the row position.
What happens if there are empty cells in my source data?
If there are empty cells in your 'Values' range, the AVERAGE function will treat those blank cells as zeroes or ignore them depending on the spreadsheet's settings, which can skew the rolling average. Ensure your dataset is continuous or use an IF statement to handle empty source cells.
How do I fix a #SPILL! error?
A #SPILL! error happens when the dynamic array tries to output results into cells that already contain data, spaces, or hidden characters. Deleting the contents of the obstructing cells will instantly resolve the error and allow the formula to populate.
