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

Lookup and Reference Examples

  • Offset in Excel
  • How to get last column number in range in Excel
  • Find closest match in Excel
  • How to get address of last cell in range in Excel
  • Count unique text values with criteria

Data Analysis Examples

  • How To Insert and Customize Sparklines in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Conditional Formatting Rules in Excel
  • Understanding Anova in Excel
  • How to Create Thermometer Chart in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation specific characters only
  • Data validation must not exist in list
  • Excel Data validation allow weekday only
  • Excel Data validation no punctuation

Calculate conditional mode with criteria in Excel

by

To calculate a conditional mode with one or more criteria you can use an array formula based on the IF and MODE functions.

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

Formula

{=MODE(IF(criteria,data))}

Calculate conditional mode with criteria in Excel

Explanation

In the example shown, the formula in F5 is:

{=MODE(IF(group=E5,data))}

where “group” is the named range B5:B14, and “data” is the named range C5:C14.

How this formula works

The MODE function has no built-in way to apply criteria. If you give it a range, it will return the most frequently occurring number in the range.

Worked Example:   Basic outline numbering in Excel

To apply criteria, we use the IF function to test each data value in “group” to see if it matches the value in E5 (“A”):

IF(group=E5,data)

Because the logical test is based on an array containing multiple values (the named range “group”), the result is an array of TRUE FALSE results:

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

where each TRUE represents a row where the group is “A”. This array acts as a filter: for each TRUE, IF returns the corresponding value in the named range “data”. FALSE values remain unchanged. The final result of IF is this array:

{3;FALSE;3;FALSE;5;FALSE;1;FALSE;2;FALSE}

Notice only data values in group A have “survived”; group B values are now FALSE. This array goes into the MODE function, which returns the most frequently occurring number in group A, which is 3.

Worked Example:   Get work hours between dates and times in Excel

Note: when IF is used this way to filter values with an array operation, the formula must be entered with control + shift + enter.

Worked Example:   Return blank if in Excel

Additional criteria

To apply more than one criteria, you can nest another IF inside the first IF:

{=MODE(IF(criteria1,IF(criteria2,data)))}

Post navigation

Previous Post:

How to set check register balance in Excel

Next Post:

Convert column number to letter 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
  • How to get top level domain (TLD) in Excel
  • How to convert numbers to text using TEXT function in Excel
  • TRIM function: Description, Usage, Syntax, Examples and Explanation
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • Get first name from full name — Manipulating NAMES in Excel
  • How to calculate months between dates in Excel
  • How to get same date next month or previous month in Excel
  • Convert date to text in Excel
  • Get first day of previous month in Excel
  • Pad week numbers with zeros in Excel
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment periods for loan in Excel
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles