'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Average Weekly Friday Percentages Across Multiple Sheets in Excel
Question details
The user needs to calculate the average percentage for Friday dates across four different worksheets based on a common date lookup, avoiding zero values or #DIV/0! errors when a date is missing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating weekly reporting metrics (such as percentages) across multiple worksheets based on a specific weekday date.
- Observed behavior
- The user wants the formula to return a blank instead of 0% or a #DIV/0! error when date entries or corresponding percentage values are missing.
Ensure that all your worksheets have consistently formatted date values in the lookup column (e.g., Column A) and that the percentage data you want to extract is correctly formatted as percentages in the target column (e.g., Column I).
Use XLOOKUP and IFERROR to Average Percentages
This method uses XLOOKUP to safely find Friday dates across four sheets, averages the found values, and returns a blank if no data is found or an error occurs.
By wrapping multiple XLOOKUP functions inside an AVERAGE formula, you can pull corresponding values from different sheets. Wrapping the entire formula in IFERROR prevents ugly #DIV/0! errors when data is missing.
Using the fourth argument in XLOOKUP (if_not_found) as an empty string ("") ensures that missing values are ignored during the average calculation.
Create a summary sheet and input your first Friday date in cell A2. Make sure this cell is formatted as a Date.
Click on the cell where you want the average percentage to appear (e.g., B2).
Type the following formula into the formula bar: =IFERROR(AVERAGE(XLOOKUP(A2,Sheet1!$A$2:$A$1000,Sheet1!$I$2:$I$1000,""),XLOOKUP(A2,Sheet2!$A$2:$A$1000,Sheet2!$I$2:$I$1000,""),XLOOKUP(A2,Sheet3!$A$2:$A$1000,Sheet3!$I$2:$I$1000,""),XLOOKUP(A2,Sheet4!$A$2:$A$1000,Sheet4!$I$2:$I$1000,"")),"")
Press Enter to calculate the result. Click the fill handle on the bottom right of the cell and drag downward to apply the formula to subsequent weeks.
Easily Average Multi-Sheet Data with WPS Office
WPS Spreadsheet fully supports advanced functions like XLOOKUP and IFERROR, making it incredibly easy to consolidate weekly data across multiple worksheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet workbook containing the weekly data.
- 2. Set up the lookup date: In your summary sheet, type your target Friday date into the designated column.
- 3. Enter the XLOOKUP formula: Select the target cell and paste the provided XLOOKUP and AVERAGE formula in the formula bar.
- 4. Copy the formula: Drag the fill handle to copy the formula for all upcoming weeks to automatically fetch and average the data.
Frequently Asked Questions
Why is my XLOOKUP formula returning an error instead of a blank?
This usually happens if the IFERROR function is missing or improperly wrapped around the AVERAGE function. Ensure your formula starts with =IFERROR( and ends with ,"") to catch any calculation errors.
Can I use VLOOKUP instead of XLOOKUP for this task?
Yes, but XLOOKUP is generally easier because it does not require counting column index numbers. If you use VLOOKUP, you will need to wrap it in an IFERROR for each individual sheet lookup to avoid breaking the AVERAGE function when a date is not found.
How do I ensure my dates advance exactly by one week?
In your date column, type your first Friday date in cell A2. In cell A3, type =A2+7 and press Enter. Drag this cell down to generate consecutive Friday dates.
Why is my formula calculating as 0% instead of leaving the cell blank?
If the lookup cell is empty or the destination cell contains a zero, the spreadsheet might average it as a zero. Using "" in the 'if_not_found' argument of XLOOKUP prevents it from feeding a zero into the AVERAGE function.
