Results 1 to 3 of 3

Thread: Display values only of the first X Occurences of a condition in a small range .
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Display values only of the first X Occurences of a condition in a small range .

    Hi...

    Could anyone help me with a solution to this.

    i am wondering if there is a function that will help me find... say 1st/2nd etc occurences of a condition in a list ?

    e.g.

    I want to pick out the first 3 numbers (say) that are over zero.

    so if my list is 0.00, 23.45, 45.67, 0.00, 0.00, 78.90, 98.76 then I want ONLY 23.45, 45.67 and 78.90. (the first 3 numbers that are over zero only)

    if my list is 12.34, 0.00, 0.00, 56.78, 0.00 ,0.00 ,0.00 .... then I only have 12.34, 56.78 and then a 0.00.

    My actual aim and therefore next step would be to determine the position of those found values in their original lists, but I can do that using the MATCH function.

    I mention both steps as ideally if I can get the positions in the lists without the middle step then great.

    Any clues even would be a massive help as I seem to have gone around in circles with this one.

    Thanks in advance.


    Dave (Sharky007)

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,167
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Display values only of the first X Occurences of a condition in a small range .

    Hi & welcome to MrExcel
    How about

    BCD
    20.003
    323.454
    445.679
    50.00
    60.00
    70.00
    80.00
    978.90
    1098.76

    Roster



    Array Formulas
    CellFormula
    D2{=MATCH(SMALL(IF($B$2:$B$10>0,$B$2:$B$10),ROWS($1:1)),$B$2:$B$10,0)+1}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

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

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display values only of the first X Occurences of a condition in a small range .

    Thank you so much for the quick reply.

    I will try immediately I get home.

    Thanks again.

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
  •