How to average the top N scores (drop the lowest)

✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)

Best-3-of-5 grading, top-scores averages, drop-the-worst policies.

The formula

App	Formula	Notes
Excel (desktop)	`=AVERAGE(LARGE(A2:A6,{1,2,3}))`	The {1,2,3} array asks LARGE for the top three at once. Change to match your N.
Google Sheets	`=AVERAGE(ARRAYFORMULA(LARGE(A2:A6,{1,2,3})))`	Executed 2026-08-30: Google Sheets returned 95 for =AVERAGE(LARGE(A2:A6,{1,2,3})) against 90 in LibreOffice — it averaged the single top score. Google Sheets does no implicit array evaluation inside a scalar function’s arguments, so LARGE(A2:A6,{1,2,3}) collapses to its first value; ARRAYFORMULA around it forces the whole array through. Documented Sheets syntax, carrying no executed result of its own.
LibreOffice Calc	`=AVERAGE(LARGE(A2:A6,{1,2,3}))`	Identical.

How it works

LARGE with the array constant {1,2,3} returns the three best values (95, 90, 85), and AVERAGE collapses them: 90. The complementary drop-the-lowest phrasing is arithmetic instead: =(SUM(range)-MIN(range))/(COUNT(range)-1) drops exactly one worst score. SMALL with the same array pattern averages the bottom N, and SUMPRODUCT(LARGE(range,{1,2,3})) is the total-of-best-3 variant already in the sum-top-n recipe.

The Google Sheets alternative

Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.

Formula	What it does	Returned by LibreOffice 25.8.7.3	Returned by Google Sheets (executed 2026-08-30)
`=AVERAGE(ARRAYFORMULA(LARGE(A2:A6,{1,2,3})))`	ARRAYFORMULA forces the whole {1,2,3} array through LARGE	n/a (Sheets-only formula)	90 Google Sheets alternative (executed 2026-08-30)

Verified, not just documented

We ran =AVERAGE(LARGE(A2:A6,{1,2,3})) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 90 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned 95, which is not what LibreOffice returned (90) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

AVERAGE · LARGE · ARRAYFORMULA — see full Excel, Google Sheets & LibreOffice compatibility for each.