LARGE

We already discussed about ARRAY in Excel earlier in this website. Today we will discuss about another function when used with array can prove its immense power in excel formula in certain cases.

Let us have one example to understand where this function will better perform.

Let suppose, there are 100 numbers and you have to provide the sum of top 10 numbers and the numbers are not sorted. If you sort these numbers, then the job can be done but sometimes it is not possible to sort the data. Anyway let us try accomplish the task using function in excel formula.

>

The Microsoft Excel LARGE function returns the ‘n’th largest value from a set of values. The LARGE function is a built-in function in Excel and is categorized as a Statistical Function. 

Let us first discuss the function then we will discuss it with some examples.

Syntax

The syntax for the LARGE function in Microsoft Excel is...

=LARGE( array, k)

Parameters or Arguments

array

A range or array from which you want to return the nth largest value.

k

The position from the largest to return.

Returns

The LARGE function returns a numeric value.

Note : If ‘k’th value is larger than the number of values in array, the LARGE function will return the #NUM! error. If array is empty, the LARGE function will return the #NUM! error.

How to use LARGE function?

Excel LARGE function with criteria

As shown in the below picture there are 11 different numbers, if we need to get the second largest number we can write the excel formula like below.

=LARGE(C3:C13,2)

Here ‘C3:C13’ (i.e., ARRAY) defines the array and ‘2’ (i.e., ‘k’ th position) represents the position of the number which is the largest. 

Excel LARGE function with criteria

How to use LARGE function with ARRAY?

Excel LARGE function with multiple criteria

In the above example let suppose we need the sum of top three largest number, which is 844 + 764 + 740 = 2348. (Colored Blue, Red & Yellow)

Now there is a problem with LARGE function is that it accepts only one argument as the ‘k’th position.

Therefore, we need to pass an ARRAY in this parameter. 

What is an ARRAY?

ARRAY is a set of numbers or strings. If you want to know more about ARRAY, please click here.

We know to implement ARRAY in excl formula we need to press Ctrl+Shift+Enter simultaneously. The formula becomes 

{=SUM(LARGE(C3:C13,E4:E6))}

Now one obvious question will arise what are these curly braces doing in the beginning and at the end of the formula?

The answer to this question is, curly braces will appear whenever we declare an array in any excel formula, curly brace will start at the beginning of the formula and will end at the end of the formula.

Let us explore the above function step by step.

Excel LARGE function with multiple criteria

This excel formula is working in two steps, first the formula finds the top three largest numbers and the second part is summing up these numbers.

For more details please visit the discussion about ARRAY in Microsoft Excel.

Hope you have enjoyed this article.

Thank you for reading if you like this article, please share….