Get the smallest number larger than a given number and a condition

Author: Oscar Cronquist Article last updated on May 03, 2023

This article demonstrates how to extract the largest number smaller than a given number based on a condition and criteria.

Get the smallest number larger than a given number and a condition
Get the smallest number larger than a given number and criteria
Get Excel *.xlsx file

1. Get the smallest number larger than a given number and a condition

Get the smallest number larger than a given number and a condition

Formula in E6:

=MINIFS(B3:B8,B3:B8,">"&F2, B5:B10, G4)

The MINIFS function calculates the smallest value based on a given set of criteria.

Formula in cell F6:

=MINIFS(C3:C8,C3:C8,">"&F2, B5:B10, G4)

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

min_range - Required. A reference to the numbers.
criteria_range1 - A reference to cells to evaluate based on the criteria.
criteria1 - Criteria in the form of a number, expression, or text.

MINIFS function was introduced in Excel 2016, you can use the following formulas if you don't have access to the function.

2. Get the smallest number larger than a given number in a date range

Get the smallest number larger than a given number and a date range

Formula in cell F10:

=MINIFS(C5:C10,C5:C10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)

2.1 Explaining formula in cell F10

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Step 1 - First condition

The first criteria_range1 is C5:C10, the first criteria1 is ">"&G6.

The ampersand character concatenates the larger than character and the condition specified in cell G6.

C5:C10,">"&G6

The first criteria pair checks if the values in C5:C10 are larger than the value in cell G6.

Get the smallest number larger than a given number and a date range first condition 1

The image above highlights which cells contain a number larger than the condition.

Step 2 - Second condition

The second criteria_range2 is B5:B10, the second criteria2 is ">="&G4.

The ampersand character concatenates the larger than character, the equal sign, and the condition specified in cell G4.

B5:B10,">="&G4

The second criteria pair checks if the dates in B5:B10 are later or equal to the date in cell G4.

Get the smallest number larger than a given number and a date range second condition

The image above shows that all dates are equal or later than the start range date specified in cell G4.

Step 3 - Third condition

The third criteria_range2 is again B5:B10, the third criteria2 is "<="&G5.

The ampersand character concatenates the less than character, the equal sign, and the condition specified in cell G4.

B5:B10,"<="&G5

The second criteria pair checks if the dates in B5:B10 are later or equal to the date in cell G4.

Get the smallest number larger than a given number and a date range third condition

The image above highlights dates equal to or earlier than the start range date specified in cell G4.

Step 4 - Evaluate MINIFS function

The MINIFS function calculates the smallest value based on a given set of criteria.

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MINIFS(C5:C10,C5:C10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)

returns 3.04 in cell F10. When all conditions are met value 3.04 is returned.

Formula in cell G10

MINIFS(D5:D10,D5:D10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)

returns 4.2. Values 4.2 and 5.33 meet all conditions, however, 4.2 is the smallest.

Get Excel *.xlsx file

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

One Response to “Get the smallest number larger than a given number and a condition”

Mel says:

October 23, 2018 at 9:14 am

Hi!

Could I please request a tutorial on how to get multiple results based on multiple criteria from different columns? An example of the problem is below,

Edit to hopefully be clearer
S/N...Sweet.....Sour.....Bitter...Type
1.....Sweet..............Bitter....A
2.............. Sour...............B
3.....Sweet.....Sour...............C
4........................Bitter....A
5..............Sour................B

The criteria for input will be Sweet or Sour or Bitter and Type.

For example, if input is Sweet and A, then the results will be 1, if input is Bitter and A, then the results will be 1 and 4.

I've been stuck at this for days....could you please help?

Thank you so much!!

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

Get the smallest number larger than a given number and a condition

Table of Contents

1. Get the smallest number larger than a given number and a condition

2. Get the smallest number larger than a given number in a date range

2.1 Explaining formula in cell F10

Step 1 - First condition

Step 2 - Second condition

Step 3 - Third condition

Step 4 - Evaluate MINIFS function

Formula in cell G10

Get Excel *.xlsx file

Get the Excel file

Useful links

Functions in this article

Excel categories

One Response to “Get the smallest number larger than a given number and a condition”

Leave a Reply

How to comment