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

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