Array Formula Issue

[tyija1995]

Hi all,

I have a field of integers residing in cells B3:B26 (randomly ranging from 2 to 22)

I have made an array formula to return a list of integers that are greater than or equal to 10 (in numerical ascending order)

However when I try to do the same but with an AND formula to get numbers between 10 and 15 inclusive, I get a #VALUE ! error.

Working formula (cell C3 - array for greater or equal to 10):
=SMALL(IF($B$3:$B$26>=10,$B$3:$B$26,""),ROW()-2)
CSE and dragged down until I start getting #NUM errors - then I just remove.

Broken formula (cell D3 - array for between 10 and 15 inclusive):
=SMALL(IF(AND($B$3:$B$26>=10,$B$3:$B$26<=15),$B$3:$B$26,""),ROW()-2)
CSE - #VALUE ! returned.

Any ideas? I'm not sure why the array formula doesn't like the AND statement.

Thanks

 

[gaz_chops]

Try

=SMALL(IF(($B$3:$B$26>=10)*($B$3:$B$26<=15),$B$3:$B$26),ROW()-2)

 

[steve the fish]

Its because AND doesnt produce an array. If you need AND use multiplication.

 

[tyija1995]

Thank you gaz - your array formula worked perfectly.

Thank you steve - I am quite new to using array formulas so this is good to know.

Have a good one guys.

 

[Fluff]

For what it's worth, if you ever need to use OR with an array you would do it the same way as the AND but use + rather than *

 

[tyija1995]

Thanks Fluff, that did cross my mind and I assumed it would be an addition symbol - I tested by setting criteria as 10 - 15 OR 20+ for my array and it worked, so thanks for the clarification

 

[Fluff]

You're welcome