All You Need to Know About

# Logical Formulas in Excel

Logical Functions in Excel are a very core part of your Excel toolkit. Learn about the different Excel Logical Formulas here so that you know what to use for each problem!

Here are the top things on what you can do with 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:

STEP 1: For numbers, we have a creative use of the Median formula:

## =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
Formula breakdown:
=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.

I explain how you can do this below:

STEP 1: We need to enter the FILTERÂ function in a blank cell:

## =FILTER(

STEP 2:Â The FILTERÂ arguments:

## array

What is the data to be filtered?
Select the cells containing the tax data, do not include the headers:

## include

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.

## [if_empty]

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

What does it do?
Matches multiple values and returns the first value that has a match
Formula breakdown:
=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:

## =SWITCH(

STEP 2:Â The SWITCHÂ arguments:

## expression

What is the value to check?
Select the cell containing the rating that you want to translate to the correct description

## value1, result1

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”

## value2, result2

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”

## value3, result3

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”

## default

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:

Latest Tutorials

## Understanding the IFS Function in Excel with Examples!

September 27, 2023

## How to Use the IF Function in Excel – The Easy Way

September 6, 2023

July 21, 2023

July 14, 2023

July 3, 2023

June 21, 2023

June 16, 2023

June 9, 2023