Excel 2010: Find Nth Largest Value With LARGE Function

Advertisement

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.

table

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.

Advertisement
  • Pingback: All About Fractions In Excel 2010()

  • Pingback: Excel 2010: Calculate Working Days In Date Format()

  • Fdileague

    is there a way for it also to Billy as the person who is the 2nd highest rank?

  • Fdileague

    is there a way for it also to Billy as the person who is the 2nd highest rank?

  • Mhnicholson

    I think the intended question was: Instead of showing the 2nd highest score, how does he show the person who got the second highest score?
     

  • Mhnicholson

    …in which case Fdileague wants something like this:

    INDEX($A$2:$C$11,MATCH(LARGE($C$2:$C$11,$E$3),$A$2:$A$11,0),2)

    • Mhnicholson

       Whups…make that

      INDEX($A$2:$C$11,MATCH(LARGE($C$2:$C$11,$E$3),$C$2:$C$11,0),1)

      I should never post before testing

  • HannuP

    Mhnicholson! It’s not working! When two or three people have the same numbers your formula fails.

  • what if you need a secondary reference number as a “tie-breaker” when there are multiple numbers have equal values? for example the world cup uses the a point system based on win-lose-tie to determine seed. when two teams have the same point, the point scored differential is use to determine the winner. do you have a formula for this?