Excel formula: nth largest value

nth largest value

If you have a set data and you want to get the 2nd, 3rd, 4th,….nth largest value, you can use the LARGE function. This is a function that can help you to select a value (s) in a list based on their standing. The formula is straightforward in application.

Generic Formula:

=LARGE(rng,nth)

….Where “rng” is the range of the data while “nth” is the data value you are looking.

Application Example

The best application is in a class set up. Assuming you are a teacher, you have scores for students of a number of tests, how do you the nth largest value? Check out how we have used LARGE in this example:

Figure 1. Example 1 of LARGE Function

From the example, the formula in H6 is:

=LARGE($C6:$F6,1)

How the Large Formula Works

Working with the LARGE function is pretty straightforward. The function is fully automated thus all you need is to apply the range, and an integer which will specify the rank of the value you’re looking for. In the formula, H6 is the 1st largest number. That is how you are able to get the nth largest value using the LARGE function. NOTE: MAX can also be used to get the largest value.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

I want to find the largest date in nth row. But my n is expressed in MATCH() form. I tried LARGE(MATCH(A4,A:A, 0):MATCH(A4,A:A, 0), 1). Only works when large(N:N,1)

An Excelchat Expert solved this problem in 14 mins!

how can i add every nth cll in a column

An Excelchat Expert solved this problem in 16 mins!

If I have names of some persons that are repeated often in an excel file. Which function in excel will give me the nth row when I use argument like persons name and nth integer which reflect nth time repetition of that name?

An Excelchat Expert solved this problem in 14 mins!