(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.
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:
Note: this is an array formula and must be entered with control + shift + enter.
The LARGE function can be used to retrieve “nth” largest value in numeric data like so:
In this example, we need include only values associated with group B. To do this, we use the IF function to filter:
Since we are running a logical test on an range of cells, we get an array of results:
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 then returns 95, the second largest value in group B as the final result.
To take into account multiple criteria, you can extend the formula with boolean logic in a form like this:
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: