Find the smallest value in a list that is larger than a number
Problem: Find the smallest value in a list but it has to be bigger than 45?
Answer:
- MINIFS function [Excel 2016]
- MIN + IF functions [Excel 2013 and previous versions]
MINIFS function [Excel 2016]
Formula in cell D6:
MIN + IF functions [Array Formula]
Array formula in cell D6:
To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
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.
Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date [โฆ]
SMALL function with duplicates
The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. [โฆ]
How to use the MINIFS function
The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The [โฆ]
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.