Excel does not return 0

[mkr9999]

Opposite problem than most... I have several models that I inherited that return blank on a blank cell. Excel should default to return a zero. I want it to return a zero, as it is causing other formulas to error out. The formula in question is a simple index match referring to another worksheet. The cell it is referring to is indeed blank (no spaces or formula within that cell). Any thoughts?

 

[Fluff]

What is the formula?

 

[mkr9999]

=INDEX('Util B'!$D$18:$IS$211,MATCH($D9,'Util B'!$A$18:$A$211,0),MATCH($B9,'Util B'!$D$18:$IS$18,0))
I forced it to return 0 with
=if (AboveFormula = "",0,AboveFormula) but I shouldn't have to do that.

Another one:
=IF(H9=0,E9,G9) if E9 or G9 are blank, should return a zero. Again, forced to return 0 with:
=IF(IF(H9=0,E9,G9)="",0,IF(H9=0,E9,G9))

 

[joeu2004]

(You finally just posted the formula.)

Re: ``The cell it is referring to is indeed blank (no spaces or formula within that cell)``. Looks can be deceiving.

The only way to truly know is use formulas of the form =ISBLANK(A1).

It is possible that the cell that appears to have no constant or formula actually contains the null string that was copy-and-pasted-value.

To demonstrate, enter ="" into A1, then copy and paste-value back into A1.

 

[mikerickson]

Is it possible that the supposed blank cell actually contains a space?

 

[Fluff]

Both those formulae will return 0 if the relevant cell is genuinely empty.

 

[mkr9999]

Re: Looks can be deceiving.

The only way to truly know is use formulas of the form =ISBLANK(A1).

It is possible that the cell that appears to have no constant or formula actually contains the null string that was copy-and-pasted-value.

To demonstrate, enter ="" into A1, then copy and paste-value back into A1.


Thank you @joeu2004 . The data source was pasted from another file, and that is the issue.

 

[Marcelo Branco]

mkr9999

You said
"..as it is causing other formulas to error out."
So i understand that cell value is not really zero.

But ... verify
File> Options> Advanced> Display options for this spreadsheet
if the checkbox
Show zero in cells whose value is zero
is checked

Just in case...

M.