nth largest value with criteria

If you have a set of data and you want to get the nth largest value with criteria ( i.e.2nd, largest, 3rd largest, 4th largest values and so on), where the value is matching the supplied criteria, use the LARGE together with IF function. The formula is straightforward in application. Here’s is the general formula:

Generic Formula

=LARGE(IF(rng=criteria,values),n)

…..Where “rng” is the range and is the nth value you are looking for.

Application Example

Assume that have data for workers in your company of a number of hours worked. If you want to get the nth largest value with criteria (2nd, 3rd…..largest value) and break it down in gender (male and female), this is how you apply LARGE and IF functions: Check out the example below:

Figure 1. Example 1 of LARGE and IF Function

From the example, the formula is

=LARGE(IF(C5:C14="F",D5:D14),F6)

….where C5:C14 is the GENDER range and D5:D14 is the hour worked range. F is female. The same formula can be applied for male gender “M.” NOTE that for the formula to work, it must be entered used the Ctrl+shift+enter on the keyboard

How the LARGE/IF Formula Works

The application of the formula is straightforward. What you need is to supply the range and the integer for the “nth” that helps to specify the rank you are looking for. IF is the first function and it provides a logical test for either “Male/M” or “Female/F.” While working for the female gender, the IF function in this example returns this array:

{FALSE;53;FALSE;49;FALSE;48;46;47;59;FALSE}

…where the FALSE represent Male score.

LARGE will work on the returned array where TRUE and FALSE will be ignored. Therefore, only the nth largest value with criteria from the supplied data will be generated.