+ Reply to Thread
Results 1 to 5 of 5

Nth smallest value from column, with option to exclude certain rows

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Nth smallest value from column, with option to exclude certain rows

    Hi all,

    I was wondering if I could please seek your advice on the following problem:

    The attached worksheet contains an extract of data from a large dataset. I would like to return the 'GROUP' and 'Sampling Method' for the Nth smallest 'Total QA cost'. In this case N = 1. As it stands, the forumla in F26 and F27 return the Nth smallest value.

    However, I would now like to understand how to exclude rows where the 'Includes ON-LINE analysis' contains an N. I've tried adding an AND function to the IF but this did not work. Does anyone have any good ideas?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,523

    Re: Nth smallest value from column, with option to exclude certain rows

    a small addition to your formula
    F26=OFFSET(B3,MATCH(SMALL(IF(E4:E21<>"N",IF(A4:A21=F24,F4:F21)),$F$23),F4:F21,0),)
    F27=OFFSET(C3,MATCH(SMALL(IF(E4:E21<>"N",IF(A4:A21=F24,F4:F21)),$F$23),F4:F21,0),)

    Try the above array formulas
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Nth smallest value from column, with option to exclude certain rows

    Edited

    If I've got the concept correctly this is one way.

    Array enter this formula in F26.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Array enter this formula in F27.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Last edited by FlameRetired; 04-29-2015 at 02:13 PM.

  4. #4
    Registered User
    Join Date
    04-29-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Re: Nth smallest value from column, with option to exclude certain rows

    Both solutions work perfectly, albeit with slightly different approaches. Many thanks for your quick replies!

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Nth smallest value from column, with option to exclude certain rows

    Glad you found solutions. Thanks for the feedback.

+ 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. Add EXCLUDE Option in table to Exclude from Solver
    By lbofbb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-17-2015, 09:05 AM
  2. Count values in column but exclude filtered rows
    By elee532 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2013, 12:53 AM
  3. Sort between bold rows with value of largest to smallest in the fourth column
    By minks in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-07-2013, 06:27 PM
  4. Replies: 4
    Last Post: 04-25-2010, 06:49 AM
  5. Replies: 2
    Last Post: 08-10-2007, 08:44 AM

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