At 17:40 19/09/2013 +0200, Regina Henschel wrote:
Brian Barker schrieb:
At 21:28 18/09/2013 +0100, Mark Bourne wrote:
Carl Paulsen wrote:
I'm trying to build an IF statement that tests if a condition
exists in two columns, and assigns the number 1 if it does, and 0
if not. The columns being checked are vLookups which return #N/A
if an ID is not found in another table. I'm trying to search for
cases where an ID# IS returned in two columns meaning the record
shows up in both tables.
I can't figure out the syntax for this. I've tried
IF(AND(A1<>"#N/A"; B1<>"#N/A"),1,0) on both the vlookup formula
columns and on columns that are pasted without formulas. No luck
yet. Can someone chime in on this?
Try:
=IF(AND(NOT(ISNA(A1)), NOT(ISNA(B1))),1,0)
Although the cell is displayed as "#N/A", it's not a text value
but an error code indicating that a value is not available. ISNA()
returns TRUE if a cell contains the #N/A error code, and FALSE otherwise.
Indeed. But it's simpler than that, in fact. If you apply De
Morgan's laws to the expression
AND(NOT(X);NOT(Y))
it simplifies to
NOT(OR(X;Y))
so we can simplify your formula to
=IF(NOT(OR(ISNA(A1);ISNA(B1))),1,0)
And if you exchange the then- and the else-expression, you can drop
the NOT function.
=IF(OR(ISNA(A1);ISNA(B1)));0;1)
And we can also similarly improve my logical expression (that you snipped):
=NOT(OR(ISNA(A1);ISNA(B1)))
into:
=1-OR(ISNA(A1);ISNA(B1))
... where the subtraction of the logical value from one has the
effect of negating it.
Apart from its brevity, this has the additional advantage that it
becomes a numeric expression so will display as 0 or 1 by default
(not as FALSE or TRUE), as the questioner required.
Brian Barker
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
