question 6: calculate the registration fee for each vehicle.

can someone help help me how to do this step by step and the formula needed?

Data Home Insert Draw Page Layout Formulas Review View Help Tell me what you want to do & AL Define Name & Trace Precedents 98 Show Formulas Use in Formula Trace Dependents Error Checking - sert AutoSum Recently Financial Logical Text Date & Lookup & Math & Name Watch Calculation nction Used - Time - Reference Trig. Functions - Manager in Create from Selection Remove Arrows-Evaluate Formula Window Options Function Library Defined Names Formula Auditing Calculati 13 A More B D E F G H Vehicle Purchase Vehicle 1 Vehicle 2 Vehicle 3 Vehicle 4 Description 2016 Ford Mustang Convertible2014 Honda Accord Hybric Vehicle Type Passenger Passenger 2014 Ducati Diavel Strada Motorcycle 2016 Chevy Colorado Truck dealer $27,800 17 28 22.5 private seller $20,463 50 45 47.5 private seller $14,250 36 36 36 dealer $20,100 22 31 26.5 MPG Avel $600 $300 $500 $500 Seller Price MPG City MPG Highway Average MPG Yearly Costs Maintenance/Year Registration Feel Insurance/Year Need Loan? Yes Amount to Borrow APR Years Monthly Costs Purchase Data Maintena Registr Insura $700 $1,500 $600 Yes Yes $1,000 Yes $19,800 3.20% Amountt $12,463 1.90% $6,250 5.90% 2 $12,100 2.90% 3 5 3 DOLL s Window Help EX2016-Challenge Yourself-3-3-instructions.pdf (page 1 of 3) @ Q se 2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at top of the workbook so you can modify it. 3. The registration fee information in cells B11:C17 on the Data sheet will be used in lookup formulas later this project. Name the range Registration Fees to make it easier to use later. 4. Retum to the Purchase worksheet. 5. Calculate the average MPG for each vehicle. 2. Enter a formula in cell C10 using the AVERAGE function to calculate the average value of C8:09. Use only one argument b. Copy the formula to the appropriate cells for the other vehicles. c. Excel will detect a possible error with these formulas. Use the SmartTag to ignore the error. Hist: Use the SmartTag while cells C10:F10 are selected and the error will be ignored for all the selected cells. 6. Calculate the registration fee for each vehicle. Enter a formula in cell C13 to look up the registration fee for the first vehicle. Use the vehicle type in cell C4 as the Lookup_valu argument. Use the Registration Fees named range as the Table_array argument. The registration fees are located in column 2 of the data table. Require an exact match. b. Copy the formula to the appropriate cells for the other vehicles. Challenge Yourself 3.3 Last Updated 3/19/18 i 1 Page Askills Anneach Excel 2016 Charter 3.singformidas and SAC Admiss Add shortcut 30 od DO s FY # A $ 4 * 3 % 5 6 & 7 8 9 0 WT E R. T Y U { P. Data Home Insert Draw Page Layout Formulas Review View Help Tell me what you want to do & AL Define Name & Trace Precedents 98 Show Formulas Use in Formula Trace Dependents Error Checking - sert AutoSum Recently Financial Logical Text Date & Lookup & Math & Name Watch Calculation nction Used - Time - Reference Trig. Functions - Manager in Create from Selection Remove Arrows-Evaluate Formula Window Options Function Library Defined Names Formula Auditing Calculati 13 A More B D E F G H Vehicle Purchase Vehicle 1 Vehicle 2 Vehicle 3 Vehicle 4 Description 2016 Ford Mustang Convertible2014 Honda Accord Hybric Vehicle Type Passenger Passenger 2014 Ducati Diavel Strada Motorcycle 2016 Chevy Colorado Truck dealer $27,800 17 28 22.5 private seller $20,463 50 45 47.5 private seller $14,250 36 36 36 dealer $20,100 22 31 26.5 MPG Avel $600 $300 $500 $500 Seller Price MPG City MPG Highway Average MPG Yearly Costs Maintenance/Year Registration Feel Insurance/Year Need Loan? Yes Amount to Borrow APR Years Monthly Costs Purchase Data Maintena Registr Insura $700 $1,500 $600 Yes Yes $1,000 Yes $19,800 3.20% Amountt $12,463 1.90% $6,250 5.90% 2 $12,100 2.90% 3 5 3 DOLL s Window Help EX2016-Challenge Yourself-3-3-instructions.pdf (page 1 of 3) @ Q se 2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at top of the workbook so you can modify it. 3. The registration fee information in cells B11:C17 on the Data sheet will be used in lookup formulas later this project. Name the range Registration Fees to make it easier to use later. 4. Retum to the Purchase worksheet. 5. Calculate the average MPG for each vehicle. 2. Enter a formula in cell C10 using the AVERAGE function to calculate the average value of C8:09. Use only one argument b. Copy the formula to the appropriate cells for the other vehicles. c. Excel will detect a possible error with these formulas. Use the SmartTag to ignore the error. Hist: Use the SmartTag while cells C10:F10 are selected and the error will be ignored for all the selected cells. 6. Calculate the registration fee for each vehicle. Enter a formula in cell C13 to look up the registration fee for the first vehicle. Use the vehicle type in cell C4 as the Lookup_valu argument. Use the Registration Fees named range as the Table_array argument. The registration fees are located in column 2 of the data table. Require an exact match. b. Copy the formula to the appropriate cells for the other vehicles. Challenge Yourself 3.3 Last Updated 3/19/18 i 1 Page Askills Anneach Excel 2016 Charter 3.singformidas and SAC Admiss Add shortcut 30 od DO s FY # A $ 4 * 3 % 5 6 & 7 8 9 0 WT E R. T Y U { P