You can calculate the maximum value with the Max function, but to calculate the second or third largest values, you need to use the LARGE function. If your data does not have duplicates, please use the formula below:
=LARGE(A2:A9, 2)
You can change the data range A2:A9 to your own range. However, the formula will not work when you work on a dataset that includes duplicates.
Example: You have the data in the table below with the data range from 2 to 5, and there are two largest values of 5.
Formula: To count the second-largest value
If we use the LARGE function directly =LARGE(A2:A9,2), the result will return 5 instead of 4 because of the two largest values of 5.
To find the 2nd largest value in the data range with duplicates, we will use the following formula:
=LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1)
- COUNTIF(A2:A9,MAX(A2:A9)) is to count the number of maximum values. In this example, we have two maximum values of 5.
- LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1) is to find the largest value after the max value.