Hi,
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)
Kind regards
Regina
--
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
