Logical data preparation
1. Logical data preparation
For future analysis, let’s add some new flags and categories to our car dealership dataset. In the Extra Details tab, we will add a flag to identify old cars in our dataset. First, unprotect the sheet by clicking Unprotect Sheet in the Review menu. In column F, we will add the header “Old Cars”. The dealership classifies a car as old if it was manufactured before 2015. Using NOT, we can create a formula for the column with a TRUE outcome when the year is not greater than 2015. This flag in our data allows us to filter the rest of the dataset easily. Now though, we want to create some categories. We will add the “Car Age & Price” header in a new column. Here, we will create two categories, “Old & Expensive” and “Other”. Using the IF function, we can create our two outcomes. First, we will add the price considered the benchmark for expensive cars in a new cell and add a header above it so we know what it is. The value is 10,000 dollars. We can use this cell for the logical test involving the price column. In our IF formula, we want to test the Year and Price columns for our condition so we can add an AND function here. The conditions in the AND function test if the car's age is less than 2015 and the price exceeds 10000. Before moving on with the rest of the formula, let's fix the reference to the benchmark price cell. We can do this by using the dollar sign. If we put a dollar sign before the J and the 5 in our first cell, we can fix the reference cell for that column and that row! If both tests are true, the outcome will be “Old & Expensive”. If the outcome is false, the value will be “Other”. Let’s filter the new column to only see cars classified as “Old & Expensive”. Cool, we have a few cars in this category. But the “Other” category has the majority of records in it. Let’s improve this category data by adding a 3rd outcome - cheap old cars. We can update our existing formula instead of making a new column. We will remove any filters and then go to the first cell of “Car Age & Price”. We want to change the outcome if the condition test is false and add two outcomes at this stage, so we will nest another IF function into the formula. The condition test will need multiple arguments for the new IF function, so we will use AND again. This time though, we will classify cheap old cars, so anything under or equal to 10,000. If these new conditions are met, the outcome will be “Old & Cheap”; otherwise, it will stay as “Other”. Make sure to add any extra brackets. The table format of the data automatically populates the column and we can filter to check for the new category. Now that we have added some new columns, it’s a good stage to add some notes and comments. Let’s clear all filters, and in cell G1, we will add a note explaining the formula by navigating to Review, Notes, and New Note. Next, we’ll add a comment to the benchmark value cell we created to start a conversation on if this is the best value to use. We select the cell, then go to the Review tab, select New Comment, and we will write our question. Time for some practice!2. Let's practice!
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.