Car MPG Summary

Car MPG Summary
Start with the Excel workbook (spreadsheet) Car MPG Summary – start.xlsx.
In the starting file, you will see information related to various cars with a 2013 model year. The column labels for this dataset are located in cells A1:M1. For example, cell B1 is the manufacturer (Mfr) name (i.e. Honda, Ford, etc.), G1 transmission (Trans) description (Desc) (i.e. manual, automatic), H1 drive description (i.e. rear 2-wheel drive, all-wheel drive, etc.), and M1 highway miles per gallon (MPG) (i.e. 21, 28, 32, etc.). The goal of this problem is to demonstrate that you can utilize sorting features of Excel properly. There are two key features of this exercise. The first being you can sort the raw data appearing in A1:M1160), and the second is that you can sort based on formula values that you will be asked to write in various cell locations in columns O through Y. Complete the following tasks:
1. For the data in cells A1:M1160, sort the data based on the following conditions based on highest to lowest priority. In other words, the highest priority will be the first sorting level and the lowest priority will be the last sorting level. Sort the data based on the following conditions:
a. Highest Priority: Trans Desc in ascending order
b. Next Priority: Carline Class Desc in ascending order
c. Lowest Priority: Highway MGP in descending order
2. For the cells in P3:P30, write a formula in P3 that can be filled down to P30 that calculates the average highway mpg value based on the manufacturer name that appears in cells O3:O30.
a. Once the formula has been written and filled, sort the values in O3:P30 based on descending order of average highway MPG.
b. In addition, use 3 Arrows (Colored) Directional Icon Set conditional formatting for the range.
3. For the cells in S3:S13, write a formula in S3 that can be filled down to S13 that calculates the average highway mpg value based on the manufacturer name that appears in cells R3:R13 and based on a Large Cars classification in S1 for the Carline Class Description found in column I.
a. Once the formula has been written and filled, sort the values in R3:S13 based on descending order of average highway MPG. You may notice that this car manufacturer list appearing in R3:R13 is limited. This is because not all car manufacturers manufacture Large Cars.
b. In addition, use 3 Arrows (Colored) Directional Icon Set conditional formatting for the range.
4. For the cells in V3:V20, write a formula in V3 that can be filled down to V20 that calculates the average highway mpg value based on the manufacturer name that appears in cells U3:U20 and based on a Midsize Cars classification in V1 for the Carline Class Description found in column I.
a. Once the formula has been written and filled, sort the values in U3:V20 based on descending order of average highway MPG. You may notice that this car manufacturer list appearing in U3:U20 is limited. This is because not all car manufacturers manufacture Midsized Cars.
b. In addition, use 3 Arrows (Colored) Directional Icon Set conditional formatting for the range.
5. For the cells in Y3:Y21, write a formula in Y3 that can be filled down to Y21 that calculates the average highway mpg value based on the manufacturer name that appears in cells X3:X21 and based on a Compact Cars classification in Y1 for the Carline Class Description found in column I.
a. Once the formula has been written and filled, sort the values in X2:Y21 based on descending order of average highway MPG. You may notice that this car manufacturer list appearing in X3:X21 is limited. This is because not all car manufacturers manufacture Compact Cars.
b. In addition, use 3 Arrows (Colored) Directional Icon Set conditional formatting for the range.