Second largest value with Duplicates

[acedie]

Hi,

I am having an issue trying to come up with a function to look for the second largest unique value in a column. I know I can use the large function to get the second largest value, but the problem is that there are duplicate values in the column.

For example, I have a range of cells containing, 12,12,12,12,11,10,9,1,0.

The large(a2:a9,2) function will return 12, but I want a return of 11.

Thanks in advance.

[daddylonglegs]

Do you only want the 2nd largest or will you need 3rd largest, 4th largest etc?

For the 2nd largest either use this array formula

=MAX(IF(A2:A9<>MAX(A2:A9),A2:A9))

confirmed with CTRL+SHIFT+ENTER

or a non-array formula

=LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1)