# Thread: Array Formula Issue Thanks: 0 Likes:  2 Post #5326688 (1)Post #5326686 (1)

1. ## Array Formula Issue

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  Reply With Quote

2. ## Re: Array Formula Issue

Try

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

3. ## Re: Array Formula Issue

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

4. ## Re: Array Formula Issue

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.  Reply With Quote

5. ## Re: Array Formula Issue

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 *  Reply With Quote

6. ## Re: Array Formula Issue

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   Reply With Quote

7. ## Re: Array Formula Issue

You're welcome  Reply With Quote

## User Tag List

#### Tags for this Thread

array, cell, equal, formula, inclusive #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•