Hi am having hard time to understand the functions rules am stuck on number 6 in the instructions

 

 

Express Car Rental
Rentals Initiated in Week Starting 4/5/2017
Created by Jason Easton Auto Type
Green Collection
Compact/Midsize
Compact/Midsize
Green Collection
Fullsize/Standard
SUV/Minivan
SUV/Minivan
Fullsize/Standard
Green Collection
Compact/Midsize
Fullsize/Standard
Fullsize/Standard
Fullsize/Standard
SUV/Minivan
SUV/Minivan
Green Collection
Green Collection
Green Collection
Fullsize/Standard
SUV/Minivan
SUV/Minivan
Fullsize/Standard
Fullsize/Standard
Compact/Midsize
Compact/Midsize
SUV/Minivan
Green Collection Auto Id Date Rented
988
4/5/2017
275
4/5/2017
277
4/9/2017
990
4/5/2017
350
4/5/2017
550
4/5/2017
551
4/7/2017
352
4/7/2017
989
4/6/2017
275
4/11/2017
355
4/9/2017
356
4/6/2017
350
4/9/2017
553
4/8/2017
550
4/11/2017
988
4/9/2017
989
4/10/2017
990
4/7/2017
356
4/9/2017
551
4/11/2017
554
4/7/2017
352
4/10/2017
356
4/11/2017
276
4/11/2017
277
4/11/2017
554
4/10/2017
990
4/11/2017 Rental Rates
Green Collection
Compact/Midsize
Fullsize/Standard
SUV/Minivan $49.99
$76.49
$84.99
$104.99 Discount for Express Miles
or Rewards
Cost to Fill Tank 20%
$65.00 Date Returned/or
Expected Return #Days Rented
4/8/2017
4/10/2017
4/10/2017
4/6/2017
4/8/2017
4/9/2017
4/10/2017
4/9/2017
4/9/2017
4/18/2017
4/16/2017
4/8/2017
4/13/2017
4/14/2017
4/17/2017
4/15/2017
4/16/2017
4/10/2017
4/10/2017
4/15/2017
4/9/2017
4/11/2017
4/17/2017
4/14/2017
4/15/2017
4/12/2017
4/18/2017 Payment
Method
Credit Card
COD
Express Miles
Credit Card
Credit Card
Rewards
Credit Card
Credit Card
Credit Card
COD
Credit Card
Credit Card
Rewards
Express Miles
COD
Credit Card
Express Miles
Credit Card
Rewards
Express Miles
Express Miles
Credit Card
Express Miles
Credit Card
Rewards
COD
Express Miles Median Rental Length
Number of Rentals ental g 4/5/2017
on Daily Rate Amount of
Discount Gas Option
n
n
n
n
y
y
n
y
n
n
y
y
y
y
y
n
n
n
n
y
y
y
y
n
n
y
n Cost of
Gas Total Cost Client Data
Dawn Schalow
Laurel Kallio
Steven Thao
Ian Falu
Suzette Karren
Jeron Jacobson
Andrea Ramirez
Keith Wreath
George Linser
Connor Ching
Kelsea VanBuren
Eli Zimmerman
Jamie Mickelson
Diane Kopiski
Ryan Kelley
Joe Kruger
Maria Maldonado
Aaron Atkinson
Suzette Kerr
Michael Stanowicz
Ramona Unger
Ty Ny
Elijah Reynolds
Sally Johnson
Brad Halstad FirstName LastName

Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_CH03_GRADER_PS2_AS - Car Rental 1.1
Project Description:
Jason Easton is a member of the support/decision team for the San Diego branch of Express Car
Rental. He created a worksheet to keep track of weekly rentals in an attempt to identify trends in
choices of rental vehicles, length of rental, and payment method. This spreadsheet is designed only
for Jason and his supervisor to try and find weekly trends and possibly use this information when
marketing and forecasting the type of cars needed on site. The data for the dates of rental, daily
rates, payment method, and gas option have already been entered.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 1 2 3 4 5 6 7 8 9 Points
Possible Instructions
Start Excel. Open the file named e02ch03_grader_as_CarRental.xlsx. Save the file with the name
e02ch03_grader_as_CarRental_LastFirst, replacing LastFirst with your last and first name.
On the RentalData worksheet, in cell E6, enter a date formula to determine the length of rental in days.
Copy this formula to the range E7:E32. Assigned a named range of RentalRates to cells A37:B40. Assign a named range of Discount to cell B42 and a named range of GasCost to cell B44. In cell J6, enter a formula to determine the cost of gas if the customer chose that option as indicated in
column I. If the customer did not choose the option, then the formula should return zero. Use the named
range for cell B44 in this formula. Copy the formula to the range J7:J32.
In cell K6, enter a formula to determine the total cost of the rental based on the daily rate, the number of
days rented, the cost of gas, and any discount given to the customer. Note that the discount applies only
to the car rental, not the cost of gas. Copy the formula to the range K7:K32. 1 12.000 5.000 In cell H6, enter a formula to determine any discount that should be applied. If the payment method was
Express Miles or Rewards, the customer should receive the discount shown in B42. If no discount
should be applied, the formula should return a zero. Use the named range for cell B42, not the cell
address, in this formula. Copy the formula to the range H7:H32. Updated: 01/03/2017 12.000 5.000 In cell G6, use the appropriate lookup and reference function to retrieve the rental rate from the named
range RentalRates. The function should look for an exact matching value from column A in the data.
Copy the function down the column to cell G32. In cell B46, use the appropriate function to calculate the median length of rental in days. 0.000 15.000 12.000 10.000 10.000 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step Excel Project Points
Possible Instructions 10 In cell B47, use the appropriate function to count the number of rentals in the data, using the data in
column A. 11 On the ClientData worksheet, use Flash Fill to separate the client names in column A to the FirstName
and LastName columns respectively. Adjust column width of columns B and C if necessary. 12 13 Insert the File Name in the left footer section of all worksheets in the workbook. 0.000 Total Points 2 5.000 4.000 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Updated: 01/03/2017 10.000 100.000 Current_Instruction.docx

• 1492965573-Alhameli_e02ch03_grader_as_CarRental.xlsx
• 1492965576-YO16XLCH03GRADERPS2AS_-_Car_Rental_11_Instructions 1.docx

ComputerScienceExpert

(11)

Status NEW Posted 23 Apr 2017 04:04 PM My Price 11.00

-----------

Attachments

file 1496777002-Express Car Rental Grader as CarRental.xlsx preview (97 words )

Ex-----------pre-----------ss -----------Car----------- Re-----------nta-----------lRe-----------nta-----------ls -----------Ini-----------tia-----------ted----------- in----------- We-----------ek -----------Sta-----------rti-----------ng -----------4/5-----------/20-----------17C-----------rea-----------ted----------- by----------- Ja-----------son----------- Ea-----------sto-----------nAu-----------to -----------Typ-----------eAu-----------to -----------IdD-----------ate----------- Re-----------nte-----------dDa-----------te -----------Ret-----------urn-----------ed/-----------or -----------Exp-----------ect-----------ed -----------Ret-----------urn-----------#Da-----------ys -----------Ren-----------ted-----------Pay-----------men-----------t M-----------eth-----------odD-----------ail-----------y R-----------ate-----------Amo-----------unt----------- of----------- Di-----------sco-----------unt-----------Gas----------- Op-----------tio-----------nCo-----------st -----------of -----------Gas-----------Tot-----------al -----------Cos-----------tGr-----------een----------- Co-----------lle-----------cti-----------onC-----------red-----------it -----------Car-----------dnC-----------omp-----------act-----------/Mi-----------dsi-----------zeC-----------ODE-----------xpr-----------ess----------- Mi-----------les-----------Ful-----------lsi-----------ze/-----------Sta-----------nda-----------rdy-----------SUV-----------/Mi-----------niv-----------anR-----------ewa-----------rds-----------Ren-----------tal----------- Ra-----------tes-----------Dis-----------cou-----------nt -----------for----------- Ex-----------pre-----------ss -----------Mil-----------es -----------or -----------Rew-----------ard-----------sCo-----------st -----------to -----------Fil-----------l T-----------ank-----------Med-----------ian----------- Re-----------nta-----------l L-----------eng-----------thN-----------umb-----------er -----------of -----------Ren-----------tal-----------sCl-----------ien-----------t

Not Rated(0)

• Buy Answer