We return with the tricks for Excel (does it really show that I love this application?), and again with a question that is usually quite recurring. Not surprisingly, on more than one occasion I have been asked, both in my personal environment and at work, how this operation can be carried out, apparently so simple, but which requires knowledge of the correct Excel function in order to carry it out. out without a problem.
I mean of course, as you have already seen in the title, how to check in Excel if a value is within a range. Or well, to be more exact, like checking if no number of values are within a range, which can be the same for all or variable for each one. We will see both cases because, although the formula is practically similar, there is a small but important detail that we must take into account in the first case: the absolute references.
And in addition, we are also going to test how to make the value that we want to check if it fits the range be as dynamic as the current date, so that the result varies depending on the moment in which we review the document. All this is, as I have already indicated, tremendously simple. Do not you believe it? Well, you will check it below
variable range
In this first case, we want to check if, for each employee, its current value is between a previously defined minimum and maximum (which can also be changed if we consider it necessary). Thus, we start with a document like this:
As you will see, each employee has a minimum and a maximum, as well as a current value, and a cell on the right in which we will have to enter the formula that checks if the current is between the two. In the case of the first row with data, 2 (the first row contains the headers), we need to check if 149 is between 150 and 375 (yes, seen as an individual calculation it is extremely simple, but remember that we are talking about functions that allow you to immediately check hundreds or thousands of records). So we want D2 to be greater than B2 and less than B3. And for this we are going to use the AND function.
Specifically, the function that we will have to write in cell E2 is the following: «=AND(D2>=B2; D2
Then use Excel’s autofill function to bring the formula to the rest of the cells where you need it (in case you’re new to autofill, just place your mouse pointer in the lower right corner of the cell you just edited and, when it changes its appearance, click and drag, without releasing, until all the cells are selected). This will apply the check to all rows
If you wish, you can now use the conditional formatting functions that we explain in this tip, to be able to visually identify entries that are in and out of range faster
fixed range
We now start from a similar document, but in which the minimum and maximum values of the range are similar for all of them, for example
In this case we will have to use a formula similar to the one in the previous example (changing, of course, the cells of origin of the current value, the minimum and the maximum) and, in addition, we will have to write the dollar symbol, $, to the left of the letter and to the left of the number of both the minimum value cell and the maximum value cell. That is, the formula for this example, with the cells shown in the image, will be the following: «=AND(B2>=$F$2; B2<$F$3)» (again, without the quotes).
What are we doing with this? Tell Excel that these references are absolute, that is, they should not be changed when the formula is copied to other cells. And it is that, if you look, in the previous example, when using the autofill, the formula was updated in each cell to refer to the minimum and maximum values of its row. Now we don’t want that to happen, because the result would be wrong.
With the min and max cells referenced absolutely, however, copying the formula to the rest of the cells will update the reference to the current value, but retain the range bounding cells, thus providing the correct result
Today in a date range
Now we are going to see a case similar to the first example, but with an interesting variation, which is that we are going to change the numbers to dates, and we are going to make the current value of all the rows the same: the current date.
To make it easier to understand, suppose we have a list of employees, with the start and end dates of their vacations, and we want Excel to automatically tell us who is on vacation and who isn’t when we open the document. For this, of course, we could manually enter the current date, but in reality it is not necessary, since Excel has a function that provides us with said value.
So, suppose we start from a document like this
Well, as I have already indicated, we are going to use the AND function again, but also the TODAY() function, which will always return the exact date and time. And, as you probably already guessed, what we are going to modify is the reference to the current value, which instead of being a value from the table, will be the information provided by the function.
Thus, for the distribution of cells shown in the example image, the formula that we will have to enter in D2 is the following: “=Y(TODAY()>=B2; TODAY() But, if we open the same document in a few days, for example on December 25, the result will be different If you like Excel tricks, remember that we also recommend these three that will make your life easier, and that we have also told you how to calculate direct rules of three in inverses, and also how to calculate different types of percentages.
Learn more about Excel: Microsoft
