Excel Large Function

The Excel LARGE Function is a statistical function used to return the nth largest value in a numeric data set. The LARGE function sorts the values in descending order and returns the value based on the position specified by the user. For example, the nth largest value could be the 1st largest value, 2nd largest value, 3rd largest value or 4th largest value etc. In today’s post, we would walk you through the LARGE function and how you can use it in Financial Modeling. First, let’s take a look at the Syntax & Argument.

Syntax & Argument

The Syntax of the LARGE Function is as follows:

=LARGE(array, k)

There are only 2 arguments in this function, let’s see what they mean below

• Array: This is an array or range of numeric values you want to check for the nth largest value
• K: Speaking of nth term above, the argument K is the position as an integer and the number defines the nth largest value. For example, if K is 1, then we are asking for the 1st largest value in the array.

Usage notes

In this section, we would review some things to note when making use of this function:

• Only numeric values are accepted by the LARGE Function, blank cells, text, logical values are ignored.
• Using the large function on an array containing only text would result in a #NUM! error
• The argument K defines what you want excel to return from the array. 1 corresponds to the largest value in the list, 2 corresponds to the 2nd largest value in the list, whilst 3 corresponds to the third largest value in the list.

To get the nth smallest value (antithesis of this function), please see the SMALL Function. Now let’s look at some examples of the LARGE Function

LARGE Function Examples

Basic Example

The formula for this section is the LARGE function with hardcoded numbers. In it, we return the 2nd largest number.

Now, let’s take an example with numbers in a range. In our example, we have the scores of students in a quiz, and our goal here is to return the 1st to 3rd position scores.

To return the 1st, 2nd and 3rd positions, we would use the formula below:

Integrating the LARGE Function in Financial Modeling

Yes, now for the fun part. How can we use this in financial modeling? To illustrate, we have carved out a section of a financial model for a power generation company and, in this section, we would be using the LARGE function to calculate the capacity price per MW.

We have arranged the price scenario in descending order from rows 7 – 9, and on cell D12 we have created a dynamic argument K which determines our selected MW per day. If cell D12 display 1, then we are selecting High case. Number 2 implies Base Case and number 3 is for low case.

Why is it so you might ask? Well, remember that the LARGE function sorts the data in descending order and selects the position K specified by the user (I.e if K is 1: largest value in the range, if K is 2: second largest value in the range. Having sorted our input data in descending order, we can be assured that the output of the LARGE Function will always correspond to the cell at the row count (from top to bottom) using the argument K. For example, if the argument K is 1 (largest value, the output of the LARGE function would be same as the number on the first row in our rows 7 -9

Now we can go to cell D15 to input the formula we would be copying and pasting to adjacent cells

We select the range D7:D9 as our array argument. Note that we did not anchor the range because we want the columns to change automatically as we copy and paste across. Next, select cell D12 (capacity price scenario) and finally multiply by the number of days to get the capacity price per day. Remember to press enter when you are done.

Now copy with Ctrl + C and paste across from cells E15: J15 by using Ctrl + V or pressing enter

There you have it, the LARGE Function and its practical use in financial modeling. I am sure you might be wondering why not select directly instead of a capacity price scenario. Well, put it this way, you would need to go into each formula to manually edit when you desire a different scenario whereas in our example you only need to change the number to update the model to a different scenario. Please note however that the CHOOSE Function is more widely used for scenario selection. Kindly check out our post on the CHOOSE Function

Download Excel File