7 Replies Latest reply on Sep 23, 2012 7:41 PM by Nay Lin Soe

# function for 2nd, 3rd, ..., nth largest/smallest value in a column

Hello all,

I know that min() and max() functions select the smallest ad largest values from a column. Is there a way to pick the 2nd, 3rd, ..., nth largest/smallest values from a column?

Nay

• ###### 1. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Hi Nay,

I don't think there is a function that gives you this straight off. Probably you could find the nth largest number using the Index() function?

Regards

Siraj

• ###### 2. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Hi Siraj,

Thanks for responding. Index() function, as far as I know, returns the positional value of a row in a table, regardless of its content. Could it really help in ranking values?

Nay

• ###### 3. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Hi Nay,

Yes, you could use it to find the position in a set as long as you order the set (sort) it in the way you want.

Cheers

Siraj

• ###### 4. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Hi Nay,

I have attached a workbook with an example for your reference. Hope it helps.

Cheers

Siraj

1 of 1 people found this helpful
• ###### 5. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Hi Siraj,

Thank you for attempting this for me. I've looked at what you have attached. It works on the pre-condition that the table is sorted, at design time, according to Sales column. I suppose there is no direct way to find, say, the 2nd largest sales value if there is NO guarantee that the column is sorted (which is my case)?

Nay

• ###### 6. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Independent Ranking
Ranking can be calculated even if table at design time is not sorted. Example below is silly in production, but does show that ranking can be independent of sorting used in table:

Filter Individual Rankings
If you what you meant was to filter only the 2nd to for example 10th in rank of sales, this can be done with a quick filter as seen below:

• ###### 7. Re: function for 2nd, 3rd, ..., nth largest/smallest value in a column

Excellent! Thank you for your help.