How to test a value

For every row:
In col A I need to test a couple other cells in the row for values (col B,C,D)
Col B,C will have text or blank - no problem there
Col D may contain

  1. blank (empty) or text

  2. blank or text based on a formula

Situation 1 comes about when I COPY the data in the cells to remove the formula and have hard data.

How do I test for a blank returned by a formula?


A3 contains =IF(ISBLANK(B3),IF(ISBLANK(C3),IF(ISBLANK(D3),"",A2),A2),A2)

D3 contains =IF(ISBLANK(E3),IF(ISBLANK(F3),"",D2),D2)

E3 ans F3 are BLANK, putting a BLANK into D3. But D3 has a formula so A3 does not see the BLANK

If a cell has content, no matter if numeric, text or formula, then it is not blank. You can test for an empty string, which yields also true for an empty cell. Furthermore you can simplify the expression in A3 to use less nested IF() calls,


which makes it more readable.

Only for completeness, and only(?) @erAck:
I don’t like array formulas too much, but I often test if a formula also works under array-evaluation as this should be expected regarding the specifications. Therefore:
{=IF(COUNTIF(INDEX(B2:D5;ROW(A1:A4););"")=0;A1:A4;"")} (e.g.) should also work, and
{=INDEX(B2:D5;ROW(A1:A4);)} actually returns the expected array.
The first formula having embedded the second one doesn’t work, however.

@Lupp: Because COUNTIF() returns one scalar value, not an array of values; you can check with =COUNTIF(INDEX(B2:D5;ROW(A1:A4););"") entered as array formula. So the IF() returns either an array of A1:A4, or an empty string. Would be interesting what Excel does there…

Yes. I once more missed to consider the fact that any formula in Calc only can return a scalar or a 2D-array of scalars (second dimension may be hidden), but never an array of arrays. If you leran something different about Excel, please tell me.
(Contributors urgently wanting to do me a favour avoid the comma used as the separator in parameter lists)