< Go Back

Value is between two numbers

HOW TO DETECT IF A VALUE IN A CELL IS BETWEEN A SET OF VALUES – USING THE AND, MIN, AND MAX FUNCTIONS

To determine whether a number is between a set of values, the “AND” function and your chosen logical tests can be applied.

Let’s assume that you have a bulky data that you need to analyze statistically. Part of this analysis involves that you determine whether a particular value is present in the different strata of data you have. You can use the “AND” function for this conveniently.

With the “AND” function, one can do the following:

  • Financial forecasting.
  • It is also useful for returning a particular result based on a set of conditions within a set of data.

Example 1- THE SIMPLE “AND” function

For the set of data in figure 1, we have used a basic formula. The result of yours mustn’t be ACCEPT or REJECT, it could be TRUE or FALSE.

BASIC FORMULA =IF(AND(E6<=H6, F6<=H6, G6<=H6),"ACCEPT", "REJECT")

How did we arrive at the REJECT result we have in Cell I6?

Step 1

  • Type your data or values on the spreadsheet, including the value of interest as shown in Cells H6, H7, and H8. Also, prepare a Cell where your results will be displayed and name it. I named mine BETWEEN.

Step 2

  • Place the cursor on Cell I6. Click on fx as shown in figure 2 below. You should have this below.

STEP 3

  • Click on OK on the popup in figure 2.
  • You will see the figure below.

  • Take your cursor and place it between the brackets on the fx section. That is = IF(PLACE THE CURSOR HERE)

STEP 4

  • Type this in the order as shown              AND (E6<=H6, F6<=H6, G6<=H6),ACCEPT", “REJECT”. Do this religiously
  • When you are done, it should be like Figure 4 below.

  • Click on OK. If you have done it correctly, you will have what is in figure 1 above.

 

What this basic formula (=IF(AND(E6<=H6, F6<=H6, G6<=H6),"ACCEPT", "REJECT")) means is this. We intend to check if the value (6) in Cell H6 is less than or equal to the values in Cells E6, F6, and G6. Because Cell G6 value doesn’t satisfy the condition, the result is returned as REJECTED.

Example 2- THE AND, MIN, and MAX function

Using the same data we have, let’s try something new. Let us use a set of conditions that involve maximum and minimum values.

The basic formula =AND(H6>MIN(E6,F6),H6<MAX(G6,E6), H6>MAX(G6,E6))

After typing the data and sticking with the aforementioned procedures, type the following into the fx bar =AND(H6>MIN(E6,F6),H6<MAX(G6,E6), H6>MAX(G6,E6)). Do not forget the equal to sign.

H6>MIN(E6,F6)- This logic means that if the value in H6 is greater than the minimum value in either cells (E6 and F6), the result should be reported as TRUE.

H6<MAX(G6,E6)- This logic means that if the value in H6 is lesser than the maximum value in either cells (G6 and E6), the result should be reported as TRUE.

H6>MAX(G6,E6)- This logic means that if the value in H6 is greater than the maximum value in either cells (G6 and E6), the result should be reported as TRUE.

As we now know, it is this particular logic that caused our result to be returned as FALSE.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar