Re: [libreoffice-users] Multi-test IF statement in Calc

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 #NA 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<>"#NA"; B1<>"#NA"),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?
>
> Many thanks in advance.

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.

Hope that helps.

Mark.

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
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