LARGE()

Syntax. LARGE(array,k)

Definition. This function returns the largest k-value in a data set. Use this function to select a value based on its relative size. For example, you can use LARGE() to calculate the top three sales in a table.

Arguments

• array (required). The array or range of data for which you want to determine the largest k-value

• k (required). The position of the element in the array or cell range to return

Background. The MIN() and MAX() functions find the smallest or largest value in a cell range, but if you need the second-largest or third-smallest value, use the LARGE() and SMALL() functions.

LARGE() returns the largest values, and SMALL() returns the smallest values from a range.

Note the following:

• If array is empty, LARGE() returns the #NUM! error value.

• If k is less than or equal to 0 or greater than the number of data points, the function returns the #NUM! error.

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

Examples

Simple Example. The following example explains the formula LARGE(array,k).

If cell range B1:B100 contains your values, LARGE(B1:B100;1) returns the largest value in the array.

Note

The following formula returns the sum of the largest, second-largest, and third-largest value:

=LARGE(B1:B100;1)+LARGE(B1:B100;2)+LARGE(B1:B100;3)

If you use an array formula, you can use a shorter formula:

=SUM(LARGE(B1:B100;{1,2,3}))

After you have entered an array formula, press Ctrl+Shift+Enter. The formula is automatically enclosed in braces.

Although the second formula is shorter than the formula containing the + operator, you have to enter the parameters for all values manually.

Software Company Example. Assume that the software company has a table with the sales of the last two years and wants to know the three highest sales without having to sort the data. The LARGE() function finds and returns the three largest values, as shown in Figure 12-83.

Figure 12-83. Finding the three largest values in a table.

Inside Out: Use conditional formats for Excel 2007

If you use conditional formats together with the LARGE() function, you can highlight the values in color (see Figure 12-84).

Excel 2007 offers many new formats. Open the menu on the Conditional Formatting button and select a format.

Figure 12-84. Selecting one of the numerous formats.

The formats are applied according to the size of the values (see Figure 12-85).

Figure 12-85. Formats as applied in Excel 2007.

See Also

PERCENTILE(), PERCENTRANK(), QUARTILE(), SMALL()

Sample Files

Use the LARGE worksheet in the Count.xls or Count.xlsx sample file. The sample files are found in the Chapter12 folder. For more information about the sample files, see the section titled Using the Sample Files.