+ Reply to Thread
Results 1 to 9 of 9

TAKE and SORTBY Functions to Extract Top and Bottom n Results

  1. 06-04-2023, 12:39 PM #1
    andrewc
    andrewc is offline
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    810

    TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Hi,

    I would like to use the TAKE and SORTBY functions together to extract the top and bottom 3 product names (in column A) based on annual sales (in column B).

    I have the formula:

    =TAKE(SORTBY($A$1:$A$100,$B$1:$B$100,-1),{3;-3})

    The formula correctly returns the top and bottom selling product names if I have {1;-1} in the formula but it doesn't work properly if I use instead {3;-3}.

    Can someone please suggest what's wrong with the formula shown above?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 06-04-2023, 12:48 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Not sure what's wrong - sample workbook?

    You could try this:

    =LET(s,SORTBY(G8:G17,G8:G17,-1),VSTACK(TAKE(s,3),TAKE(s,-3)))

    Change the sortby arrays to suit your data.
    Last edited by AliGW; 06-04-2023 at 12:52 PM.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 06-04-2023, 02:41 PM #3
    andrewc
    andrewc is offline
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    810

    Re: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Thanks AliGW.

    I've attached a sample of what I mean.

    Sample data in A:B and the desired outcome is in column D.

    My attempt at the formula is in column F.

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-04-2023, 08:17 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: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Edited: Missed the SORTBY SALES part. (faceinpalm) Now corrected.

    Try maybe
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 06-04-2023 at 08:27 PM.
    Dave
    Reply With QuoteRegister To Reply
  5. 06-05-2023, 12:53 AM #5
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,427

    Re: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Pls try this formula , Cell D2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 06-05-2023, 03:17 AM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Or maybe:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  7. 06-05-2023, 03:32 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Thank for the rep.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  8. 06-05-2023, 06:53 PM #8
    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: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    Quote Originally Posted by AliGW View Post
    .......
    =LET(s,SORTBY(G8:G17,G8:G17,-1),VSTACK(TAKE(s,3),TAKE(s,-3)))

    Change the sortby arrays to suit your data.
    Oooh! I did not discern your LET did exactly the same thing.

    Sorry about that Ali.
    Reply With QuoteRegister To Reply
  9. 06-06-2023, 12:24 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: TAKE and SORTBY Functions to Extract Top and Bottom n Results

    No apology necessary!
    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] Search bottom to top of a column to displaying all results that match a criteria
    By EricDonk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2022, 03:37 PM
  2. [SOLVED] Extract and sortby in descending order
    By paradise2sr in forum Excel General
    Replies: 5
    Last Post: 01-09-2022, 05:19 AM
  3. Find last row and count multiple criteria using last row (bottom up) for results
    By cvercrus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-04-2013, 12:32 PM
  4. 2 IF functions and remove bottom 2 cells from a series total
    By baker2gs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-19-2012, 12:06 AM
  5. How to get the average, ignoring top and bottom 10 most extreme results?
    By ycomp in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-29-2008, 04:12 PM
  6. No filter results in bottom left hand corner
    By Natalie in forum Excel General
    Replies: 1
    Last Post: 10-24-2005, 01: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