Ask Your Question

How to test a value

asked 2019-06-18 01:47:06 +0100

waltp gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-06-18 21:37:21 +0100

waltp gravatar image

Thanks, erAck. So ISBLANK checks the physical contents of a cell, not the evaluated contents. That makes sense.

edit flag offensive delete link more


Yes, but please add comments with add a comment, not as answer to the original question, which it is not. Thanks.

erAck gravatar imageerAck ( 2019-06-20 13:43:46 +0100 )edit

answered 2019-06-18 13:11:26 +0100

erAck gravatar image

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.

edit flag offensive delete link more


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 gravatar imageLupp ( 2019-06-18 13:53:10 +0100 )edit

@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..

erAck gravatar imageerAck ( 2019-06-20 13:23:31 +0100 )edit

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)

Lupp gravatar imageLupp ( 2019-06-20 14:03:30 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-06-18 01:47:06 +0100

Seen: 412 times

Last updated: Jun 18 '19