+ Reply to Thread
Results 1 to 6 of 6

Return nth Largest value using multiple criteria

  1. #1
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Return nth Largest value using multiple criteria

    Hi,
    I've a two column dataset with one column for person name and one column for sales.
    I'm trying return the nth largest value where the person name equals the value in a referenced cell.

    My dataset may grow or shrink over time so I'm trying to avoid array formulae

    Basically I'm looking for something like the below that actually works.
    Please Login or Register  to view this content.
    Sounds like it could be simple, but I'm bamboozelled

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Return nth Largest value using multiple criteria

    Try this,
    D1 being the criteria to find in ColumnA and the value is on Column B
    =SUMPRODUCT(MAX((A1:A21=D1)*(B1:B21)))

  3. #3
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: Return nth Largest value using multiple criteria

    Brilliant!! Thanks Dave

    I was after a way to find the 2nd, 3rd or 40th largest value. Substituting the MAX for LARGE and adding the 'nth' critieria looks to have done the job.

    Much appreciated.

  4. #4
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: Return nth Largest value using multiple criteria

    Not quite as sorted as I'd first thought.

    I seem to have hit some sort of range / array limit while using LARGE.
    At 12529 elements in each array the formula return #N/A
    At 12528 elements in each array the fomula returns the correct value

    The values in range A2:A12530 are all Strings and the values in range B2:B12530 are all numbers
    This works:
    Please Login or Register  to view this content.
    This doesn't work:
    Please Login or Register  to view this content.
    Any clues anyone?

  5. #5
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Return nth Largest value using multiple criteria

    Hi,

    You haven't hit a limit that I know of and, once I corrected the typo in your formula, it worked fine for me in XL 2003.

    Please Login or Register  to view this content.
    Either there's a bug in XL 2007 (which I can't check) or you should scrutinise the data in cells OtherSheet!A12530 and OtherSheet!B12530 (they don't happen to have a #N/A error?). You could check if it's the data in the worksheet that is causing the problem: if you clear the data in OtherSheet does the formula still return an error?
    Hope that helps,

    Colin

    RAD Excel Blog

  6. #6
    Registered User
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    20
    Posts
    1

    Re: Return nth Largest value using multiple criteria

    You don't even need SUMPRODUCT. You can achieve the same result by doing:

    =LARGE((OtherSheet!A2:A12530=ThisSheet!A3)*(OtherSheet!B2:B12530)*1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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