# Excel formula for Beginners – How to find Large element with criteria in Excel

Hits: 648 # (Excel examples for Beginners)

In this end-to-end excel example, you will learn – Excel formula for Beginners – How to find Large element with criteria in Excel.

Generic formula
`{=LARGE(IF(criteria,values),n)}`
Summary

To return the largest values in a set of data with criteria, you can use the a formula based on the LARGE and IF functions.

In the example shown, the formula in F5 is:

`{=LARGE(IF(B5:B11="B",C5:C11),2)}`

Note: this is an array formula and must be entered with control + shift + enter.

Explanation

The LARGE function can be used to retrieve “nth” largest value in numeric data like so:

`=LARGE(values,n)`

In this example, we need include only values associated with group B. To do this, we use the IF function to filter:

`IF(B5:B11="B",C5:C11)`

Since we are running a logical test on an range of cells, we get an array of results:

{FALSE;98;FALSE;60;FALSE;95;FALSE}

Note that only values in group B make it into the array. Group A values become FALSE since they fail the logical test. This array is returned inside the LARGE function with 2 hardcoded as as “nth” (the argument “k” in LARGE):

`=LARGE({FALSE;98;FALSE;60;FALSE;95;FALSE},2)`

LARGE then returns 95, the second largest value in group B as the final result.

### Multiple criteria

To take into account multiple criteria, you can extend the formula with boolean logic in a form like this:

`=LARGE(IF((criteria1)*(criteria2),values),n)`

Where criteria1 and criteria2 and represent an expression to test values in a criteria range, as shown in the original example above.

## Excel formula for Beginners – How to find Large element with criteria in Excel

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only \$19.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!