On Wed, 17 Aug 2011 22:45:37 +0200
Frederic Parrenin <[email protected]> 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
_______________________________________________
gnumeric-list mailing list
[email protected]
http://mail.gnome.org/mailman/listinfo/gnumeric-list
