1. The data in theMPG Dataworksheet were downloaded from the Web sitehttps://www.fueleconomy.gov/feg/download.shtml. The greenhouse gas scores range from 0 to 10, where 10 is best. The vehicles with the best scores on both air pollution and greenhouse gas receive theSmartWaydesignation. Use custom filtering to find cars in this data set that meet your criteria and then copy the smaller data set to another worksheet where you can work with it further.
2. Go to theMPG Dataworksheet, and display the AutoFilter arrows for the data set.
3. Create a custom number filter to show only rows where the greenhouse gas score is greater than8.
4. Create another custom number filter to show only rows where the highway MPG is greater than35.
5. Create a custom text filter to show only rows where theTranscolumn contains the wordauto.
6. Create a custom text filter to show only rows where the value in theFuelcolumn equalsElectricityorGasoline.
7. At this point, the list of more than 2,000 cars should be filtered down to just 48 records.
8. Copy the filtered data to cellA1in theMy Car Dataworksheet. Be sure to include the heading row but do not include theAll Modelstitle. Paste keeping the source columns widths.
9. On theMy Car Dataworksheet, use Advanced Filter to copy a subset of data to another location on this worksheet.
10. Copy cellsA1:P1and paste them below the data set in row52.
11. In cellA51, type:Criteria
12. Apply theAccent 4cell style to cellA51.
13. Set up the criteria in the row(s) below row 52. Find cars where the value in theFuelcolumn isGasolineandthe value in theHwy MPGcolumn isgreater than 35.
14. In cellA55, type:Filtered Data
15. Apply theAccent 4cell style to cellA55.
16. Autofit columnA.
17. Use Advanced Filter to find the rows that meet the criteria and copy the filtered data to another location beginning in cellA56.
18. Sixteen rows should have been copied from the main data set to cellsA57:P72.
19. Format the filtered data set (cells A56:P72) as a table. UseTable Style Light 1(the first table style in the gallery). Be carefulnotto include row 55 in the table data range.
20. Insert a new column to the left of theGreenhouse Gas Scorecolumn.Hint: Be sure to add a column to the worksheet, not just the table.
21. Copy the values from cellsM57:M72toN57:N72.
22. Type this column label in cellN56:Cmb MPG Icon
23. Apply a new custom conditional formatting rule to cellsN57:N72to show the4 Ratingsicon set. Show the icon only.
24. Continue working with the filtered data that has the conditional formatting applied. Sort the table first alphabetically by the values in theModelcolumn and then by icon in theCmb MPG Iconcolumn so theSignal Meter With Four Filled Barsicon appears at the top.
25. Go back to the main data set at the top of theMy Car Dataworksheet and add subtotals for each change inModelto calculate theaveragefor the following:Air Pollution Score,City MPG,Hwy MPG, andCmb MPG.
26. Collapse the data to show just the total rows.
27. Go to theMPG PivotTableworksheet. This sheet includes a PivotTable created from the data on theMPG Dataworksheet.
28. Refresh the PivotTable data.
29. Apply thePivot Style Medium 1Quick Style to the PivotTable (the first style in theMediumsection).
30. Display a slicer for theSmartWayfield and show only data where theSmartWayvalue isElite.
31. To pay for the new car, you've decided to start a bakery business out of your home kitchen. You have limited capital and capacity, so you need to make wise decisions about which products to bake each day. Go to theBakery Businessworksheet. Cell B1 uses a SUMPRODUCT formula to calculate profit. Carefully review the assumptions and formulas in this worksheet before entering the following Solver parameters:
32. Find the maximum possible profit (cellB1) by changing the values in cellsC7:C10.
33. The working hours required (B3) must be less than or equal to the number of working hours in the day (D3).
34. The total cost of ingredients (B4) must be less than or equal to the available capital (D4).
35. For each item in the rangeC7:C10, the number of items baked cannot exceed the maximum capacity available per day as defined in cellsF7:F10. This restriction requires four separate constraints in theSolver Parametersdialog.
36. The items in the variable cell rangeC7:C10must be whole numbers.
37. There should be a total of seven constraints.
38. Run Solver and accept the Solver solution.