Check if the referenced cell inside an array is `0

Question

I need to find some way of telling apart zeroes and empty cells in e.g. the results of the array value of OFFSET function. In the included illustration, I'd like to have empty string in the cells D4 and D6.

The B5 equals ="", and B7 is genuinely empty. I don't want to distinguish between these cases.

• I'm interested only in formula solutions that take constant number of cells for each row of index vector.

• No VBA.

• As a last resort I will accept a solution that uses the popular MoreFunc Excel package or other free and publicly available Excel add-on.

I know how to do that using RExcel, but this is too sophisticated, and there will be issues/difficulties with cell dependence during recalculations (not to mention installation of R and "friends").

---

Here is a related question of mine:

Is there a function, that would return “” when input ref is empty, and its contents if it is not?

Answer 1

OK, I've found one. Actually it was very simple.

=COUNTBLANK(<array of refernces>)