Nth Largest Value With Criteria in Excel

The nth largest value with criteria is the value at a chosen rank after excluding entries that do not meet your conditions. To calculate it, provide the values, a ranking position such as 1 for largest or 2 for second largest, and the criteria each row must satisfy; the result is the matching value at that rank.

Formula

For eligible values sorted from largest to smallest, the answer is the value in position n. In symbols, answer = sorted eligible values[n], where n is the rank and positions start at 1. The calculator’s value list contains the numbers being ranked; the eligible-flags list uses 1 for a value that passes the criteria and 0 for one that does not. The two lists must have the same number of entries and be in corresponding order.

The displayed block is a compact specification for the page calculator, not a full spreadsheet implementation: its output expression cannot filter one list using another or select the nth sorted item. A usable calculation requires applying the criteria first, then sorting the remaining values and selecting the requested position. The example below shows that operation explicitly. A real calculator should return the selected value, or report that the rank is invalid; it must not treat the largest eligibility flag as the requested value.

Worked Example

Use the exact defaults above. The values are 12, 30, 18, 30, 25, and 9. Their eligibility flags are 1, 1, 0, 1, 1, and 1, so 18 is excluded and the eligible values are 12, 30, 30, 25, and 9.

1. Sort the eligible values from largest to smallest: 30, 30, 25, 12, 9.
2. The requested rank is n = 2.
3. Select the second item: 30.

Therefore the nth largest eligible value is 30. The specification’s output line above is not mathematically adequate for this result; it should be implemented with a filtering-and-order-statistic operation rather than maxl(eligible). This distinction matters because a calculator description should match its actual formula.

What “Nth Largest With Criteria” Means

“Largest” describes descending order, while “with criteria” means that only records meeting specified rules participate. The rank is counted after filtering. If the qualifying values are 42, 35, and 20, then the second largest is 35, regardless of how many excluded values are larger.

Criteria may be a single condition, such as department equals “Sales,” or multiple simultaneous conditions, such as department equals “Sales” and status equals “Complete.” In the latter case, a row qualifies only when it satisfies both conditions. Some tasks instead use alternative conditions (either category A or category B); these are logically different and should be stated clearly.

• Rank 1: largest qualifying value.
• Rank 2: second item in the qualifying values ordered largest first.
• Rank n: nth item in that ordered set, with n a positive whole number.

How to Calculate It in Excel

In newer Excel versions, FILTER can create the qualifying set and LARGE can select its nth largest value. For example, if numbers are in A2:A100, matching categories are in B2:B100, the desired category is in E2, and the rank is in F2, use:

=LARGE(FILTER(A2:A100,B2:B100=E2),F2)

For two conditions, such as category in column B and status in column C, combine the tests with multiplication, which represents AND for these TRUE/FALSE arrays:

=LARGE(FILTER(A2:A100,(B2:B100=E2)*(C2:C100=G2)),F2)

FILTER is available in Microsoft 365 and newer perpetual Excel releases that include dynamic arrays; older versions may not support it. A traditional alternative is an array formula or a helper column that marks qualifying values, followed by ranking only those values. Formula syntax and array entry behavior can differ by spreadsheet version.

Ties, Empty Results, and Invalid Ranks

Ties do not normally change the meaning of nth largest. If the sorted values are 30, 30, 25, then both the first and second largest values are 30. This is a rank by position, not a request for the second distinct value. To find the second distinct largest value, duplicates must first be removed.

If fewer than n rows qualify, there is no nth qualifying value. Spreadsheet functions may return an error in this case; a robust workbook can show a clear message instead. The same issue occurs when the filter produces no rows, or when n is zero, negative, non-integer, or larger than the count of qualifying numeric values. Decide whether blanks and text should be ignored or handled separately; common ranking functions generally rank numbers, not arbitrary text.

Common Mistakes and Practical Checks

• Filtering after ranking: rank the qualifying values, not the full dataset followed by a criteria check.
• Misaligned ranges: each criteria range must cover the same rows as the values range. Different lengths can cause errors or unintended matches.
• Confusing AND with OR: multiplying condition tests generally means all must match; OR logic needs a different expression.
• Counting duplicates incorrectly: ordinary nth-largest calculations count repeated values as separate positions.
• Using a text criterion that does not match: trailing spaces, inconsistent spelling, and mismatched data types can leave the filtered set empty.

Before relying on the answer, inspect the filtered values and count them. This makes it easier to catch a wrong range or an unintended criterion. Also confirm whether the intended rank is positional or distinct, and whether the desired sort is largest-to-smallest rather than smallest-to-largest.

Frequently Asked Questions

How do I find the second largest value that meets a condition in Excel?
Filter the values using the condition, then apply LARGE to the filtered result with 2 as its rank. For example: =LARGE(FILTER(A2:A100,B2:B100=E2),2).

Does nth largest count duplicate values?
Yes. A repeated value occupies a position each time it appears. Use a distinct-value approach first if you want the nth different value.

What if fewer than n values meet the criteria?
There is no result at that rank. Check the number of qualifying numeric entries and either choose a smaller rank or handle the no-result case in the spreadsheet.

Can I use more than one criterion?
Yes. Apply all required conditions when filtering. In Excel array formulas, multiplying Boolean tests commonly combines conditions as AND; separate logic is needed for OR.

Is LARGE with criteria the same as MAXIFS?
No. MAXIFS returns the single largest value meeting criteria. It does not select the second, third, or another ranked value; use filtering with LARGE for those ranks.

Related

• Staged AWS Rotation for Node.js LLM API Keys
• Match Server Infrastructure to AI Workloads
• How to Encrypt Email With Proton Mail

in n | Rank (n) | - | 2 | 1
list values | Values to rank | 12, 30, 18, 30, 25, 9
list eligible | Eligible flags (1=yes, 0=no) | 1, 1, 0, 1, 1, 1
out result | Nth largest eligible value | - | maxl(eligible) | 0