Excel Exercise 1 - Taxi Log
Build a worksheet in Excelon your own according to the directions below. Make sure to include the Style Guide requirements.
Save the document as taxilog.xlsx
Upload & submit it to D2Ldropbox: “Excel: Taxi Log”.
- This week we will analyze data from a day spent driving a taxi in Erie
- Download the following file:
- Save it to your excel folder as taxilog.xlsx
- The data used in this workbook are charges for a cab in ErieYellow Cab which came fromtheirwebsite
- The information in cells A3:B8 are charges for a cab in Erie Yellow Cab in 2014. This information came also came from their website.
- Cell A3 is the title
- Cell B4 contains the charge to enter the taxi
- Cell B5 contains the charge per mile for riding in the taxi
- Cell B6 contains the charge per minute for when the cab is not moving.
- Cell B7 contains the charge for out of county trips, we will not use this.
- Cell B8 contains the charge for fuel recovery. This charge is applied to all trips.
- The information in cells B10:E22 is related to the trips a cab made in a single evening. I made up this information. We can assume that this is the output from the taximeter.
Column B contains the trip number. It is incremented once each time the meter starts.
Column C contains the odometer reading when the trip starts.
Column D contains the odometer reading when the trip ends.
Column E contains the number of minutes spent waiting. If no minutes were spent waiting, the entry is blank.
- From Excel 2013, save the downloaded file as taxilog.xlsx
- Format the charges area as follows
- Expand column A so that all of the text is visible
- Make the text bold
- Center the word Charges over the entire table,
- Make Charges bold
- Format the charges as currency
- Draw a green border around the entire section.
- Add the following column titles to the output log
Cell / Title
E10 / Waiting Time
F10 / Pickup Charge
G10 / Total Miles
H10 / Mileage Charge
I10 / Waiting Charge
J10 / Total Charge
K10 / Percent Change
L10 / Running Total
- All of these should be bold and centered over the column
- Word wrap should be enabled for all of these fields.
- A thick black line should be under all of these titles.
- Format the miles columns with commas.
- In column F, compute the pickup charge
- This will be the sum of the entry charge and the fuel recovery charge.
- This should be a single formula, developed in cell F11 and copied to cells F12:F22
- In column G, compute the total miles for each trip.
- Subtract the starting miles (in column C) from the ending miles (Column D)
- This should be a positive number.
- Develop a single formula in cell G11 and copy this to the other cells.
- Calculate the mileage charge for each trip in column H.
- This should be the total miles, from column G times the mileage charge in cell B5.
- Develop a single formula in cell H11 and copy it to the rest of the table.
- Calculate the waiting charge in column I.
- This is the waiting time in column E times the Each Minute Waiting charge in the charges table.
- Develop a single formula in cell I11 and copy it to the rest of the table.
- Calculate the total charge for each trip in column J
- This is the sum of the pickup charge, the mileage charge and the waiting charge.
- See the instructions above.
- In column K, calculate the percent change from the previous tip.
- This is the percent change = (new value - old value)/old value
- This does not really make sense in this case, but I want you to practice doing this.
- This should be formatted as a percent.
- In column L, compute the running total charge for all trips.
- To make the table easier to read, make the background of every two rows alternate between light blue and no background.
- Apply conditional formatting to the total charge column
- Highest charges should be green.
- Lowest charges should be red.
- Apply conditional formatting to the Percent Change column
- Use the four icon scheme, where lower cells have a red arrow pointing down
- And higher cells have a green arrow pointing up.
- Add an arrow to the $30.93 charge in column J
- The text should be "Best fare of the day"
- You may need to use rotate tool to accomplish this.
- Create a summary area in cells I24:K28
- Add the following
Cell / Value
I24 / Miles Driven
I25 / Miles Charged
I26 / Number of Trips
I27 / Charged for Time
I28 / Total Fees
- For each cell, merge this with the cell in column J
- Make the text bold
- Left align the text.
- Calculate Miles Driven.
- Ending miles in d22 minus the starting miles in c11
- Calculate the Miles Charged
- This is the sum of column g in the table.
- Calculate the Number of Trips.
- This is a count of the number of trips take, column B for example.
- Calculate the number of trips that were charged for time.
- This is the number of trips with minutes recorded in column E.
- Calculate the total fees
- This is the sum of column J.
- Create a statistics table in cells D5 through G8
- Place Miles, Time and Fee in cells E5, F5 and G5
- Center these and make them bold
- Place a border under each
- Place Max, Min and Average in cells D6, D7 and D8
- Make these bold
- Place a border to the right of each.
- Using the appropriate functions, fill in the values of this table.
- Finally, merge cells D1 through L4 into a single cell
- Place "Fare Report by yourname" in this cell
- Use 24 point font.
- Make this text center and middle aligned.
The final image:
- Make sure all data is well formatted and visible.
- When you are finished submit this excel workbook to the drop box “Excel: Taxi Log”.
- All students in the face-to-face class should print report worksheet “as displayed” and “cell formulas” (2 printouts). Please use landscape orientation, resize columns before printing, scaling should be set to “fit-to-one-page”. If you are an online student and can drop off the same print out to my mailbox in ROSS 109 – that would be appreciated!
1/29/2019Page 1 of 8