How to Dynamically Average the Last Two Months Using TODAY in Excel
Question details
The user needs to automatically calculate the average of data from the last two months based on the current date, avoiding manual updates to horizontal formula ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating averages for horizontally structured data where the relevant date columns shift forward every month.
- Observed behavior
- The AVERAGEIF formula requires the user to manually change column letters each month. The user wants the range to update automatically as the TODAY() function changes.
Before building dynamic formulas, ensure your workbook's Calculation Options are set to 'Automatic' so that the TODAY() function recalculates daily.
Use INDIRECT with AVERAGEIF to Create Dynamic Ranges
Construct dynamic cell ranges based on column letters calculated for the last two months, allowing your formula to adapt automatically.
By storing the start and end column letters for your two-month timeframe in specific cells, you can use the INDIRECT function to stitch together a functional range for AVERAGEIF. This avoids the need to manually alter your primary formula.
In a cell (e.g., H31), calculate or manually enter the column letter for the start of your two-month period. In another cell (e.g., F31), place the column letter for the end date.
Select the cell where you want the average to appear. Enter the following formula: =AVERAGEIF(INDIRECT($H$31&"34"):INDIRECT($F$31&"34"),A12,INDIRECT($H$31&"40"):INDIRECT($F$31&"40"))
Press Enter. The INDIRECT function will read the text strings (combining your column letters and row numbers 34 and 40) and convert them into actual cell ranges that AVERAGEIF can evaluate.
Restructure Data Vertically in an Excel Table
Transposing horizontal data into vertical columns inside an Excel Table simplifies dynamic date calculations significantly and represents a best practice for data modeling.
Use WPS Spreadsheet for Advanced Dynamic Calculations
WPS Spreadsheet fully supports INDIRECT, AVERAGEIF, and TODAY functions, allowing you to build complex dynamic formulas just like in Microsoft Excel. It handles large datasets efficiently while remaining completely free to use.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the file containing your horizontal data.
- 2. Select your target cell: Click on the specific cell where you want to output the dynamically calculated two-month average.
- 3. Input the dynamic formula: Type your AVERAGEIF and INDIRECT combination formula exactly as you would in standard spreadsheet software.
- 4. Calculate and save: Press Enter to execute the calculation. Save your workbook in standard .xlsx format to retain full formula compatibility.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
A #REF! error usually occurs if the text string generated inside the INDIRECT function does not form a valid cell reference, or if the referenced cells/worksheets have been deleted. Double-check your column letters and row string concatenations.
Does the TODAY function update automatically?
Yes, the TODAY() function is volatile and updates automatically to the current system date whenever the workbook is opened or when any recalculation is triggered within the file.
Can I use AVERAGEIFS instead of AVERAGEIF for multiple criteria?
Yes. If you need to evaluate multiple conditions—such as a specific category alongside the dynamic date range—AVERAGEIFS is perfectly suited for this. Just remember that the syntax order is slightly different: AVERAGEIFS requires the 'Average_range' to be the first argument.
How can I dynamically find the column letter based on a date?
You can use a combination of the ADDRESS and MATCH functions to find the column number containing a specific date, and then extract the letter. Alternatively, restructuring your data vertically eliminates the need to extract column letters entirely.
