Re: pb/question with the use of the and function

[Oliver Burnett-Hall <olly burnett-hall co uk>]

On Wed, 17 Aug 2011 22:45:37 +0200
Frederic Parrenin <parrenin gmail com> wrote:
> I attach a simple sample .gnumeric file where I have a problem.
> In this file, I thought the result of cell C3 should be 'TRUE' but it
> is 'FALSE'.
>
> Could somebody explain me why?

You've entered this array formula:
  =and(A1:A3="volcanic",B1:B3>1)

This is valid syntax, but doesn't actually make much sense.

A1:A3 is an array with value {"volcanic","isotopic","volcanic"}, and
you're comparing this a text value  of "volcanic", which gives you a
result array of {TRUE,FALSE,TRUE}.

You then do something similar with B1:B3 -- {0.5,0.8,1.8}>1 gives a
result of {FALSE,FALSE,TRUE}.

The problem is that you're then trying to and() these two arrays.  That
doesn't work like you're expecting -- and() returns a single value, not
an array, and will only be TRUE if all values input passed to it are
TRUE.

The simple solution here is not to use an array formula -- just use (in
cell C1) the formula:
  =and(A1="volcanic",B1>1)
Copy this down into C2 and C3.

If you must use an array formula then you need to be multiplying the
component tests together, e.g.:
  =n(A1:A3="volcanic")*n(B1:B3>1)
(using the n() function isn't strictly necessary, but it's a habit I've
got into to avoid unexpected results when using Excel).

HTH,

- olly