1. Intermediate logical functions in Excel
Hello, and welcome to this screencast. We will review creating formulas with IFS(), CONCAT(), SWITCH(), AVERAGEIF, and AVERAGEIFS.
The first thing we want to do here is to convert our data range to a table. This will make referencing columns easier and autofill our rows with the formulas we type.
We do this by simply clicking on insert, table. Then check my table has headers and click OK. We’ll rename this table to Sales_Data under Table Design.
Let’s say we need to create a column that can categorize our sales into four buckets: small for anything less than 10,000; medium for sales between 10,000 and 100,000; and large for sales over 100,000. An IFS statement is perfect for this job.
Remember that the first true logical test will give us our result. In addition, we need to create a function that will not throw an error since we cannot default to any false value.
Since our largest bucket is for any sales over 100,000, let's start there and use the greater than sign.
Notice that instead of referencing a cell, it referenced the column header with the at symbol.
Now let’s move on to the medium bucket. Anything greater than 100,000 will hit our large bucket first, therefore, we only need to look for anything equal to or greater than 10,000. Finally, anything less than 10,000 will be in our small bucket.
Notice how we strategically ordered our tests in a way that will correctly categorize our data from high to low.. Since we have the entire range of numbers in our evaluation, we don’t have any errors.
CONCAT is a function that ties different data together into one text string, and it can be useful in creating categories. Let’s combine Sales Size, Product and the text “Order” to create an order category. To make this easier to read, we should put spaces in between the different values.
Nice! Now we have a concise category for each row.
Now, let’s look at SWITCH. SWITCH is a powerful function that essentially swaps values that match the ones we specify in our list. This is great for recategorizing items.
Let’s say we want to create a new category, Continent. In this data we have 5 countries in 2 continents: Canada, Mexico and the USA in North America; Germany and France in Europe. So to do this using SWITCH, we first need to reference the cell which value we want to change, then we need to type out our list. Let’s see what happens if we just type out or list for North America.
Notice that Germany and France return Errors. That’s because they weren’t specified on our list. So let’s fix that by adding them and switching them to Europe.
There, that’s much better!
It’s important to reiterate that the text in logical functions is case-sensitive and must be put in quotations.
Now let’s take a look at AVERAGEIF. Let’s say we want to average the sales for each country. The syntax simply needs us to specify the range to be evaluated, and the criteria, which is the country row. Then we select the range to be averaged.
AVERAGEIFS is similar but it allows us to use multiple criteria. Let’s say we want to average the sales from each country in every segment except Small Business.
The syntax is a bit different in that we need to select the average range first, and then we specify our first criteria range and criteria, and then the second range and criteria.
To exclude a value within our criteria, we can simply add the not equal to operator within the quotations.
Nice work! Now this is averaging everything except small business sales. Now you’ve seen it, you’re ready to give it a try!
2. Let's practice!