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

**Daxxa** · 04-29-2015, 01:45 PM

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!

**samba_ravi** · 04-29-2015, 02:04 PM

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

**FlameRetired** · 04-29-2015, 02:08 PM

Edited

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

Array enter this formula in F26.

Formula:

Please Login or Register  to view this content.

Array enter this formula in F27.

Formula:

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.

**Daxxa** · 04-30-2015, 04:35 AM

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

**FlameRetired** · 04-30-2015, 11:49 AM

Glad you found solutions. Thanks for the feedback.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Add EXCLUDE Option in table to Exclude from Solver

Count values in column but exclude filtered rows

Sort between bold rows with value of largest to smallest in the fourth column

Exclude rows from SUM based on the row returned from another column

How do I find the smallest number in a list but exclude the zeros ?

Bookmarks

Bookmarks

Posting Permissions