How to Fix Excel AVERAGE Formula Error for External Workbooks
Question details
The user needs to properly format an AVERAGE function that calculates data from a different, external Excel workbook.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to calculate the average of a specific cell range located in an external workbook, such as a sales report file.
- Observed behavior
- The formula returns a syntax error because it lacks proper function structure, specifically missing the opening parenthesis between the function name and the external workbook reference.
Ensure that you know the exact file path, workbook name, and sheet name of your external data source. It is highly recommended to keep the source workbook open while building your formula to prevent link update errors and simplify the selection process.
Add Parentheses and Verify External Reference Syntax
Fix the formula error by completely enclosing the external workbook path and cell range within the AVERAGE function's parentheses.
In spreadsheet software, every function requires opening and closing parentheses to contain its arguments. A common mistake when linking external workbooks is placing the file path immediately after the function name without parentheses, like =AVERAGE'https://...'. This immediately breaks the formula calculation.
Click the cell in your current workbook where you want the calculated average to appear.
Enter =AVERAGE( to begin the formula. Do not forget the opening parenthesis.
Type or paste the external link format inside the parentheses. If it is a web path, include the full URL. It should look similar to: 'https://d.docs.live.net/.../[Bike_Sales_2021.xlsx]Sheet1'!$Q$2:$Q$14
Type the closing parenthesis ) at the end of the formula and press Enter. The complete correct formula is: =AVERAGE('https://d.docs.live.net/.../[Bike_Sales_2021.xlsx]Sheet1'!$Q$2:$Q$14).

Build the External Reference Automatically Using the Mouse
Let the spreadsheet software generate the correct complex external reference syntax automatically by selecting the ranges manually.
Calculate External Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers full support for cross-workbook references and advanced formulas like AVERAGE. By using WPS Office's tabbed workspace, you can easily click between open workbooks to automatically generate complex formula syntax, ensuring accurate calculations without syntax errors.
- 1. Open your files in WPS: Open both your destination file and source file in WPS Office's convenient tabbed workspace.
- 2. Initiate the function: Type =AVERAGE( in your target cell to start the formula.
- 3. Select and auto-generate: Click on the source workbook tab at the top, highlight your data range, and press Enter to let WPS auto-generate the perfect formula.

Frequently Asked Questions
Why does my external reference formula show a #REF! error?
A #REF! error usually occurs if the source workbook was deleted, moved, or renamed. It also happens if the specific worksheet or cell range referenced in your formula was deleted from the source file.
Do I need to keep the source workbook open for the AVERAGE formula to work?
Generally, simple calculation functions like AVERAGE will retrieve data from a closed external workbook as long as the file path in the formula is fully defined and accurate. However, keeping the file open helps prevent reference update warnings.
How do I update the external workbook links in my spreadsheet?
Navigate to the Data tab on your ribbon and click on 'Edit Links' or 'Connections'. Select your source workbook from the list and click 'Update Values' to fetch the latest data for your formula.
What is the correct syntax for referencing a closed workbook locally?
For a closed local workbook, the syntax must include the full drive path. For example: =AVERAGE('C:\Documents\Reports\[Bike_Sales_2021.xlsx]Sheet1'!$Q$2:$Q$14).




