Taxi Log Practice Exercise
 Download this
file
 This workbook contains information related to a day spent driving a taxi
in Erie pa.
 The information in cells A3:B8 are charges for a cab in Erie. This
information came from http://www.erieyellowcab.com/rates.html

 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.
 Start excel 2013 and open the workbook.
 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
 If you are unable to develop this formula, ASK FOR HELP. You need to
master this technique.

 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.
 If you are unable to develop this formula, ASK FOR HELP. You need to
master this technique.
 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.
 If you are unable to develop this formula, ASK FOR HELP. You need to
master this technique.
 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.
 You should know how to do this from previous instruction.
 But 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.
 Again, you should know how to do this from previous instruction.

 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 to use rotate tool to accomplish this.

 Create a summary area in cells H24:J28
 Add the following
Cell 
Value 
H24 
Miles Driven 
H25 
Miles Charged 
H26 
Number of Trips 
H27 
Charged for Time 
H28 
Total Fees 
 For each cell, merge this with the cell in column I
 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.

 My final image:
 Make sure all data is well formatted and visible.
Double check against the video of the solution !!