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
1. Find the smallest number in a list that is larger than a given number - [Excel 2016]
Formula in cell D6:
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
Array formula in cell D6:
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.
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 […]
Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]
This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
Functions in this article
More than 1300 Excel formulas
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.