The minimum value greater than n is less than the maximum value of M

In this case, we're going to find out, in a set of data, the minimum value greater than 3, and the maximum value less than 9. First look at the table below.

A1:b3 This range is the data range. Next we start using the formula to find the minimum value greater than 3. Of course, with our own observation, we can directly find the minimum value of 3 is 4, because the amount of data is small, can be seen at a glance. However, we have to use formulas to automatically calculate and produce results.

method is not difficult, the use of the Min and if functions to coordinate with each other can solve this problem. But please note that you have to use an array formula, first look at the graph. In cell B5, we first enter the formula:

=min (if (A1:B3>3,A1:B3)) then, in the state of the input formula, and then press the key combination: Ctrl+shift+enter Note that the formula entered automatically adds a pair of curly braces to the {=min of the above image (if (a1:b3>3,a1:b3 )}. Note that curly braces are automatically added by pressing the ctrl+shift+enter, not directly through the keyboard. Many local articles, only the formula, did not mention this pair of curly braces input, resulting in the calculation can not get the correct results, but got a #value! invalid value.

In the same way, if you want to find the maximum value less than 9, look at the figure below.

The formula we use is:

=max (IF (A1:B3<9,A1:B3))

Again, you have to press Ctrl+shift+enter to make it an array formula to get the correct result.

Finally, let's summarize the general formula for a minimum value greater than a number and a maximum value that is less than a number:

=min (IF (data range >n, data range))

=max (IF (data range

The difficulty with this example is the use of the combination of the function and the combined key of the array formula.