Logical Formulas in Excel
BETWEEN Formula in Excel
There is no explicit Between formula in Excel, however we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10?
We have three possible scenarios: numbers, dates, and text.
I explain how you can do this in the Between Formula Example below:
=IF(C7=MEDIAN(A7:C7), “Yes”, “No”)
In our first example above, the range is 20-60, upon checking the value 50, it is in between this range.
The median formula will return the value in the middle of these 3 values when arranged in increasing order: 20, 50, 60. The median value is 50. Since it matches the value we are evaluating, then the answer we get is a Yes, this value (50) is in between the range.
STEP 2: For dates, we have the same application of the median formula. Because Excel treats dates as numbers too:
=IF(C10=MEDIAN(A10:C10), “Yes”, “No”)
In our first example above, the range is May 1 – July 1, upon checking the date June 1, it is in between this range.
The median formula will return the value in the middle of these 3 dates when arranged in increasing order: May 1, June 1, July 1. The median value is June 1. Since it matches the value we are evaluating, then the answer we get is a Yes, this value (June 1) is in between the range.
STEP 3: For text, we are checking if the value is alphabetically in the middle. We will be using the and formula:
=IF(AND(C12>=A12, C12<=B12, "Yes", "No")
Interestingly enough, you can compare texts using the >= and <= operators. Excel is able to compare them which goes alphabetically first or last.
In our first example above, the range is Cat – Dog, upon checking the text Cow, it is in between this range. As when arranged alphabetically, it would be: Cat, Cow, Dog.
The And formula checks if Cow >= Cat, and Cow <= Dog. You will see that both of these are true, as Cow is alphabetically later than Cat, while Cow is alphabetically ahead of Dog. Which is why we get a Yes result.
FILTER Formula in Excel
What does it do?
Filters a table array based on the filtering condition given
=FILTER(array, include, [if_empty])
What it means:
=FILTER(data to be filtered, the filtering condition, [value to display if nothing gets matched])
Did you know that you can now filter your table data with an Excel Formula? Yes you can! It is definitely possible now with Excel’s FILTER Formula. It is a new formula introduced in Office 365 released in 2018!
We have a tax table that we want to dynamically filter with a given rate.
STEP 1: We need to enter the FILTER function in a blank cell:
STEP 2: The FILTER arguments:
What is the data to be filtered?
Select the cells containing the tax data, do not include the headers:
What is your filtering condition?
We want to filter the tax rate that is greater than the specified rate. Type in the condition as the tax rate column > the specific tax rate.
What is the value to display in case nothing gets matched?
Just place an empty string to be displayed if nothing gets matched.
=FILTER(C9:D14, D9:D14>G8, “”)
Try it out now with different values and see it get filtered magically!
SWITCH Formula in Excel
Matches multiple values and returns the first value that has a match
=SWITCH(expression, value1, result1, [value2 / default, result2], …)
What it means:
=SWITCH(value to check, value to match against, result to return, [succeeding values to match or the default value if nothing gets matched], …)
If you have multiple values to check, we can use Excel’s SWITCH Formula! It allows us to specify multiple values to check, then the SWITCH Formula will look for the first value that gets matched!
Let us try it out on a simple ratings table (e.g. 1 = Bad, 2 = Average, 3 = Great), then we will create a SWITCH Formula that will simulate the exact same logic of the table!
I explain how you can do this below:
STEP 1: We need to enter the SWITCH function in a blank cell:
STEP 2: The SWITCH arguments:
What is the value to check?
Select the cell containing the rating that you want to translate to the correct description
What is the first lookup value and value to return if it is matched?
Let us start from the first value of the rating table. If the value is 1, then the description is “Bad”
=SWITCH(G8, 1, “Bad”
What is the second lookup value and value to return if it is matched?
Let us start from the second value of the rating table. If the value is 2, then the description is “Average”
=SWITCH(G8, 1, “Bad”, 2, “Average”,
What is the third lookup value and value to return if it is matched?
Let us start from the third value of the rating table. If the value is 3, then the description is “Great”
=SWITCH(G8, 1, “Bad”, 2, “Average”, 3, “Great”,
What is the default value to return if nothing gets matched?
We want to show the value “Unknown”, if an unknown rating is specified.
=SWITCH(G8, 1, “Bad”, 2, “Average”, 3, “Great”, “Unknown”)
You now have your correct rating description!
Let us try an unknown rating (40) and see the resulting description: