*From*: Oliver Burnett-Hall <olly burnett-hall co uk>*To*: gnumeric-list gnome org*Subject*: Re: pb/question with the use of the and function*Date*: Wed, 17 Aug 2011 23:45:48 +0100

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

**References**:**pb/question with the use of the and function***From:*Frederic Parrenin

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