Formula Errors

How to Fix Excel Circular References and Display 65 When Result is Zero

Khadija Khan Sep 30, 2026 871 views

Question details

The user needs a formula to display 65 when the calculated value from another workbook is zero, but the current attempt results in a circular reference, a #NAME? error, or displays as 065.

How to Fix Excel Circular References and Display 65 When Result is Zero

Product: Excel
Device & OS: not provided
Scenario: Referencing data across workbooks to calculate a maximum value and substituting zero results with a specific numerical value without leading zeros.
Observed behavior: The formula triggers a circular reference warning, returns a #NAME? error due to invalid syntax, or formats the output incorrectly with a leading zero as 065.

Before you start

Ensure you are using a modern version of your spreadsheet software that supports the LET function, and verify that your target cell is not formatted as Text or with a custom number format that forces leading zeros.

Solution 1Recommended

Use the LET Function to Prevent Circular References

By defining a variable with the LET function, you calculate the maximum value only once. This prevents infinite calculation loops (circular references) and streamlines the formula.

A circular reference occurs when a formula refers to its own cell directly or indirectly. By using the LET function, we evaluate the target range first, store it in a temporary variable, and then apply our IF logic.

Select the target cell

Click on the cell where you want the final calculated result (or 65) to be displayed.

Input the LET formula

Type the formula =LET(m,MAX(E52:I52),IF(m=0,65,m)) into the formula bar. Replace E52:I52 with your actual external workbook reference if needed.

Verify the logic

This formula assigns the result of MAX(E52:I52) to the variable 'm'. It then checks if 'm' is equal to 0. If it is, it outputs 65; otherwise, it outputs the actual maximum value.

Press Enter

Press Enter to apply the formula and check that the circular reference warning is resolved.

Use the LET Function to Prevent Circular References

Formula Validated: Using LET not only avoids circular references but also improves calculation performance by preventing redundant evaluations.

Solution 2

Resolve the #NAME? Error and Remove Leading Zeros

Fix syntax errors that cause the #NAME? error and adjust cell formatting to ensure the number displays as 65 instead of 065.

Advanced Formula Support

Solve Formula Errors Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and array functions like LET, IF, and MAX. You can easily build complex, cross-workbook references without worrying about circular errors or syntax compatibility issues.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your data.
2. Apply the LET formula: Select your output cell and paste =LET(m,MAX(E52:I52),IF(m=0,65,m)) into the formula bar.
3. Format as General: Right-click the cell, select 'Format Cells', and apply the 'General' format to prevent 65 from displaying as 065.

Fully compatible with Microsoft Excel formats (.xlsx and .xls)Native support for modern data functions like LET to streamline complex calculationsIntuitive error checking to easily spot and resolve circular references and #NAME? errorsLightweight and fast, even when referencing heavy external workbooks

microsoft office alternative - wps office

Frequently Asked Questions

What causes a circular reference warning in spreadsheets?

A circular reference happens when a formula directly or indirectly refers to the cell it is typed in. This creates an infinite loop where the formula tries to calculate its own result continuously. Moving the formula to a different cell or restructuring the logic usually solves this.

Why is my formula returning a #NAME? error?

The #NAME? error is triggered when the software cannot recognize text in a formula. The most common reasons are misspelled function names (e.g., typing MAXX instead of MAX), forgetting to put text strings inside double quotes, or referencing a named range that hasn't been defined.

How do I stop numbers from showing leading zeros like 065?

Leading zeros appear when a cell is formatted as Text, or when a Custom Number Format (like '000') is applied. To remove them, select the cell, right-click to access 'Format Cells', and change the category to 'General' or 'Number'.

Is the LET function available in all versions of Excel?

No, the LET function is only available in Microsoft 365, Excel 2021, and newer versions, as well as modern alternatives like WPS Office. If you are using an older version (like Excel 2019 or earlier), you will need to repeat the calculation instead: =IF(MAX(E52:I52)=0,65,MAX(E52:I52)).

Excel & Data Problems