How to Calculate Average Price for a Selected Date Range in Excel
Question details
Calculate the average unit price for sales occurring between a dynamic start and end date using dropdown cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user needs to dynamically analyze average sales prices across a horizontal dataset by selecting start and end dates from a dropdown list without manually altering the formula.
- Observed behavior
- The user needs a flexible formula combining AVERAGE, INDEX, and MATCH to retrieve the correct column ranges based on the selected dates and return the average.
Ensure your date headers in the data table are formatted correctly as dates, and that your start and end date dropdown cells contain values that exactly match the header row.
Use the AVERAGE, INDEX, and MATCH Formula
Combine the AVERAGE function with INDEX and MATCH to dynamically locate the correct start and end columns for your date range.
This method is highly effective for horizontal data structures where dates are stretched across columns. By using INDEX and MATCH, you can define a dynamic range for the AVERAGE function without needing volatile functions like OFFSET or INDIRECT.
Click on the cell where you want the calculated average price to appear (for example, cell AJ17).
Type the formula: =AVERAGE(INDEX($B12:$AE12,MATCH($AH$2,$B$2:$AE$2)):INDEX($B12:$AE12,MATCH($AI$2,$B$2:$AE$2))). In this formula, $AH$2 and $AI$2 represent your start and end date dropdowns, $B$2:$AE$2 represents your date headers, and $B12:$AE12 is the row containing the prices to average.
Press Enter to execute the formula and view the average unit price for that specific period.
Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the formula to the remaining rows in your table.
Alternative: Use the AVERAGEIFS Function
If your data is structured vertically (dates in one column, prices in another), the AVERAGEIFS function is a much simpler alternative.
Calculate Dynamic Averages Effortlessly in WPS Office
WPS Spreadsheet fully supports advanced array formulas like INDEX, MATCH, and AVERAGE. You can seamlessly perform dynamic date range calculations and data analysis exactly as you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing data report.
- 2. Create date dropdowns: Use Data > Validation to quickly set up your Start Date and End Date dropdown lists.
- 3. Apply the calculation: Enter the AVERAGE and INDEX-MATCH formula in your target cell.
- 4. Fill the series: Press Enter, then drag the fill handle down to calculate the averages for all rows instantly.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
This error usually occurs when the dates selected in your dropdown cells do not exactly match any of the dates in your header row. Ensure there are no hidden spaces and that both ranges use the exact same date format.
Can I use AVERAGEIFS instead of INDEX and MATCH?
Yes, but only if your data is arranged vertically (e.g., Column A for Dates, Column B for Prices). The INDEX and MATCH combination is specifically required when your dates span horizontally across columns.
How do I create the dropdown cells for the start and end dates?
Go to the Data tab on your ribbon, click Data Validation, choose 'List' from the Allow dropdown menu, and select your date headers as the source range.
Will this formula still work if there are blank cells in the price range?
Yes. The AVERAGE function automatically ignores empty cells and text strings, so missing data in your selected period will not skew your calculated average.
