Find nth largest value, when there are duplicate n-1, n-2 etc values

**Bobneil** · 08-19-2013, 02:54 AM

Hi gurus,

I'm having an issue using Large formula, I'm sure there's a simple alternative.

Say you have a list of 3 numbers

3
3
1

I want to find the second largest. Large formula will return 3 as the second largest. I want a formula that will recognise that 3 and 3 are the same, and the second largest number in that list is 1.

Likewise, if the list is

5
5
4
4
3
1

And I want the 3rd largest number, I want a formula to return the number 3.

The list of numbers won't already be sorted, and there will be blank cells that should be ignored.

Any help would be most appreciated.

Many thanks

**ramananhrm** · 08-19-2013, 03:01 AM

Please try this file.

Note: If you require 2nd, 3rd, 4th... etc, then you can modify the function.

Eg: In the below mentioned example, you can modify the '2' to any number you want.

=LARGE(IF(FREQUENCY($D$2:$D$4,$D$2:$D$4)<>0,$D$2:$D$4),2)