Explaining how the LARGE Function works

gray elephant

  • Applies to: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Starter 2010.

This post will deep dive into how the LARGE function works in Microsoft Excel. It will include a summary of this function, its formula syntax, its key conditions, as well as an example for you to play around with it to your delight!

Summary of how the LARGE Function works

The LARGE function works by returning the k-th largest value in a data set. You can deploy this function to pick a value according to on its specific position. For instance, you can employ LARGE to capture the highest, runner-up, or third-place score.

A large wave to represent the LARGE function.
Image by Elias Sch. from Pixabay.

Syntax of how the LARGE Function works

LARGE(array, k)

The LARGE function syntax contains these essential arguments:

  • Array  –  Required. The array or data range in which you are looking to establish the k-th largest value.
  • K  –  Required. The place (from the largest) in the array or cell range of data to yield.

Key conditions of how the LARGE Function works

  • If array is empty, LARGE brings backs the #NUM! error value.
  • If k ‚ȧ 0 or if k is greater than the amount of data points, LARGE captures the #NUM! error value.

If n is the number of data points in a range, then LARGE(array,1) brings back the largest value, and LARGE(array,n) yields the smallest value.

A large tree to symbolise the LARGE function.
Image by My pictures are CC0. When doing composings: from Pixabay.

An example applying the LARGE Function

Copy the example data in the following table, and paste it into cell A1 of a fresh Excel worksheet. For formulas to present results, choose them, press F2, and then press Enter. If you have to at any time, then you can freely modify the column widths to enjoy the full view of the data.

DataData
34
52
34
56
47
FormulaDescriptionResult
=LARGE(A2:B6,3)3rd largest number in the numbers above5
=LARGE(A2:B6,7)7th largest number in the numbers above4

Similar Topics – Statistical Functions

Find the middle value with MEDIAN

Rank data without sorting with RANK

Return the largest value in a data set with LARGE

Return the smallest value in a data set with SMALL

Tabulate blank cells with COUNTBLANK 

Determine frequency distributions with FREQUENCY 

Build analysis via regression techniques with TREND

GROWTH function

All statistical functions

Leave a Reply

%d bloggers like this: