How to find the 2nd largest value in a series of data that may contain multiple incidences of the same value?

[greenpower78]

Hi, I am trying look for a function or a solution to finding the second highest value in the array of data that may contain multiple instance of same values. for example: array = [31,28,31,27,28,29,26]. The LARGE function couldn't help me because if I pass arguments like LARGE(array.2) it will return 31 instead of 29 because there are two instances of 31.

 

[JimM]

How about something like

=LARGE(UNIQUE(A1:A7),2)

 

[greenpower78]

Thanks! It is one of the possible solutions. But I realized the unique function is exclusive to Office 365. Can you think of 2016 version for the sake of compatibility?

 

[JimM]

In that case, it's not very pretty but could try

=LARGE(A1:A6,COUNTIF(A1:A7,MAX(A1:A7))+1)

 

[greenpower78]

Genius! Solved my problem Thanks!