Filter a Named Range by Employee Group for a 90-Day Average in Excel or Google Sheets
Question details
Calculate a rolling 90-day average of hours for employees who belong to a selected named employee group.
- Product
- Microsoft Excel or Google Sheets
- Device & OS
- not provided
- Scenario
- The worksheet uses named ranges for hours, dates, employee names, and an employee group such as Technicians or Advisors.
- Observed behavior
- The average should include only records dated from 90 days ago through today where the employee appears in the selected group.
Make sure the Hours, Date, and Employee named ranges have matching row counts and that the Date range contains real date values. Confirm that the employee-group named range, such as Technicians or Advisors, contains the employee names exactly as they appear in the source data.
Use FILTER and XMATCH to average records for the selected employee group
Use this method in Excel versions that support dynamic arrays, or in Google Sheets where the required functions are available.
The formula filters the hours range using three conditions: the date is within the last 90 days, the date is not later than today, and the employee name is found in the selected employee group. AVERAGE then calculates the result from the filtered hours.
In a cell or named formula, enter =TODAY()-90 and name it NinetyDaysAgo if you want to use the formula exactly as shown.
Select the result cell and enter =AVERAGE(FILTER(TableHours,(TableDate>=NinetyDaysAgo)*(TableDate<=TODAY())*ISNUMBER(XMATCH(TableEmployee,Technicians))))
Replace Technicians in the formula with Advisors, or replace it with a cell reference that contains the name of the named employee-group range.
Press Enter and confirm that only rows within the 90-day period whose employee names occur in the selected group contribute to the average.
Use MATCH when XMATCH is not supported
Choose this alternative when the target spreadsheet application does not recognize XMATCH but supports FILTER and MATCH.
Calculate Employee Group Averages with WPS Spreadsheets
WPS Spreadsheets lets you organize employee data with named ranges and calculate rolling averages with date and employee-group criteria. Use familiar spreadsheet formulas and filter functions to analyze hours without changing your workbook structure.
- 1. Open the employee-hours workbook: In WPS Office, open the workbook from the Home screen or select Menu > Open and choose the Excel file.
- 2. Create or verify named ranges: Select each source range, then use Formulas > Define Name to create names such as TableHours, TableDate, TableEmployee, and Technicians.
- 3. Choose the output cell: Click an empty cell where the rolling average should be displayed.
- 4. Enter the group-filtered average: Type =AVERAGE(FILTER(TableHours,(TableDate>=TODAY()-90)*(TableDate<=TODAY())*ISNUMBER(MATCH(TableEmployee,Technicians,0)))) and press Enter.
- 5. Review and update the group list: Edit the cells in the Technicians named range or substitute another group range, such as Advisors, to update the calculation.
Frequently Asked Questions
How do I change the formula from Technicians to Advisors?
Replace Technicians in the membership portion of the formula with Advisors. The Advisors named range must contain the employee names that should be included.
Why does my 90-day average formula return no result?
Verify that TableDate contains valid dates, that its rows align with TableHours and TableEmployee, and that at least one employee name exactly matches an entry in the selected group range.
Can I use a cell to select the employee group dynamically?
Yes. If a cell contains the text name of a named range, use INDIRECT with that cell where the group range is referenced. For example, replace Technicians with INDIRECT(A1) when A1 contains Technicians.
What does ISNUMBER(XMATCH(TableEmployee,Technicians)) do?
XMATCH searches each value in TableEmployee within the Technicians range. It returns a position for matches, and ISNUMBER converts those matches into TRUE values that FILTER can use.
Does the formula include dates exactly 90 days ago and today?
Yes. The >=NinetyDaysAgo and <=TODAY() conditions include records dated exactly 90 days ago and records dated today.
