This guide will explain how to use BETWEEN
formula in Excel using three easy and simple methods.
Excel is a popular tool containing hundreds of built-in formulas to make calculations and other tasks easy. However, Excel does not have any built-in BETWEEN
formulas.
And this is not something to fret about. Since Excel already has several built-in formulas, we can use a few of them to create our own BETWEEN
formula easily.
The BETWEEN
formula is used to compare values to other ranges of values to determine whether the value is between them. So we want to determine if a value is between two other values in a given data set.
Moreover, the value does not have to be a number. But, it can also identify if a date or text is between two given values in our data set.
Let’s take a sample scenario wherein we need to use a BETWEEN
formula in Excel.
Suppose you are making a sales report. And you want to compare the 2022 sales to the 2020 and 2021 sales. Furthermore, you want to determine if the 2022 sales are between the total sales for 2021 and 2020.
Since Excel does not have an explicit BETWEEN
formula, you opted to create one using the AND
function, MIN
function, and MAX
function.
Great! Now let’s move on and learn how to use a BETWEEN
formula in Excel using three easy and simple methods.
How to Use BETWEEN Formula in Excel for Numbers
Firstly, let’s discuss how to use a BETWEEN
formula in Excel specifically for numbers. Moreover, one of the most common uses of the BETWEEN
formula is to compare a number between two given numbers to determine whether the value is between the two numbers.
For instance, we have a list of numerical values arranged by the lower limit and upper limit. Then, we have the value we want to determine is between the lower and upper limits in another column.
Luckily, there are many combinations of formulas we can use to create a BETWEEN
formula for numbers in Excel. In this case, we will use a combination of the IF
function and MEDIAN
function to determine whether the values are between the lower and upper limits.
Another combination we can utilize is the combination of the IF
function and the AND
function. And both combinations will return TRUE or we can specify what value it will return if the value is between the two given limits or FALSE if it is not between the two limits.
To use this method, follow the step-by-step process below:
1. Firstly, we will input the result in a new column. In this case, we will first try the combination of the IF
function and the MEDIAN
function. So type in the formula “=IF(D3=MEDIAN(B3:D3), “TRUE”, “FALSE”)”. Then, press the Enter key to return the result.
2. Secondly, we can drag down the fill handle to apply the formula to the other cells.
3. And tada! We have used the BETWEEN
formula in Excel.
4. Furthermore, let’s try the IF
function and AND
function combination. So we will input the formula “=IF(AND(D3>B3,D3<C3),”TRUE”,”FALSE”)”. Then, press the Enter key to return the result.
5. Next, we can drag down to copy the formula to the other cells.
6. And tada! We have used another BETWEEN
formula in Excel.
How to Use BETWEEN Formula in Excel for Dates
Next, we will discuss how to create a BETWEEN
formula in Excel to use for dates. For example, we want to determine if a specific date is between two given dates. Since Excel sees dates as numbers, the formula will be the same. When we compare dates, we are essentially comparing numbers still.
For example, we have a data set containing the start and end dates. Then, we have a column of specific dates to check if it is between the start and end dates.
Since dates are exactly the same as numbers in Excel, we can use the same combinations of formulas. Firstly, we can use a combination of the IF
function and the MEDIAN
function.
Next, we can use a nested IF
function to create another BETWEEN
formula. In this case, this formula would check if the specific date is before the start date or after the end date. So it will either return “before start date” or “after start date.”
To use this method, follow the step-by-step process below:
1. Firstly, we will use the IF
function and MEDIAN
function combination. So type in the formula “=IF(D8=MEDIAN(B8:D8),”TRUE”,”FALSE”)”. Then, press the Enter key to return the result.
2. Next, we can drag down the fill handle to copy the formula to the other cells.
3. And tada! We have determined whether the date was between the start date and the end date.
4. After, let’s try using the nested IF
formula. So input the formula “=IF(D8<B8,”BEFORE START DATE”,IF(D8>C8,”AFTER START DATE”,”IN BETWEEN”))”. Lastly, press the Enter key to return the result.
5. Then, we can drag down the fill handle to apply the formula to the other cells.
6. And tada! We have successfully used the nested IF
formula as a BETWEEN
formula.
How to Use BETWEEN Formula in Excel for Text
Lastly, we can also create a BETWEEN
formula in Excel specifically to deal with text. And this is something not everyone knows or is common to do. But, we can determine whether a text string is between two given text strings.
For example, we have a data set containing two columns of text. Then, we have another column containing the text we want to check is between the first two text strings.
How does this work? The formula will determine the text strings alphabetically and return a result based on that. Similarly, we can use the same combination of formulas to perform this task.
In this case, we will use a nested IF
formula to check whether a text string is between two given text strings.
Let’s learn how to use this method by following the process below.
1. Firstly, we will input the formula “=IF(AND(D13>B13,D13<C13),”TRUE”,”FALSE”)”. Then, press the Enter key to return the result.
2. Next, we can drag down the fill handle to copy the formula and apply it to the other cells.
3. And tada! We have successfully used a BETWEEN
formula in Excel for text strings.
You can make your own copy of the spreadsheet above using the link attached below.
And that’s pretty much it! We have explained how to use BETWEEN
formula in Excel using three easy and simple methods. Specifically, we discussed how to create a BETWEEN
formula in Excel for numbers, dates, and even text strings.
Are you interested in learning more about what Excel can do? You can now use the MEDIAN
function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.