Good evening,

I need help with the VLOOKUP formulas  please.

 

 

Jefferson Community College
Watertown, NY Instructor Andy Smith
CIS 110 - Introduction to Computing and Applications Excel Topic 4 - Lab
Context
You work for a company that provides livery services. Your company operates busses, vans, and
limousines. The company charges a flat rate for use of each vehicle plus a variable hourly rate to cover
the driver. Therefore, you have decided to create the work-order spreadsheet shown at the end of
these instructions. The user of this sheet will enter customer name and address, the number of hours
they’ll be needing the services, the count of each vehicle they’ll need, and the distance they’ll be
driven. From those variables, we’ll calulate the costs. Design Notes
1.) These are extra notes about the design of the sheet. Not an exhaustive list of instructions. Use the
provided image to construct your WorkSheet. 2.) Cells B2, B3, and B4 are each individually merged across, over to column C 3.) Cells B2, B3, B4, B7, as well as the range of B13:D14 are Sample Data cells. Your Sample sheet will have
these filled in, while the Blank sheet will not. They also have a lightly shaded background color and a Jefferson Community College
Watertown, NY Instructor Andy Smith
CIS 110 - Introduction to Computing and Applications dashed bottom border.
4.) Cells B15:D17, D19, D20 contain formulas. Do not attempt to type in “$ - “. 5.) Cells containing money values are in Accounting format with two decimal places. 6.) Note the Cell and Text properties set on B12:D12, E2, E7. 7.) Note the Borders set on the two Lookup tables. 8.) Note that our name is NOT in A1. Place your name in the Left Header of the Worksheet. Formula Instructions
1.) The Vehicle Costs formula (B15:D15), uses the VLOOKUP function to find the rental price for the current
vehicle type and then multiplies that price by the number of vehicles requested. 2.) The Driver Costs formula (B16:D16), uses the VLOOKUP function to find the driver hourly rate based on
distance they will drive, multiplies that by the number of hours to they’ll be working, and multiples that
by the number of vehicles requested. 3.) The Subtotal formula (B17:D17), adds the vehicle and driver costs together. 4.) The Tax formula (D19), is the SUM of all subtotals times the Local Tax. 5.) The Total formula (D20), is the SUM of all subtotals plus the Tax. Submission
1.) Enable the Show Formulas option and double check your work. Then disable Show Formulas. 2.) Place your name in the Left Header of the Worksheet. 3.) Make a copy of your sheet in the same workbook and select the new sheet. 4.) Fill the new copy with sample data of your own in the Data Entry cells. Double check that the numbers
make sense. Feel free to use simple numbers to make this step easier. 5.) Upload your spreadsheet file to Blackboard in the appropriate assignment area.