Max if Criteria Match: How It Works
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This calculator works out the maximum value from a set of numbers, but only for the entries that meet a condition you set. You enter your list of values, choose a comparison rule, and the calculator returns the largest qualifying number—or a clear message if none qualify. The result is the highest value that passes your criteria, not the overall maximum.
Formula
The logic is simple: Max if criteria match = the largest number in the list that satisfies the condition. The condition is one of four comparisons against a threshold value. In symbols, for a list L and threshold t:
- Greater than: max of {x ∈ L | x > t}
- Less than: max of {x ∈ L | x < t}
- Equal to: max of {x ∈ L | x = t}
- Not equal to: max of {x ∈ L | x ≠ t}
Each variable is defined as:
- L – the list of numbers you enter (comma-separated).
- t – the threshold value you enter.
- x – each individual value in the list.
The result is the maximum of the filtered subset. If no value satisfies the condition, the result is “None”.
Worked Example
Using the default values from the calculator: list = [4, 8, 15, 16, 23, 42], criteria = “Greater than”, threshold = 10.
- Filter the list: keep only values greater than 10 → [15, 16, 23, 42].
- Find the maximum of that filtered list: max(15, 16, 23, 42) = 42.
- The calculator displays 42 as the answer.
If you change the criteria to “Less than” with the same threshold, the filtered list would be [4, 8], and the max would be 8. If you choose “Equal to” with threshold 10, no value equals 10, so the result is “None”.
When to Use This Calculation
This type of conditional maximum appears in data analysis, spreadsheet work, and everyday decision-making. You might use it to:
- Find the highest sales figure above a certain target.
- Identify the largest temperature reading below freezing.
- Determine the most recent date (as a numeric timestamp) that falls before a deadline.
- Filter a list of scores to only those above a passing grade, then find the top score.
It is especially useful when you need to exclude outliers or focus on a specific range. For example, a teacher might want the highest test score among students who scored below 50 to see who needs the most help, ignoring the students who passed.
How to Read the Result
The result is the single largest number that passes your condition. If you see “None”, it means no value in your list meets the criteria—not that the maximum is zero. This is a common source of confusion. For instance, if your list is [1, 2, 3] and you ask for values greater than 10, the answer is “None”, not 0 or 3.
Also note that the result is the maximum of the filtered set, not the original set. If you want to know which value it is, you can find it by scanning the list for the number that matches the result and the condition.
Common Mistakes and Pitfalls
Several errors frequently occur when working with conditional maximums:
- Mixing up the condition direction: “Greater than 10” and “Greater than or equal to 10” are different. This calculator uses strict comparisons (>, <, =, ≠), so be clear about whether you need inclusive or exclusive.
- Using the wrong threshold unit: If your list is in meters and your threshold is in feet, the result will be wrong. Always convert to the same unit first.
- Forgetting that the list may contain non-numeric entries: This calculator expects numbers only. If you have text or blank cells, remove them or handle them separately.
- Assuming the result is the second-largest overall: If the overall maximum does not meet the criteria, the result will be the largest value that does, which might be much lower. For example, list [100, 5, 3] with “less than 10” gives 5, not 100.
Limitations of the Formula
This calculation works only with a simple list of numbers and a single threshold. It does not handle:
- Multiple criteria (e.g., values between 10 and 20). For that, you would need a more complex formula or a spreadsheet function like MAXIFS.
- Text or logical values. The list must be purely numeric.
- Very large lists (millions of entries) may be slow, but for typical use (hundreds or thousands of values) it is instant.
Also, the result is a single number. If you need to know the position of that number in the original list, you must do a separate lookup.
Alternatives and Related Calculations
If you need more flexibility, consider these alternatives:
- MAXIFS (in spreadsheet software): Allows multiple criteria and ranges.
- MINIFS: The same logic but for the minimum value.
- Conditional average: Instead of the max, you might want the average of values meeting a condition.
- Percentile with condition: To find a value below which a certain percentage of qualifying data falls.
For a quick manual check, you can sort the list in descending order and pick the first value that meets your condition. That is essentially what this calculator automates.
Frequently Asked Questions
What does “None” mean in the result?
“None” means that no value in your list satisfies the condition you set. For example, if your list is [1, 2, 3] and you ask for values greater than 10, there are none. It does not mean the answer is zero; it means there is no qualifying value.
Can I use this for dates or times?
Yes, if you convert dates or times to numeric values (e.g., Unix timestamps or Excel serial numbers). The calculator will treat them as numbers and find the maximum among those that meet the condition. Just remember to convert back to a readable format after.
How is this different from a regular maximum?
A regular maximum finds the largest number in the entire list, regardless of any condition. This calculator first filters the list based on your criteria, then finds the maximum of that filtered subset. For example, the maximum of [1, 5, 10] is 10, but the maximum of values less than 8 is 5.
What if my list has duplicate values?
Duplicates are handled naturally. If the maximum qualifying value appears multiple times, the result is that value. The calculator does not care how many times it appears; it just returns the number.
Can I use negative numbers?
Yes, negative numbers work fine. For example, if your list is [-5, -2, -10] and you ask for values greater than -7, the filtered list is [-5, -2], and the max is -2.
What if I have an empty list?
If you enter an empty list or a list with no numbers, the calculator will return “None” because there is no value to evaluate. Make sure to enter at least one number.
Related
Formula used
in list | Values | - | 4, 8, 15, 16, 23, 42 | 0 sel crit | Criteria | Greater than=1, Less than=2, Equal to=3, Not equal to=4 in val | Threshold | - | 10 | 0 out max_match | Max if criteria match | - | maxl(list) if (crit == 1 and maxl(list) > val) or (crit == 2 and maxl(list) < val) or (crit == 3 and maxl(list) == val) or (crit == 4 and maxl(list) != val) else "None" | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.