Find the smallest number in a list that is larger than a given number
This article demonstrates formulas that lets you extract the smallest number larger than a given number.
The example above specifies the given number in cell D3, in this case, 45. The data is in cell range B3:B16, the formula in cell D6 calculates the smallest value but larger than 45.
Table of Contents
- Find the smallest number that is larger than a given number - [Excel 2016]
- Find the smallest number that is larger than a given number - earlier Excel versions
- Find the largest number that is smaller than a given number - [Excel 2016]
- Find the largest number that is smaller than a given number - earlier Excel versions
- How to find the k-th smallest number that is larger than a given number?
- How to find the k-th largest number that is smaller than a given number?
- Get Excel file
1. Find the smallest number in a list that is larger than a given number - [Excel 2016]
This example shows an Excel 2016 function that extracts the smallest number larger than a given condition. The condition is specified in cell D3 and the source data is deployed in cell range B3:B16.
Cell D3 contains number 45, the formula in cell D6 returns 50 which is the smallest number larger than 45 in cell range B3:B16.
Formula in cell D6:
Section 2 below describes a formula that works in all Excel versions.
1.1 Explaining formula
Step 1 - Populate arguments
The MINIFS function calculates the smallest value based on a given set of criteria.
Function syntax: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
becomes
MINIFS(B3:B16,B3:B16,">"&D3)
The greater than sign > is a logical operator that lets you check if a number is larger than another number.
The ampersand character & lets you concatenate values in an Excel formula. You also have the option to enter the greater than sign in cell D3 combined with the given number, like this: >45. If you do remove the less than sign and the ampersand and reference only cell D3 in the MINIFS function.
Step 2 - Evaluate MINIFS function
MINIFS(B3:B16,B3:B16,">"&D3)
becomes
MINIFS({86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},">"&45)
and returns 50.
2. Find the smallest number in a list that is larger than a number - earlier Excel versions
This example demonstrates a formula that works in all Excel versions, it extracts the smallest number larger than a given condition.
The condition is specified in cell D3 and the source data is in cell range B3:B16. Cell D3 contains number 45, the formula in cell D6 returns 50 which is the smallest number larger than 45 in cell range B3:B16.
Array formula in cell D6:
This formula is an array formula, it is required to enter this formula given the instructions in section 2.1 below if you work in an Excel version earlier than Excel 365.
2.1 How to enter an array formula
- Type the formula in cell B3
- Then press and hold CTRL and SHIFT simultaneously,
- Now press Enter once.
- Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.
Don't enter the curly brackets yourself, they appear automatically.
Explaining array formula
Step 1 - Filter values in cell range larger than the condition in cell D3
The IF function allows you to create a logical expression that evaluates to TRUE or FALSE. You can then choose what will happen to a value that returns TRUE and also FALSE.
IF(logical_test, [value_if_true], [value_if_false])
If you use a cell range instead of a cell reference to a single cell you evaluate all the values in the cell range. The larger than sign lets you check if the values in B3:B16 are larger than the value in D3.
IF(B3:B16>D3,B3:B16)
becomes
IF({86;51;68;50;38;66;23;59;79;72;19;99;76;88}>45,B3:B16)
becomes
IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},B3:B16)
becomes
IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88})
and returns the following array:
{86;51;68;50;FALSE;66;FALSE;59;79;72;FALSE;99;76;88}
Step 2 - Return the smallest value in the array
The MIN function ignores boolean values which is useful in this case.
MIN(IF(B3:B16>D3,B3:B16))
becomes
MIN({86;51;68;50;FALSE;66;FALSE;59;79;72;FALSE;99;76;88})
and returns 50 in cell D6.
3. Find the largest number in a list that is smaller than a given number - [Excel 2016]
The image above shows a small formula in cell D6 that calculates the largest number in cell range B3:B16 smaller than the condition specified in cell D3.
The formula in cell D6 returns 38, it is smaller than 45 but the largest of the numbers smaller than 45.
Excel 2016 formula in cell D6:
Section 4 describes a formula for earlier Excel versions than Excel 2016.
3.1 Explaining formula
Step 1 - Populate arguments
The MAXIFS function calculates the highest value based on a condition or criteria.
Function syntax: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MIAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
becomes
MAXIFS(B3:B16,B3:B16,"<"&D3)
The less than sign < is a logical operator that lets you check if a number is smaller than another number. The ampersand character & lets you concatenate values in an Excel formula.
Step 2 - Evaluate MINIFS function
MINIFS(B3:B16,B3:B16,">"&D3)
becomes
MINIFS({86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},"<"&45)
and returns 38.
4. Find the largest number that is smaller than a given number - earlier Excel versions
Array formula in cell D6:
4.1 Explaining the formula
Step 1 - Filter values in cell range larger than the condition in cell D3
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
Function syntax: IF(logical_test, [value_if_true], [value_if_false])
IF(B3:B16>D3,B3:B16)
becomes
IF({86;51;68;50;38;66;23;59;79;72;19;99;76;88}<45,B3:B16)
becomes
IF({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},B3:B16)
becomes
IF({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88})
and returns the following array:
{FALSE;FALSE;FALSE;FALSE;38;FALSE;23;FALSE;FALSE;FALSE;19;FALSE;FALSE;FALSE}
Step 2 - Return the smallest value in the array
The MAX function calculate the largest number in a cell range.
Function syntax: MAX(number1, [number2], ...)
MAX(IF(B3:B16>D3,B3:B16))
becomes
MAX({FALSE;FALSE;FALSE;FALSE;38;FALSE;23;FALSE;FALSE;FALSE;19;FALSE;FALSE;FALSE})
and returns 38 in cell D6.
5. How to find the k-th smallest number that is larger than a given number?
Array formula in cell D6:
5.1 Explaining formula
Step 1 - Check if numbers are larger than the condition
The larger than character lets you preform logic in an Excel formula. The result is a boolean value TRUE or FALSE.
B3:B16>D3
becomes
{86;51;68;50;38;66;23;59;79;72;19;99;76;88}>45
and returns
{TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}.
Step 2 - Extract numbers larger than the condition in cell D3
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
Function syntax: IF(logical_test, [value_if_true], [value_if_false])
IF(B3:B16>D3,B3:B16,"")
becomes
IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},{86;51;68;50;38;66;23;59;79;72;19;99;76;88},"")
and returns
{86; 51; 68; 50; ""; 66; ""; 59; 79; 72; ""; 99; 76; 88}
Step 3 - Extract the k-th smallest number
The SMALL function returns the k-th smallest value from a group of numbers.
Function syntax: SMALL(array, k)
SMALL(IF(B3:B16>D3,B3:B16,""),D6)
becomes
SMALL({86; 51; 68; 50; ""; 66; ""; 59; 79; 72; ""; 99; 76; 88},D6)
becomes
SMALL({86; 51; 68; 50; ""; 66; ""; 59; 79; 72; ""; 99; 76; 88},2)
and returns 51.
50 is the smallest number in the array, however, we are looking for the second smallest number in the array.
6. How to find the k-th largest number that is smaller than a given number?
Array formula in cell D6:
6.1 Explaining formula
Step 1 - check if numbers are smaller than the condition
B3:B16<D3
becomes
{86;51;68;50;38;66;23;59;79;72;19;99;76;88}<45
and returns
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.
Step 2 - extract numbers smaller than the condition in cell D3
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
Function syntax: IF(logical_test, [value_if_true], [value_if_false])
IF(B3:B16<D3,B3:B16,"")
becomes
IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},B3:B16,"")
becomes
IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88}, "")
and returns
{""; ""; ""; ""; 38; ""; 23; ""; ""; ""; 19; ""; ""; ""}.
Step 3 - extract the k-th largest number
The LARGE function calculates the k-th largest value from an array of numbers.
Function syntax: LARGE(array, k)
LARGE(IF(B3:B16<D3,B3:B16,""),D6)
becomes
LARGE({""; ""; ""; ""; 38; ""; 23; ""; ""; ""; 19; ""; ""; ""},D6)
becomes
LARGE({""; ""; ""; ""; 38; ""; 23; ""; ""; ""; 19; ""; ""; ""},2)
and returns 23.
38 is the largest number in the array, however, we need to extract the second largest number specified in cell D6.
Useful links
How to find smallest positive value (greater than 0) in Excel?
Calculate the smallest or largest number in a range
Minimum Value in a Range if Greater Than "X"
Get the smallest number larger than a given number and a condition
Get the smallest number larger than a given number and criteria
If category
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]
Small category
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]
Excel categories
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.