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.

 

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

 

 

Sign up to get end-to-end “Learn By Coding” example.


 

 

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!