# Excel Exercise 1 - Taxi Log

ZimmerCSCI104

**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