BYU Student Author: @Parker_Sherwood
Reviewers: @Hyrum, @Mike_Paulsin
Estimated Time to Solve: 25 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
The year is 2012 and you are in the market for a new car! You know you want a reasonably fuel efficient car within your price range. Specifically, you’re looking for a car with at least 28 MPG and under $17,000. You downloaded some car sales data for 2008-2012, and it’s time to do some analysis. Using Excel and the power of filters, IF(), and IFS(), you’ll be able to make an informed decision. All of your work will be in the “Car_sales” worksheet. The table below describes the columns in the “Car_sales” worksheet.
Instructions
Complete the following:
- In the “Fuel Efficient (T/F)” column, use a logical function (<, >, <=, >=) to identify whether a car gets at least 28 MPG. Copy the formula down for the whole column. The formula should return “TRUE” or “FALSE”
- In the “Fuel Efficient (Desc)” column, use the IF() function to identify whether a car gets at least 28 MPG. If a car gets less than 28 MPG, it should be labeled as “Inefficient”. Otherwise, the car should be labeled “Efficient”. Copy the formula down for the whole column.
- In the “Is it affordable?” column, use the IF() function to identify whether a car is within your budget of $17,000. If it is, return the word “Affordable”. Otherwise, create a label to show that the car isn’t affordable. To keep this part simple, do not check for blank cells. Copy the formula down for the whole column.
- In the “Fuel Efficient and within budget” column, use the IF() function to test if a car is both “Efficient” and “Affordable”. If a car is both efficient and affordable, it is a great choice. If it’s not both efficient and affordable, you’ll need to think about it. Your function should return something along those lines.
- As you’ve done a little more research, you’ve realized you want to lower your budget to $16,000. In the “Price range” column, you’ll create categories for the prices of cars that are more descriptive than “Affordable” or not. Specifically, use the IFS() formula and the following chart to create your function. Copy the formula down for the whole column.
- In the “How Fuel Efficient?” column, use multiple IF() functions to create more specific categories for fuel efficiency. Use the following chart as a guide.
- In the “Further analysis” column, use the IF() or IFS() funciton to conduct some further analysis of your own! What would you want to consider if you were buying a car?
- Add filters to the data. Filter the “Fuel Efficient and within budget” column to find the “great choice” cars. Sort the “Price range” column so that the most affordable of the “great choice” cars are at the top of the list. Manually type the make and model of your favorite “great choice” car in cell Z1.
Data Files
Suggestions and Hints
- Hint for #1: This formula should not use a function (Don’t use IF(), IFS(), etc.)
- Hint for #4: This can be done by using the AND() function within the IF() function, or using multiple IF() functions
- Hint for #5: While working with creating categories, you have to start at one extreme (>20 or <10) for this to work because the formula can’t handle double inequalites (testing if a value is between two values)
- Hint for #6: The solution file uses three IF() functions. It is helpful to “nest” the IF() functions in the “value_if_false” condition. It may also help to think about the conditions in sentence form: if the MPG is less than 20, then return “Poor”; otherwise, if MPG is less than 25, then return “Fair”; etc.
- Ideas for #7:
- You could use “Sales_in_thousands” as a measure of reliability (cars with relatively low sales may not have a good reputation)
- You could use “__year_resale_value” and create categories similar to what was done for price and MPG earlier
- You could use “Horsepower” and create categories similar to what was done for price and MPG earlier
- Maybe you have really long legs and want to make sure you will have enough room. You could use length to conduct further analysis
Solution