Reference functions convert empty cells to zero

Posted by Derek on June 27, 2001 3:22 AM

All the Excel reference functions (such as INDEX) return the value of an empty cell as zero. You cannot then tell the difference between data where zero has been entered and data which has been left blank. Of course it would be possible to convert the data (using ISBLANK) into a format where the blanks are identified, before calling INDEX. However if there is a large amount of data this seems a bit of a sledgehammer to crack a nut. Can anyone think of another way around this?

Posted by Russell on June 27, 2001 7:17 AM

Well, you could use ISBLANK in your formula:

If(ISBLANK(Sheet2!D2),"",INDEX(....))

Otherwise I don't know a workaround.

Hope this helps,

Russell

Posted by Derek on June 27, 2001 7:52 AM

Thanks for that, but I'm afraid it won't work because in the formula:

If(ISBLANK(Sheet2!D2),"",INDEX(....))

Sheet2!D2 cannot be hardcoded; it would need to be derived by something like an INDEX, ADDRESS or OFFSET function.

I tried building the address using the ADDRESS function. However the problem is that as soon as you try to obtain the contents of the address by INDIRECT, I get the same problem: empty cells are converted to zeros.

Posted by Russell on June 27, 2001 8:02 AM

Just put your reference formula in the IF(ISBLANK(...)) STATEMENT.

=IF(ISBLANK(INDEX(blah blah blah),"",INDEX(blah blah blah))

That should work.

Russell

Posted by Derek on June 27, 2001 9:00 AM

Oh thanks. I was going to say that:

ISBLANK(INDEX(blah blah blah)) is FALSE because

INDEX(blah blah blah) is "0" not "".

I had assumed therefore that this wouldn't work.

However it turns out to be TRUE (slightly surprisingly I think)!