Position of k-th value in SMALL(array, k)

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Position of k-th value in SMALL(array, k)

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Position of k-th value in SMALL(array, k)

     
    Hi All,
    I cannot find the simple way of how to get the position (row number) of the k-th value for the functions like
    SMALL(array, k).
    please find an example:

    value k k-th smallest
    1 1 1
    2 2 1
    1 3 2
    3 4 3
    4 5 4

    Formula in C2 is the following: =SMALL($A$2:$A$6,B2)
    Formula in C3 is logically as: =SMALL($A$2:$A$6,B3)

    So Excel understands that even though values are repetitive ("1" in this example), they can take different places in grading (1st and 2nd in this example)

    lets say that result in C2 is the "1" in the 1st matching row of the range, and result in C3 is the "1" in the 2nd matching row of the range (if excel gives "1" twice).

    So my question: is there a way to reveal the number of the row, where the 2nd "1" is, based on the criteria "k" entered in formulas above?

    something like:
    if k=1, show me row number of the 1st smallest value
    if k=2, show me row number of the 2nd smallest value (even though values repetitive)

    Solution might be simple if values (in column A) are different- we could use MATCH, VLOOKUP or other formulas, but if there are the repetitive values, MATCH and VLOOKUP give the result of the 1st match only, so if we used those, they would reveal the ROW number 2 for both cases, meaning that both 1st and 2nd smallest value is in the row 2, what is not really logical.

    I tried to use OFFSET, COUNTA and other functions, but the formulas become a Kilometre of length and they do not cover cases when value repeats 3,4 or n times.
    Helper Columns might be helpful but maybe there's another way how to get the result?

    Hope my problem is clear enough, can't wait for the solution

    Thanks in advance

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,991
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Position of k-th value in SMALL(array, k)

    Hi
    Welcome to the board

    This is one solution. please try:


     ABCD
    1valuerowrow k-th smallest 
    2111 
    3223 
    4139 
    5342 
    6456 
    7264 
    8475 
    9587 
    10198 
    11    
    [Book1]Sheet1




    AddrFormula
    [Book1]Sheet1
    C2 =SMALL(IF($A$2:$A$10=SMALL($A$2:$A$10,ROWS($C$2:C2)),$B$2:$B$10),ROWS($C$2:C2)-COUNTIF($A$2:$A$10,"<"&SMALL($A$2:$A$10,ROWS($C$2:C2))))
    This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.
    Copy down
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,991
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Position of k-th value in SMALL(array, k)

    ... or, if you calculate the k-th smallest in another column, the formula is simpler:


     ABCDE
    1valuerowk-th smallestrow k-th smallest 
    21111 
    32213 
    41319 
    53422 
    64526 
    72634 
    84745 
    95847 
    101958 
    11     
    [Book1]Sheet1




    AddrFormula
    [Book1]Sheet1
    C2 =SMALL($A$2:$A$10,ROWS($C$2:C2))
    Copy down
    D2 =SMALL(IF($A$2:$A$10=C2,$B$2:$B$10),ROWS($D$2:D2)-COUNTIF($A$2:$A$10,"<"&C2))
    This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.
    Copy down
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,991
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Position of k-th value in SMALL(array, k)

    ... if you don't want to use an array formula, use in my last example in D2:

    =AGGREGATE(15,6,$B$2:$B$10/($A$2:$A$10=C2),ROWS($D$2:D2)-COUNTIF($A$2:$A$10,"<"&C2))

    Copy down
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Position of k-th value in SMALL(array, k)

    Thank you so much, you did a great job!

    1st option works perfectly, however the goal of my calculations is slightly different, and i still cannot find an answer to it.

    I will show you the exact example that i currently have:

    I have a range of data:
    price quantity
    78 3
    60 1
    80 3
    206 4
    87 3
    60 5
    114 2
    92 4


    and afterwards i have another table, with 5 lowest prices from the above and the respective quantity

    60 1
    60 5
    78 3
    87 3
    92 4

    So my question is: how to get the "5" in the 2nd table? and what the formula should look like, if price repeated n times in the 2nd table?

    Thank you in advance and Karma points granted from my part

  6. #6
    New Member
    Join Date
    Nov 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Position of k-th value in SMALL(array, k)

    I think I found it, just had to clarify the logics of the formula.
    Thank you, pgc01!

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,991
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Position of k-th value in SMALL(array, k)

      
    I'm glad it helped.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

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
  •  

 

 
DMCA.com