CODEWITHSUNDEEP
excelstringif-statementerror-handlingboolean-logic
Jack Armstrong
Jack Armstrong

Reputation: 1239

Checking values with IF statement

Very simple question. I have four columns. If the value in column 1 is False OR 0 OR N/A, then I want to check column 2. If the value in column 2 is False OR 0 OR N/A, then I want to check column 3. If the value in column 3 is False OR 0 OR N/A, then I want to print value in column 4. The formula that I have is as follows:

=IF(NOT(OR(ISNA(BJ2),BJ2=FALSE,BJ2=0)),BJ2,
IF(NOT(OR(ISNA(BK2),BK2=FALSE,BK2=0)),BK2,
IF(NOT(OR(ISNA(BL2),BL2=FALSE,BL2=0)),BL2,BM2)))

So BJ is column 1, BK column 2, BL column 3, BM column 4. The formula works for the False and 0 errors, but when I add the ISNA part, it breaks down. In fact it simply doesn't do anything more.

The data in the columns are either Strings, FALSE (not a string), or #N/A as a value not available error. I checked one of my N/A values with ISNA and the result was True. Therefore the OR statement should come back as TRUE, then pass through the negation and be false. Therefore the If statement should jump to the next column.

What is causing this not to read?

Upvotes: 1

Views: 51

Answers (1)

Leandro Caniglia
Leandro Caniglia

Reputation: 14868

The problem is that an expression like, say, BJ2=FALSE or BJ2=0 will evaluate to N/A if BJ2 happens to be N/A. So, if anything of this happens the OR function will answer with N/A, because OR() evaluates all its arguments! (don't know why).

Upvotes: 1

Related Questions