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

Source: Internet
Author: User
Tags min

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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.