Sign in or start a free trial to avail of this feature.
8. The Switch Function
Testing many conditions using IF statements can quickly become difficult to read. The Switch function provides an alternative that is much easier to understand.
Lesson Goal (00:22)
The goal of this lesson is to create three new price categories using the SWITCH function: Cheap, Midrange, and Expensive.
Using Switch to Categorize Products (00:33)
We use the SWITCH function to create a new column containing the price range of each product. SWITCH searches a specified column for a list of values we define, and returns a result from a corresponding list of outputs.
The first argument to SWITCH is the column to search, for example the column of product names. Next, we provide a pairs of values from that column and their corresponding outputs. For example, we supply a list of product names, and their price ranges. Finally, we provide a default output, which is the output for any value that is not already classified. For example, our default value is the price profile for any product that has not been classified already.
Visualizing the New Column (02:04)
We use SWITCH to create a price profile for each product. As before, we can then use this new column in visualizations. For example, using the price range as the legend on a bar chart lets use easily see which products fall into each category.
In previous lessons, we've seen how to use nested IF statements to divide our data into several categories.
However, we've also seen how nested IF statements can become difficult to read and potentially inefficient.
In some cases, you may be able to solve this problem using the SWITCH function.
In this lesson, we'll use the SWITCH function to create three new price categories: cheap, mid-range, and expensive.
Since the price ranges are fixed attributes of the product, we'll add them as a column to the product details table. We'll navigate to data view, select the product details table, and then select new column.
The SWITCH function checks a single column for a list of values we define and assigns a corresponding result.
In this case, we'll check the product name against a list of products whose price we know to create the column.
The SWITCH function is very intuitive when you see it in action. As before, I'll paste the formula into the formula bar and quickly explain it.
The expression is the product name column. Meaning that our SWITCH function will search entries in that column.
Below is a paired list of products and their price profiles.
As you can see, we've identified Melacaine, Sycalink, and Zenatene as expensive and Lumesta, Novatane, and Carmalume as mid-range.
Note that the cheap category doesn't have a corresponding product name.
This last argument is the default value.
This value will be assigned to any product that has not already been classified as expensive or mid-range.
The function will search the products details table for the products we've identified and then assign the appropriate value to the price range field.
We'll press enter and see our new price range column. Let's move to the report view and use this field in a chart.
We'll create a stacked bar chart of sales by product as we've done before, but this time, we'll add price range as a legend.
We can see that three products are classified as expensive, three as mid-range, and the rest cheap, exactly as we defined in the SWITCH function.
As you can see, SWITCH functions are particularly useful for creating a categorical field in your data set. If you have a long list of nested IF statements, using a SWITCH function can make your formula far easier to read and understand.
In the next lesson, we'll see how to create a ranking field using the RankX function.