Analyzing data often requires to find the largest value. Sometimes we need to extract the largest value based on a criteria. Excel offers a great solution to find the largest value with criteria. The LARGE and IF functions help us to find the largest value with criteria. In this tutorial, we will learn how to find the largest value with criteria in Excel.
Figure 1. Example of How to Use Large with Criteria in Excel
Generic Formula
{=LARGE(IF(criteria,values),n)}
Here, we use the functions LARGE and IF. LARGE retrieves the “nth” largest value from a numeric data. The IF function here performs a logical test on the range to see if they fulfil the criteria. The values that satisfy the criteria returns those values. The ones that does not satisfy the condition returns FALSE. This array is returned inside the LARGE function. We hardcode the number k for the kth value. This returns the kth largest number.
Setting up Data
The following example uses a student information database. Column A, B and C has the names, ids and ages
Figure 2. The Sample Data
To find out the second highest age in grade 4, we need to
- Go to cell E5.
- Assign the formula
=LARGE(IF(B2:B8=”Grade 4”,C2:C8),2)to E5. - Press Ctrl + Shift + Enter to apply it as an array formula.
Figure 3. Applying the Formula
This will show the second highest age, which is 8.
Notes
We can find the largest value based on multiple criteria as well. To do that we need to extend the formula based on boolean logic. The formula would have the syntax {=LARGE(IF((criteria 1)*(criteria 2),value),n)}. This will find the nth largest value based on multiple criteria.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment