2013 Archives by date, by thread · List index

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