Return to the Purchase worksheet.

$5.$ Calculate the average MPG for each vehicle.

a$.$ V Enter a formula in cell C$10$ using the AVERAGE function to calculate the average value of C$8$:C$9.$ 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. Hint: Use the SmartTag while cells C$10$:F$10$ are selected and the error will be ignored for all the selected cells.

$6.$ Calculate the registration fee for each vehicle.

a$.$ Enter a formula in cell $C13$ to look up the registration fee for the first vehicle. Use the vehicle type in cell C$4$ as the Lookup$\_$value argument. Use the RegistrationFees 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.

$7.$ Determine whether or not you will need a loan for each potential purchase.

a$.$ In cell C$15,$ enter a formula using an IF function to determine if you need a loan. Your available cash is located on the Data sheet in cell A$3.$ If the price of the tar is less than or equal to your available cash, display $"$no$".$ If the price of the car is more than your available, cash, display "yes". Use absolute references where appropriate$-$you will be copying this formula across the row.

b$.$ Copy the formula to the appropriate cells for the other vehicles.

$8.$ Calculate how much you would need to borrow for each purchase.

a$.$ In cell C$16,$ enter a formula to calculate the price of the vehicle minus your available cash $($from cell A Data worksheet$).$ Use absolute references where appropriate$-$you will be copying this formula across

Reset Progress

b$.$ Copy the formula to the appropriate cells for the other vehicles