+ Reply to Thread
Results 1 to 8 of 8

Return LARGE with tiebreakers and title from result

  1. #1
    Registered User
    Join Date
    06-09-2014
    Location
    Hawaii
    MS-Off Ver
    Office 365
    Posts
    75

    Return LARGE with tiebreakers and title from result

    Hello,

    I hope this makes sense.

    I am trying to return the LARGE numbers from an array while keeping tiebreaker results in tact and moving onto the next result.

    Here are the formulas that seem to work but I am having trouble with the tiebreakers.

    This array returns the LARGE result and moves on to the next result even if duplicate. Seems to work but I'm having a hard time validating.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This array returns the header title for the LARGE result. Does not return the correct header.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Following is a sample spreadsheet of how the data looks.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2212
    Posts
    37,029

    Re: Return LARGE with tiebreakers and title from result

    Your formulae do not tally with your data or your data layout,. Can you re-check and re-post?
    Glenn



  3. #3
    Registered User
    Join Date
    06-09-2014
    Location
    Hawaii
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Return LARGE with tiebreakers and title from result

    Sorry about that.

    Attached is the revised spreadsheet with the formula arrays included.

    As you can see my tiebreakers don't return the correct header title.

    Cheers!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,893

    Re: Return LARGE with tiebreakers and title from result

    This solution makes use of a helper table B7:H10, which could be hidden for aesthetic purposes, that utilizes the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The most objects table is then populated using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The header name table is then populated using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2212
    Posts
    37,029

    Re: Return LARGE with tiebreakers and title from result

    Or this array formula in O2, copied across & down, with no helper column:

    =IFERROR(INDEX($B$1:$H$1,SMALL(IF($B2:$H2=J2,COLUMN($B2:$H2)-1,""),1+COUNTIF($I$2:I2,J2))),"")

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-09-2014
    Location
    Hawaii
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Return LARGE with tiebreakers and title from result

    Thank you gents! Both solutions seem to work.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2212
    Posts
    37,029

    Re: Return LARGE with tiebreakers and title from result

    You're welcome and thanks for the Reputation.

  8. #8
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    892

    Re: Return LARGE with tiebreakers and title from result

    For Excel 2010+ offers a simpler option.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Breaking Tiebreakers
    By bbobes327 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-26-2014, 04:21 PM
  2. Replies: 1
    Last Post: 06-03-2014, 08:16 AM
  3. Filtering a large range of data to return only row title and cell value
    By adamhirst in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2013, 08:47 PM
  4. Ranking Tiebreakers
    By Confused9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2010, 08:29 AM
  5. Large SumProduct Array -- original title:Loop Function
    By Divius in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-21-2007, 11:25 AM
  6. excel result return wrong calcuation result
    By garyww in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2006, 06:20 AM
  7. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1