Re: Array formulas, piecewise functions

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

Stephen R Laniel wrote:
> On Thu, Sep 15, 2005 at 06:34:50PM +0100, Oliver Burnett-Hall wrote:
>
> > > =sum(if(and($g$2:$g$30000>=h2,$g$2:$g$30000<h3),1,0))
>
> [snip]
>
> > Have you tested that formula in Excel?  I'd be very surprised if it worked.
>
>
> I've not tested it. But it does seem syntactically valid,
> right? If not, where did I mess up the syntax?

You've got it inside out.

Breaking it down, you start off by creating two 30,000 element arrays of 
booleans from the expressions '$g$2:$g$30000>=h2' and '$g$2:$g$30000<h3'.

You then combine this in using an AND function.  AND converts its 
arguments to booleans; I've no idea what happens when you try and 
convert an array of booleans to a boolean - you'll probably get an error.

The result of this AND function - TRUE, FALSE or error - is then put in 
the IF function, giving you a result of 1, 0 or an error.  And you then 
sum this result.

I don't think the final result is the one you were looking for :)

- olly