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

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: 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