How to Average the Three Most Recent Values by Name in Excel
Question details
The user needs a formula to calculate the average of the three most recent non-zero values for a specific name based on date or sequence data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing recent performance or data entries by extracting the moving average of the latest three records per category or person.
- Observed behavior
- Calculating the average specifically for the top 3 most recent entries that meet specific name and non-zero criteria.
Ensure your dataset is organized in clear columns (e.g., Name, Date, Value) and check whether your spreadsheet software supports dynamic array formulas before choosing a method.
Use Dynamic Array Functions (Excel 365 & Modern Spreadsheets)
The most efficient method for newer spreadsheet versions using dynamic arrays like FILTER and SORT.
Modern spreadsheet applications support dynamic array functions, which significantly simplify complex data extraction. By combining FILTER, SORT, INDEX, and AVERAGE, you can easily isolate the most recent records for a specific name.
Ensure your data is structured properly. For example, assume Column A contains Names, Column B contains Dates or Sequence numbers, and Column C contains the Values.
Click on the cell where you want the average to appear. Assume the specific name you want to look up is typed in cell F4.
Type the following formula into the formula bar: =AVERAGE(INDEX(SORT(FILTER(A4:C21,A4:A21=F4),2,-1),ROW($1:$3),3))
Press Enter. The formula will filter the data by the name in F4, sort it by the date column in descending order, extract the first 3 rows of the value column, and calculate their average.
Use an Array Formula (Older Excel Versions)
Ideal for legacy spreadsheet software that does not support dynamic array functions.
Calculate Complex Averages Easily in WPS Office
WPS Spreadsheet fully supports advanced array formulas and modern dynamic functions to help you analyze your recent data efficiently.
- 1. Download and Install: Download WPS Office for free and install it on your device.
- 2. Open your Dataset: Launch WPS Spreadsheet and open your existing workbook containing the names, dates, and values.
- 3. Apply the Formula: Select an empty cell, click on the formula bar, and paste your preferred array or dynamic formula.
- 4. Get Instant Results: Press Enter (or Ctrl+Shift+Enter for legacy arrays) to instantly calculate the average of your recent values.
Frequently Asked Questions
Why does my older Excel formula return a #VALUE! error?
If you are using the older IF and LARGE formula, it requires array evaluation. You must confirm the formula by pressing Ctrl+Shift+Enter simultaneously instead of just Enter. If done correctly, curly braces {} will surround the formula in the formula bar.
Can I average the last 5 values instead of 3?
Yes. In the modern Excel 365 formula, change ROW($1:$3) to ROW($1:$5). In the legacy array formula, change the number 3 at the end of the LARGE function to 5.
Does this formula ignore zero values?
The provided legacy array formula explicitly checks for non-zero values using the logic ($C$2:$C$19<>0). The modern dynamic array formula provided does not inherently exclude zeros, but you can add a condition inside the FILTER function (e.g., FILTER(A4:C21, (A4:A21=F4)*(C4:C21<>0))) to ignore them.
What happens if there are fewer than three records for a specific name?
The formulas will likely return a #NUM! or #REF! error because it cannot find the 3rd value. You can wrap your entire formula in an IFERROR function, like =IFERROR(YourFormula, "Not enough data"), to handle this gracefully.
