Find Nth Largest Value With LARGE Function

Are you looking for a simple way to find out the rank of any given value from the data set? Excel 2010 provides a list of functions and ability to create custom formulas to solve a wide range of data problems. Excel 2010 includes a simple function namely LARGE, which allows you to easily show the current ranking of the any value in the list. It’s an Excel 2010 built-in function that provides the easiest way to find out the rank from the data set. It lets users find out the standing of the specified value from the defined data range or values entered directly as an argument.

Launch Excel 2010 spreadsheet on which you want to apply the LARGE function. For illustration, we have included a spreadsheet containing student records with following fields; Name, Course and Marks, as shown in the screenshot below.

We want to know the marks obtained by students with respect to their position/rank. For this we will be creating a Status label adjacent to the student record table.

status

Now we will find out the marks against their positions. For this, we will use the LARGE function. The syntax of the function is;

=LARGE(array,k)

The first argument is the location of the data set and the second argument is the number for which we want to pull out specific data from the data set. We will enter the position in the first row of Status, and in its adjacent cell, we will write the LARGE function as;

=LARGE(C2:C11,E3)

C2:C11 is the cells’ locations where our data set is residing (Marks field) and E3 is the location of the cell where position/rank number will be shown. For example if you want to find out the 5th position in the data set, it will yield the fifth largest number from your data set, as shown below.

table 1

Now if you want to find out the 2nd position, you’ll just need to enter 2 under the Status label, and it will automatically show you the marks against the 2nd position(as 90 is the second-largest number in the array).

2nd

It must be noted that if you enter the value greater the number of values in the list, it will show you the NUM error. Similarly, entering non-numeric value displays VALUE error. You can also check out previously reviewed Excel functions; CEILING, RANK, TIME, ADDRESS ,FACT (factorial), MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *