Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Extract data with helper column in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to get address of first cell in range in Excel
  • How to get last row in text data in Excel
  • To count total rows in a range in Excel

Data Analysis Examples

  • Conditional Formatting Icon Sets Examples in Excel
  • Move chart to a separate worksheet in Excel
  • How to calculate average last N values in a table in Excel
  • How To Insert and Customize Sparklines in Excel
  • Get column index in Excel Table

Data Validation Examples

  • Excel Data validation date in next 30 days
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel
  • Prevent invalid data entering in specific cells
  • Excel Data validation no punctuation

nth largest value with criteria in Excel

by

This tutorial shows how to calculate nth largest value with criteria in Excel using the example below;

To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, where each value matches supplied criteria, you can use an array formula that uses the LARGE and IF functions.

Formula

{=LARGE(IF(range=criteria,values),n)}

Explanation

In the example shown, the formula in G7 is:

Worked Example:   Tax rate calculation with fixed base in Excel
{=LARGE(IF(Sex="F",Score),F7)}

Where “Sex” is a named range for C3:C15 and “Score” is the named range D3:D15.

Note: this is an array formula and must be entered using Control + Shift + Enter.

How this formula works

The LARGE function is fully automatic — you just need to supply a range and an integer for”nth” to specify the ranked value you want.

Worked Example:   IF with boolean logic in Excel

The problem in this case is that we don’t want LARGE to operate on every value in the range, just values that are either male or female (M or F). To apply this criteria, we use the IF function, which provides a logical test for either “M” or ‘F”. Because we are applying the test to an array of values, the result will also be an array. In the example shown, the resulting array looks like this:

Worked Example:   LARGE function: Description, Usage, Syntax, Examples and Explanation

{93;FALSE;85;FALSE;81;FALSE;FALSE;FALSE;FALSE;72;FALSE;FALSE;83}

Where FALSE represents male scores (“F”) and numbers represent female scores.

The LARGE function will automatically ignore TRUE and FALSE values, so the result will be the nth largest value from the set of actual numbers in the array.

Post navigation

Previous Post:

Minimum value if in Excel

Next Post:

nth largest value with duplicates in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove line breaks in a cell in Excel
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to count line breaks in cell in Excel worksheet
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate total hours that fall between two times in Excel
  • Count dates in current month in Excel
  • Convert Excel time to decimal minutes
  • Basic timesheet formula with breaks in Excel
  • How to calculate next anniversary date or birthday in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • How to calculate compound interest in Excel
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles