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

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:

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

The simple solution here is not to use an array formula -- just use (in
cell C1) the formula:
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.:
(using the n() function isn't strictly necessary, but it's a habit I've
got into to avoid unexpected results when using Excel).


- olly

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]