Excel formula showed as '=#NULL' returns correct result

0
votes

I have an Excel file, in that file I have plenty of formulas and ALL seem to do what they suppose to, give the results.

Now the weird part, I have found that some(few) cells instead of showing in formula bar original formula like =SUM(A1:A5) are now showing formula like =#NULL!.

However in cell it self result is displayed and it is correct result, recalculated with each change in sheet. I had a backup copy of that file, I have copied formulas over and everything seemed fine until day after. When file was reopened issue has returned.

Have anyone have any idea how to sort out that mystery?

enter image description here

Edit: Maybe I wasn't clear enough. Formulas are working and calculating correctly. The issue lies in showing formula it self, formula bar isn't showing formula used for calculation, instead it is showing =#NULL! formula in formula bar.

Please see picture with 'Show Formula' enabled. All cells showing =#NULL! should actual formulas used for calculation. Look specially at formula bar it self.

Excel - Show formula enabled view

1
excelexcel-formula
The error is #NULL! Errors – Incorrectly Separated Cell References, so I guess first check for this using formula auditing tools. support.office.com/en-us/article/… - QHarr
@QHarr That is not and issue, please see edit in OP - Leszek J.
Anyone? Any idea? - Leszek J.

1 Answers

0
votes

I can't give you a definitive answer without actually seeing all your formulas, but generally the #NULL! error appears when (according to documentation):

This error occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space character between references.

Most common issues (I'll use formula =SUM() since you used it in your original question, but this doesn't exclusively apply to it)

=SUM(A3 A6)

=SUM(A1:A5, B1:B5)

=SUM(A1, #REF!)

#REF! is commonly caused if you are referencing to a cell, that from to a different worksheet that is no longer available, or the reference is no longer correct altogether`

So to sum it up (no pun intended) check up if your formulas have correct seperators (,) or (;). Check if your formulas make sense (eg. you can't sum two ranges, use =SUBTOTAL()instead) and check if your cell references are still correct.