AND in an array function?

[Vince]

Does anyone know how to nest an AND function into an array. Suppose
I'm trying to find the highest column B value that coresponds to a
column A value between 92.05 and 92.25

A B
92 2.89
92.01 7.03
92.02 4.67
92.06 2.76
92.11 5.11
92.13 3.25
92.21 5.58
92.27 7.47
92.29 7.5
92.3 7.04

I've tried this array formula
{=MAX(IF(AND($A$1:$A$10>=92.05,$A$1:$A$10<92.25),($B$1:$B$10)))}
but it returns a 0. If I take out the AND function and only compare
column A values above or below one threshold it works. How do I make
this search work?

Thanks

[Bernard Liengme]

You cannot use Boolean function in an array function but you can use Boolean
operators: AND is *, OR is +
SO you could use this (entered, of course, with CTRL+SHIFT+ENTER
=MAX(IF(($A$1:$A$10>=92.05)*($A$1:$A$10<92.25),($B$1:$B$10)))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Vince" <[email protected]> wrote in message
news:[email protected]...
> Does anyone know how to nest an AND function into an array. Suppose
> I'm trying to find the highest column B value that coresponds to a
> column A value between 92.05 and 92.25
>
> A B
> 92 2.89
> 92.01 7.03
> 92.02 4.67
> 92.06 2.76
> 92.11 5.11
> 92.13 3.25
> 92.21 5.58
> 92.27 7.47
> 92.29 7.5
> 92.3 7.04
>
> I've tried this array formula
> {=MAX(IF(AND($A$1:$A$10>=92.05,$A$1:$A$10<92.25),($B$1:$B$10)))}
> but it returns a 0. If I take out the AND function and only compare
> column A values above or below one threshold it works. How do I make
> this search work?
>
> Thanks
>

[Vince]

Thanks, that was painfully simple.