+ Reply to Thread
Results 1 to 11 of 11

Return 2nd largest value (ignoring duplicates) with criteria

  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Return 2nd largest value (ignoring duplicates) with criteria

    I tried so long to get this on my own, then I searched the web for hints, and as a last resort I am posting it up here myself!

    So here is some example data:


    A B
    1 25
    1 40
    1 40
    2 25
    2 16
    2 35
    2 35
    2 30
    2 13


    Now say I want the 2nd largest number from column B but only if there is a 2 in column A. The answer in this case should be 30.

    I've used many different combinations of LARGE, IF, COUNTIF, MAX, SUMPRODUCT, Ctrl+Shift+Enter, etc.

    This was my most recent attempt:

    =SUMPRODUCT(LARGE((A2:A10=2)*(B2:B10),COUNTIFS(A2:A10,2,B2:B10,MAX(B2:B10))+1))

    I know that the following formula works if I need the 2nd largest number with no duplicates:

    =LARGE(B2:B10,COUNTIF(B2:B10,MAX(B2:B10))+1) which should give 35 in my example and it does

    My problem comes into play when I try to add the criteria.

    Any help would be greatly appreciated (fix my formulas or give me a totally different formula)

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    Hi Sqwirtle

    See the attached file where I have written an array formula to obtain the desired result. thanks.
    second largest.xlsx

    Regards,
    DILIPandey


    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    That only works for this specific example. I need something more automatic.

    If you change the example to:

    A B
    1 25
    1 40
    1 40
    2 25
    2 35
    2 35
    2 35
    2 35
    2 35

    The answer is obviously 25 but your formula gives 35.

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    Sqwirtle,

    Using the setup in dilipandey's workbook, this regular formula returns the correct result in both scenarios:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    haha, I tried following that formula and it gave me a headache. Maybe I've been looking at this problem too long or I just have no idea what your formula does

    If possible, can you give a quick explanation what it's basically doing? If that's not possible, it's all good.

    Anyways it works! Thanks so much!


    Edit: Although it works, I am getting a Circular Reference Warning. Any ideas ?
    Last edited by Sqwirtle; 07-12-2012 at 04:24 PM.

  6. #6
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    Here is a curveball, what if I wanted to do the same thing but instead of returning that 2nd largest number I wanted to return the corresponding number in column C

    A B C
    1 25 101
    1 40 102
    1 40 103
    2 25 104
    2 35 105
    2 35 106
    2 35 107
    2 35 108
    2 35 109

    I originally took Tigeravatar's formula and added another index in front of it with a match for the row which worked. The problem though is that I am still getting that underlying circular argument and it is bogging down the whole spreadsheet very badly.

    Any suggestions?
    Last edited by Sqwirtle; 07-12-2012 at 04:53 PM. Reason: typo

  7. #7
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    That formula shouldn't cause a circular reference (unless you have the formula somewhere in A1:B9). I'd have to see an example workbook that is experiencing the problem.

    As for what the formula does (warning, gets kind of long)...

    ROW(INDIRECT("1:"&ROWS(B1:B9))))

    This basically creates an array of sequential numbers, 1 through the number of rows in the data. In this case, the data is 9 rows, so it creates the following array:
    {1,2,3,4,5,6,7,8,9}

    That array is fed to the Large() function with the purpose of returning the numbers in descending order. In Excel terms:
    Large(index_of_values,{1,2,3,4,5,6,7,8,9})

    So it returns Large(_,1) then Large(_,2), etc to get all of the values in descending order.



    The index_of_values is generated from this part:
    INDEX((A1:A9=2)*B1:B9,)

    Basically, that says if A1:A9=2, then return B1:B9. A1:A9=2 will return an array of True/False values. Anything True will return a 1 and anything False will return a 0. That gets multiplied to B1:B9, so anything that met the criteria will return successfully, and anything that was false will return a 0 (because it gets multiplied by 0). So now we have the values according to the criteria. The Large() function discussed earlier then sorts them in descending order so that the max value is first.



    Next is the CountIf part, which is used to get the 2nd largest value:
    COUNTIF(B1:B9,MAX(INDEX((A1:A9=2)*B1:B9,)))+1

    You'll notice the use of the Index() with the use of (A1:A9=2)*B1:B9. This is the second time this formula we're using it. That's because this time, that array of values isn't being sorted, its being evaluated by Max(). That returns the largest value. The largest value is fed to the CountIf function, so now we know exactly how many times that max value occurs. This is useful because the Large() function earlier sorted the values in descending order. So if the max value is repeated 4 times, the first 4 values are the max value. So we add +1 to the count so that we return the next number in the series, which is the 2nd largest number ignoring duplicates. Now that we have which position it is, we can feed that to the overarching Index() formula:


    Index(array_of_sorted_values, position_number)
    The array_of_sorted_values is generated from the Large(Index(),Row()) function. The position_number is from the CountIf(value,Max(Index())+1) function. So now, out of that sorted list, it grabs the 2nd highest number.


    Hopefully that explanation helps.

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    Quote Originally Posted by Sqwirtle View Post
    Here is a curveball, what if I wanted to do the same thing but instead of returning that 2nd largest number I wanted to return the corresponding number in column C
    Didn't see this post until after I was done writing up the explanation. Formula changes quite a bit, here you go:
    Please Login or Register  to view this content.

    Quote Originally Posted by Sqwirtle View Post
    The problem though is that I am still getting that underlying circular argument and it is bogging down the whole spreadsheet very badly
    As mentioned in the explanation post, I'd need to see an example workbook that is experiencing the problem in order to diagnose it.

  9. #9
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    Thank you so much for all your help.

    I really appreciate you taking the time to explain it as well.

    My data set is ~1800 lines summarizing it into ~100 lines and its bogging this bad boy down like crazy. So once I had them all calculated, I had to copy special (values). I hope no else who looks at my spreadsheet needs to know where those numbers came from. -_-

    Thanks again!

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  11. #11
    Registered User
    Join Date
    04-29-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Return 2nd largest value (ignoring duplicates) with criteria

    Thanks tigeravatar for the explanation and formula. I plugged in the formula into my spreadsheet of 36000 rows and it seems like it doesn't work but does when I use a smaller set. Is this a problem due to the size of my sheet?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1