How to Calculate the Second Largest Value with Duplicates

You can calculate the maximum value with the Max function, but to calculate the second or third largest values, you will need to use the LARGE function.

When there is no duplicate values, please refer to how to calculate the 2nd largest value or using the formula below:

=LARGE(A2:A9,2)

where A2:A9 is the data range.

However, when there are duplicates in the dataset, the above formula may not work. For example, in the data below, there are two largest values of 5, and the formula =LARGE(A2:A9,2) will return 5 instead of 4.

To find the 2nd largest value in the data range with duplicates, please refer to the following formula.

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

=COUNTIF(A2:A9,MAX(A2:A9)) is to determine the number of maximum values. In this example, we have two maximum value of 5.

=LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1) is to find the second largest value.

Leave a Reply