10

I know how to use MAX(A1:A30) to get the highest value in the cell range A1 to A30.

But how do I get the second or third highest value?

8

The following formula, will allow you to choose the highest value you want:

=INDEX(SORT(UNIQUE(A1:A30),1,FALSE),1,0)

The formula explained from the inside out:

  • The SORT function will sort the range DESCENDING via the FALSE statement (high to low). The 1 indicates the first column.
  • The UNIQUE function will exclude any duplicates
  • The INDEX function will allow you to select the order you want: 1 for the first, 2 for the second....and so on.

I was about to write a script and found out that the problem can be resolved by good old formulas !!

6

Try looking for the SMALL and LARGE formula.

I'd use:

=LARGE(UNIQUE(A1:A30);2)

UNIQUE to pick and look only for the different values in the range. The number 2 is for the 2nd largest number.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.