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 the formula below:
=LARGE(A2:A9,3)
where A2:A9 is the data range.
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,3) will return 4, which is the second largest value. The third largest value in the dataset is 3.
To find the third largest value in the data range with duplicates, please refer to the following formula.
=LARGE(A2:A9, COUNTIF(A2:A9,LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1)) + 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, so =COUNTIF(A2:A9,MAX(A2:A9)) returns 2;
= LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1) is to find the second largest value, which is 4;
= COUNTIF(A2:A9,LARGE(A2:A9,COUNTIF(A2:A9,MAX(A2:A9))+1)) is to determine how many of the second largest value, and it returns 1;
In the dataset, we have two largest values of 5, and one second largest value of 4, so the third largest value will be the next largest number, the fourth number in this example.
