Diving deeper with logical functions
1. Diving deeper with logical functions
Hello, and welcome back to Data Analysis in Excel. In this chapter we'll focus on creating more complex logical functions and how we can apply these to customer segmentation.2. Customer segmentation
Much like how we sort clothes into different bins when we do laundry, customer segmentation is a strategy where businesses divide their customer base into distinct groups based on shared characteristics and behaviors. Segmentation can be based on various criteria such as demographics, geographic location, purchasing behavior. By identifying and understanding these segments, businesses can tailor their marketing efforts, products, and services to meet the specific needs and preferences of each group, ultimately improving their sales.3. Nesting logical tests using IF
In Excel, we can use logical functions to create different categories of customers based on the sales data we have. Nesting functions is a technique where two or more functions are used in a single formula. By now you should be pretty familiar with the IF function. The syntax is pretty easy to remember: it starts with a logical test, and if its true, it returns a specified value. If its not, then it can return a different default value. A nested IF statement is similar, except that instead of closing out the first statement with a value if false, we add another IF statement. This can be repeated as many times as needed.4. Nesting logical tests using IFS
IFS is a powerful statement that works similarly to a nested IF statement, except that it is much easier to write and read. It evaluates each logical test in order, and returns the value for the first true condition. The major difference in this function is that there is no default value if the formula is false, so the function will return an error if none of the tests are true.5. SWITCHing values
Another useful logical function is SWITCH. SWITCH evaluates an expression, which can be a cell reference or formula, and will return a value if that evaluation matches the given list.6. SWITCHing values
For example, let's say we want to switch the value in a cell, C1. If the value is 0, we want to return FALSE, and if the value is 1 we want to return TRUE. The SWITCH function easily handles this. First we reference the cell C1, then type our list out like this.7. SWITCHing values
SWITCH can also evaluate formulas or expressions. For example, let's say that we want to evaluate the formula 2 minus 1, and we want to SWITCH the output of the expression for the results in the table. Obviously, 2 minus 1 equals 1, so SWITCH will result in GREEN for this expression. While this is a simple example, it illustrates SWITCH's ability to evaluate expressions which could be also be complex.8. Row level evaluations
The logical functions we've covered so far only return results on the row level, which means they evaluate one row and give a value back for that row.9. Aggregate functions
Aggregation functions on the other hand are functions that evaluate the entire column to give an aggregate value. You're already familiar with these as well.10. Aggregate functions
For example, SUM is an aggregate function that will add up all the values in a given column.11. Aggregate logical functions
Aggregate logical functions also perform column level evaluations, but they are more picky because they can use logical tests to select with columns to aggregate.12. Aggregate logical functions
For example, a SUMIF function with the criteria to only evaluate rows in the column with a value equal to or greater than 5 would only add up the 5, 6 and 7, totaling 18.13. Syntax for COUNTIF and COUNTIFS
There are many aggregate logical functions, all which follow a similar syntax. COUNTIF and COUNTIFS are popular aggregate logical functions, so let's review them as an example. The COUNTIF function will calculate and display the count of cells that meet the specified criteria. The syntax is very simple too, simply specify the range of cells to evaluate, and the criteria that needs to match. COUNTIFS is similar to the COUNTIF function, only that more ranges and criteria can be specified. Each criteria must be true in order for COUNTIFS to count the values.14. Let's practice!
Don't worry if that felt like a lot of information. We'll review this some more in the screen cast, and we have plenty of time to practice. But first: Pop Quiz!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.