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?
Second highest value
google spreadsheets
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 FALSEstatement (high to low). The 1 indicates the first column.The UNIQUE function will exclude any duplicatesThe 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 !!