Find the second largest value
=LARGE(B2:B7,2)Excel · result on the sample sheet: 41.6
Describe what you need and get a working LARGE formula for Excel and Google Sheets, tested on real data before you copy it. LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=LARGE(B2:B7,2)Excel · result on the sample sheet: 41.6
=SUM(LARGE(B2:B8,{1,2,3}))Excel · result on the sample sheet: $188,050
=SUM(B2:F2)-MIN(B2:F2)Excel · result on the sample sheet: 5 rows (G2:G6)
=MAX(B2:B8)-MIN(B2:B8)Excel · result on the sample sheet: 22
=AVERAGE(LARGE(B2:B9,{1,2,3}))Excel · result on the sample sheet: $62,633
=SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11)Excel · result on the sample sheet: 62.2%
=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))Excel · result on the sample sheet: 5 rows (E2:E6)
=INDEX(SORTBY(A2:A9,B2:B9,-1),E1)Excel · result on the sample sheet: Ben
You describe the task in plain English, and it writes the formula, using LARGE where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the LARGE function page has a practice exercise.