Scenario
You work at a car dealership and have volunteered to use Excel help make it easier for people to see information that's important to them. Many customers want to understand the fuel efficiency (abbreviated asFE) of the cars they consider purchasing. Please create a summary sheet following the minimum requirements.
Requirements
On theCar_Fuel_Efficiencysheet, create a new column that calculates the rank for the "Annual Fuel Cost ‐ Conventional Fuel" column, with the lowest cost (smallest number) ranked as #1. If there is a tie, each item should be given anequalranking.
Across all of your worksheets, remember to use theStandards of ProfessionalismLinks to an external site.
, including naming the sheets in your workbook so they are more recognizable for the users, and ordering sheets appropriately.Please review the standards and the rubric carefully.
Summary sheet: Car fuel efficiency
Build a summary sheet (calledEfficiency Summary) that will allow users to quickly pick two vehicles from two different drop‐down lists (data validation), using the column labeled "Car Name (for lookup)" from theCar_Fuel_Efficiencysheet to populate the lists. This should be the landing sheet when users open the workbook.
The summary sheet should display fuel information (for each of the selected cars) from these four columns using lookup functions:
- The ranking of the Annual Fuel Cost ‐ Conventional Fuel
- City FE ‐ Conventional Fuel
- Highway FE ‐ Conventional Fuel
- Combined FE ‐ Conventional Fuel
Have the cell with the "Combined FE ‐ Conventional Fuel" number shadedgreenif the car's "Combined FE ‐ Conventional Fuel" is above 30 andredif the car's "Combined FE ‐ Conventional Fuel" is below 20.
Standards of Professionalism reminders
- Lines - Remove the gridlines on the summary sheet.
- Input / Output designation - Clearly label and format appropriately Input and Output cells (i.e. use appropriate number formatting and use color).
- Typos and Errors - Check spelling.
- First Impressions - Scroll every sheet to the top left, select cell A1.
- First Impressions - Make the Efficiency summary sheet the "landing page" when the workbook is opened and is in the first position of the workbook.