Array Formula Issue
Results 1 to 7 of 7

Thread: Array Formula Issue

  1. #1
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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
    √-1 2³ ∑ π
    …And it was delicious!

  2. #2
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array Formula Issue

    Try

    =SMALL(IF(($B$3:$B$26>=10)*($B$3:$B$26<=15),$B$3:$B$26),ROW()-2)
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Array Formula Issue

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

  4. #4
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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.
    √-1 2³ ∑ π
    …And it was delicious!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,817
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default 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 *
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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
    √-1 2³ ∑ π
    …And it was delicious!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,817
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Array Formula Issue

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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