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.
=IF(AND(E6<=H6, F6<=H6, G6<=H6),"ACCEPT", "REJECT")
How did we arrive at the REJECT result we have in Cell I6?
- 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.
- Place the cursor on Cell I6. Click on fx as shown in figure 2 below. You should have this below.
- 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)
- 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
After typing the data and sticking with the aforementioned procedures, type the following into the fx bar
. 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.