Page 1 of 1

### Change False?

Posted: Sat Jan 19, 2019 2:56 pm
Is it possible to do something instead of "False" in the formula, for example, an empty cell or the text that I want to introduce?
e.g. in the formula = IF(D =M\$2;B2) or others? ;]
greetings ;]

### Re: Change False?

Posted: Sat Jan 19, 2019 3:40 pm
For a text:
Code: Select all   Expand viewCollapse view
`= IF(D2=M\$2;B2;"The case, when it is not True")`

For a VISUALLY empty cell (a cell is never EMPTY, when it contains a FORMULA):
Code: Select all   Expand viewCollapse view
`= IF(D2=M\$2;B2;"")`

### Re: Change False?

Posted: Sat Jan 19, 2019 4:16 pm
ok is working, but what about #N/A?
like COUNTIF(A2:A50; A2)(some like: if true = ok, if flas = " ")
or VLOOKUP(L2;MyTitle;1;0)
or =r2=s2 (as above)
or ISTEXT(a1) (as above)

is working too?

### Re: Change False?

Posted: Sat Jan 19, 2019 5:58 pm
The formula
Code: Select all   Expand viewCollapse view
`COUNTIF(A2:A50; A2)`

doesn't make a lot of sense since A2 is both the criterion to be searched for and part of the search range. In any case, are you looking for
Code: Select all   Expand viewCollapse view
`IF(COUNTIF(A2:A50; A2);"ok"; "")`

which will show ok if the COUNTIF returns something other than zero.
Similarly, your VLOOKUP returns a value from the search column. But if VLOOKUP might return #N/A, you can use the ISNA() function.
Code: Select all   Expand viewCollapse view
`IF(ISNA(VLOOKUP(L2;MyTitle;1;0));"";VLOOKUP(L2;MyTitle;1;0))`

### Re: Change False?

Posted: Sat Jan 19, 2019 6:48 pm
ok this two is working... but what to do if i have: IF(COUNTIF(A2:A50; B2);"ok"; " ")
and in B2 is #N/A then result is #N/A, not " "

### Re: Change False?

Posted: Sat Jan 19, 2019 7:17 pm
If you want to handle the case of #N/A in B2 and see either a blank cell or ok, use
Code: Select all   Expand viewCollapse view
` =IF(ISNA(COUNTIF(A2:A50; B2));""; "ok ")`

If you want to see the result of the COUNTIF, use
Code: Select all   Expand viewCollapse view
`=IF(ISNA(COUNTIF(A2:A50; B2));""; COUNTIF(A2:A50; B2))`

### Re: Change False?

Posted: Sat Jan 19, 2019 7:20 pm
ok this two is working... but what to do if i have: IF(COUNTIF(A2:A50; B2);"ok"; " ")
and in B2 is #N/A then result is #N/A, not " "

Then you must use more conditions:
There is a function named ISNA(). Use it to determine if an input parameter is valid or not.

### Re: Change False?

Posted: Sat Jan 19, 2019 9:53 pm
The #N/A is an error message, and all error messages, when generated via a formula, take precedence over the normal returns of that formula.
ie: a formula that returns #N/A will show #N/A, rather than the "" you are expecting from your formula.
To include the ISNA function, with your formula, you have to check that first, so your formula could look like
Code: Select all   Expand viewCollapse view
`=if(isna(countif(A2:A50;B2));"";"OK")`

### Re: Change False?

Posted: Sun Jan 20, 2019 4:14 pm
okey, there is a problem with =if(isna(countif(A2:A50;B2));"";(A2:A50;B2))
there is no #N/A is ok, if it finds B2 entries in column A ... it shows "1" but if it does not find "0" shows.

The question is, why the autofilter does not show 0?
Can I change 0? ew to do so that the autofilter can see 0?

### Re: Change False?

Posted: Sun Jan 20, 2019 5:35 pm