Sign in or start a free trial to avail of this feature.
7. Refining Logical Functions
We continue looking at conditional functions, seeing how you can build more complex conditions using the AND and OR functions.
AND and OR functions
- The AND and OR functions allow you to test two conditions at the same time
- When testing conditions you place them inside an IF function
- Using OR will return True if either condition is True
- Using AND will return True if both conditions are True
- Alt H, D2 – Enter data view
- Shift + Enter – Create new line (when creating formulas)
In the previous lesson, we used an if statement to create a column categorizing our products as small, medium, or large. In this lesson, we'll make the categorization more complex by adding further conditions to our if statement.
Looking at our graph, it's clear that Melacaine has the most sales by some distance.
Therefore, we might want to give it a special category of its own called very large.
To do this we'll go to power pivot, and select the product size column in the product details table.
We're going to modify the formula used to create this column and put a new condition at the start.
We'll enter a new if statement so that if the product name is Melacaine the product size will be very large.
Otherwise, the existing if statement will be executed.
We'll close out the formula, press Enter, navigate to Excel and look at our pivot chart to see that Melacaine now has its own category of very large, at the moment a product's category is based on the sales field only.
Let's refine this categorization by also considering the number of customers a product has.
If a product has large sales numbers that are concentrated among a smaller number of customers, then it shouldn't be categorized as large.
We'll start making this change by creating a new column to count the number of customers for each product.
We'll double click Add Column and call it customer count.
We'll then enter a formula for the column, we'll use a calculate function and then distinct count of customer IDs.
This function counts the number of unique customer IDs in the data set, as before we'll use related table so the distinct count function counts customers for the individual product only. We'll then press Enter to create our column.
We now have a count of the number of customers for each product which we can use as a part of our product category column.
We'll refine the product size column so that the output depends on the customer count and the product sale columns.
When we have two conditions such as this, we can use the and function or the or function.
In this case a product will be categorized as small if it has sales below 20,000 or if it has fewer than 70 customers. A product with sales below 50,000 and fewer than 100 customers will be medium.
We'll add these conditions using an or statement.
We'll select the product size column and move each if statement to a separate line using the keyboard shortcut Shift + Enter.
This will make the formula easier to read.
We'll now add our or function within the second if statement.
Or takes two logical tests and returns true if one of those tests is true We want to include the existing test of sales, and add a test of customer count being less than 70.
Now any product with less than 20,000 in sales or less than 70 customers will be small.
We'll do something similar for the medium test.
We'll add an or statement inside the if statement and create a condition of customer count, less than 100.
We'll close the or statement and press Enter to accept the new formula.
If we navigate back to our pivot chart, we can see it now reflects the new category definitions.
As you can see, logical functions can be quite powerful and the logic behind them is usually not difficult to understand, however it's also clear that long if statements can be slightly difficult to read, interpret, and format correctly. Especially when you have multiple conditions to check.
In the next lesson we'll learn how to deal with checking multiple conditions by using the switch function.