Formula Errors

Why the Same Excel Formula Returns Zero in One Workbook and #VALUE! in Another

Phi Hung VoPhi Hung Vo Nov 18, 2026 870 views

Question details

Find out why an identical Excel formula returns zero in one workbook and a #VALUE! error in another.

Why the Same Excel Formula Returns Zero in One Workbook and #VALUE! in Another
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user is comparing the same formula across two workbooks that contain Room and Date data.
Observed behavior
The formula returns zero in one workbook and #VALUE! in the other.
Before you start

Open both workbooks and locate the formula cells and the Room and Date columns they reference. Make sure you are comparing the same rows, criteria, and table ranges.

Solution 1Recommended

Compare the Source Data and Formula References

Use this method to identify differences in data types, errors, or references that could explain the two results.

1
Compare the formulas

Select the result cell in each workbook and compare the formulas in the formula bar. Check that sheet names, table names, column names, and referenced ranges point to the intended data.

2
Check data types

In empty cells, enter =ISTEXT(cell) and =ISNUMBER(cell), replacing cell with a Room, Date, or criteria cell. Compare the TRUE or FALSE results in both workbooks; dates stored as text may not compare like Excel date values.

3
Look for source errors

Inspect the referenced Room and Date cells for errors, or enter =ISERROR(cell) in an empty cell to test a specific reference. Correct any source error or invalid value before retesting the formula.

4
Review locked references

In the formula bar, check whether criteria or calculation references should stay fixed when copied. If appropriate, change references such as AV1, C1, and BL1 to $AV$1, $C$1, and $BL$1.

5
Trace the calculation

Select the formula cell and use Formulas > Evaluate Formula, if available, to step through the calculation. Compare where the values or references begin to differ between the workbooks.

Compare the Source Data and Formula References
A zero may be a valid result: A zero can mean that no records matched the criteria or that the calculation produced zero. It does not necessarily indicate an error.
Free Microsoft Office alternative

Compare Spreadsheet Formulas with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks to review formulas and data as you move between applications; it does not repair an error in Microsoft Excel itself.

Review formulas and referenced spreadsheet data in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a familiar interface that makes moving from Office easierWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the same formula return different results in two workbooks?

The referenced data may differ in type or contain errors, or the formula may point to different ranges or table columns. Compare the formulas in the formula bar and test source cells with ISTEXT, ISNUMBER, and ISERROR.

How can I tell whether an Excel date is stored as text?

Enter =ISNUMBER(A2) in an empty cell, replacing A2 with the date cell. Excel dates are normally stored as numbers; ISTEXT(A2) returns TRUE if the cell contains text.

When should I use an absolute reference such as $AV$1?

Use an absolute reference when the formula should keep referring to the same cell as it is copied. Edit the reference in the formula bar, but first confirm that the cell is meant to remain fixed.

Will IFERROR fix a #VALUE! error?

No. A formula such as =IFERROR(your_formula,0) displays zero instead of the error, but it does not fix the source data or reference that caused it.