Change False?
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Change False?
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 ;]
e.g. in the formula = IF(D =M$2;B2) or others? ;]
greetings ;]
OpenOffice 4.1.6 Windows 10
Re: Change False?
For a text:
For a VISUALLY empty cell (a cell is never EMPTY, when it contains a FORMULA):
Code: Select all
= IF(D2=M$2;B2;"The case, when it is not True")
Code: Select all
= IF(D2=M$2;B2;"")
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: Change False?
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?
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?
OpenOffice 4.1.6 Windows 10
Re: Change False?
The formula
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
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
COUNTIF(A2:A50; A2)
Code: Select all
IF(COUNTIF(A2:A50; A2);"ok"; "")
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
IF(ISNA(VLOOKUP(L2;MyTitle;1;0));"";VLOOKUP(L2;MyTitle;1;0))
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: Change False?
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 " "
and in B2 is #N/A then result is #N/A, not " "
OpenOffice 4.1.6 Windows 10
Re: Change False?
If you want to handle the case of #N/A in B2 and see either a blank cell or ok, use
If you want to see the result of the COUNTIF, use
Code: Select all
=IF(ISNA(COUNTIF(A2:A50; B2));""; "ok ")
Code: Select all
=IF(ISNA(COUNTIF(A2:A50; B2));""; COUNTIF(A2:A50; B2))
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Change False?
Then you must use more conditions: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 " "
There is a function named ISNA(). Use it to determine if an input parameter is valid or not.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Change False?
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
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
=if(isna(countif(A2:A50;B2));"";"OK")
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: Change False?
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?
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?
OpenOffice 4.1.6 Windows 10
Re: Change False?
Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Change False?
When did autofilter get introduced to this problem?
Autofilter, like all of the filters, shows just the rows that match the filter.
I hate auto-filter and always use the Standard Filter, as I find it gives me much more control over the information to be shown.
Your current problem does not match (any longer) the original topic, so, technically you should mark this one as Solved, then start a new topic with this new problem.
There are several topics regarding auto-filter already on the forums, so I'm going to suggest you check them before starting a new topic.
Autofilter, like all of the filters, shows just the rows that match the filter.
I hate auto-filter and always use the Standard Filter, as I find it gives me much more control over the information to be shown.
Your current problem does not match (any longer) the original topic, so, technically you should mark this one as Solved, then start a new topic with this new problem.
There are several topics regarding auto-filter already on the forums, so I'm going to suggest you check them before starting a new topic.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.