+ Reply to Thread
Results 1 to 9 of 9

Find Largest Value with Criteria, but limit Instances

  1. 11-27-2018, 07:57 PM #1
    amonty78
    amonty78 is offline
    Registered User
    Join Date
    08-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    78

    Find Largest Value with Criteria, but limit Instances

    Hi all,

    I have a formula that looks for the largest value in column X, based on the value in column K. The formula currently searches all rows above the current cell, however I need to find only the 5 most recent instances.
    My worksheet is sorted by oldest to newest, so it would be the largest of the 5 instances directly above the current cell (hope that makes sense ?).

    Here is the formula that I already have :

    Please Login or Register  to view this content.

    I'm just not sure how to add this extra critera to it.

    Thanks :-)
    Reply With QuoteRegister To Reply
  2. 11-27-2018, 08:40 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find Largest Value with Criteria, but limit Instances

    Which cell is the 'current cell'

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 11-27-2018, 09:01 PM #3
    amonty78
    amonty78 is offline
    Registered User
    Join Date
    08-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Find Largest Value with Criteria, but limit Instances

    Apologies, I have attached a file as an example.
    In the example the formula (in AA36), is picking up the largest value of all records (76), where as I need it to pick up the largest value of the first 5 matching cells from the bottom (so I need it to return 73 from cell X26).
    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-27-2018, 09:51 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Find Largest Value with Criteria, but limit Instances

    Try this.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  5. 11-27-2018, 09:57 PM #5
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find Largest Value with Criteria, but limit Instances

    Hi,

    With a helper column Y, in Y14 copied down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Then the result is

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 11-27-2018, 10:30 PM #6
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Find Largest Value with Criteria, but limit Instances

    Simplify Dave's formula

    =AGGREGATE(14,6,X14:X34/(K14:K34=K36)/(ROW(X14:X34)>AGGREGATE(14,6,ROW(X14:X34)/(K14:K34=K36),6)),1)
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  7. 11-27-2018, 10:44 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Find Largest Value with Criteria, but limit Instances

    @ Bo_Ry

    Yup! Good one.
    Reply With QuoteRegister To Reply
  8. 11-28-2018, 02:59 AM #8
    amonty78
    amonty78 is offline
    Registered User
    Join Date
    08-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Find Largest Value with Criteria, but limit Instances

    Thanks all for your input, it works perfectly.
    Cheers !
    Reply With QuoteRegister To Reply
  9. 11-28-2018, 08:33 AM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Find Largest Value with Criteria, but limit Instances

    You're welcome. Glad to help. Thanks for the feedback and marking your thread Solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find largest value with criteria
    By Moonfaxx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-03-2017, 12:43 PM
  2. find the largest RANK value by criteria
    By ccsmith in forum Excel General
    Replies: 2
    Last Post: 05-12-2010, 08:04 AM
  3. Find largest alphanumeric value matching alpha criteria in databas
    By Alison in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  4. Find largest alphanumeric value matching alpha criteria in databas
    By Alison in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. Find largest alphanumeric value matching alpha criteria in databas
    By Biff in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] Find largest alphanumeric value matching alpha criteria in databas
    By Alison in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Find largest alphanumeric value matching alpha criteria in databas
    By Alison in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1