Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles MIN AND MAX Large with criteria

Large with criteria

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.

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

Another blog reader asked this question today on Excelchat:
Solution examples
Given: RetYr = 2057 B48=2055 B49=2056 B50=2057 FirstYrAnnExp = 174,855 B51=2058 M48=IF(B48>=RetYr-1,IF(M47>1,M47*(1+Inflation),FirstYrAnnExp)) Returns FALSE M49=IF(B49>=RetYr-1,IF(M48>1,M48*(1+Inflation),FirstYrAnnExp)) Correctly retuns 0 M50=IF(B50>=RetYr-1,IF(M49>1,M49*(1+Inflation),FirstYrAnnExp)) Correctly returns 174,855 M51=IF(B51>=RetYr-1,IF(M50>1,M50*(1+Inflation),FirstYrAnnExp)) Correctly returns 180,100 How can I avoid “FALSE” when the B column is less than RetYr? A zero or blank would be better.
Solved by V. W. in 20 mins
Hi Can you see any glaring error with the below formula and help me why it wont work? =IF(E8<400,J6,(IF(E8<=1200,J5)),(IF(E8>=1201,J4)))*E8 Thanks Em
Solved by V. C. in 40 mins
I need a formula that looks up if the date (which looks like "8/1/2018 8:05:36 AM") in Column "C" contains year 2018. And then populates "2018" in Column D, if the date does not contain "2018" then enter False in Column D.
Solved by F. H. in 20 mins
I am trying to have a tab change color when the Sum of a row = 0. I am using code on the tab but it appears to be confused because the value of the given cell is not 0 it is "=SUM(G4:G17)"
Solved by S. F. in 41 mins
Could you help me by explaining the IF function?
Solved by G. L. in 24 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc