EXCEL Assignment - Excel attachment: https://drive.google.com/file/d/1YJNYqmEsvI
ID: 3902409 • Letter: E
Question
EXCEL Assignment - Excel attachment: https://drive.google.com/file/d/1YJNYqmEsvI1mE5IZShTg0zAvj1xi_3nR/view?usp=sharing Car Inventory You have been tasked with creating an Excel workbook that will allow sales persons the ability to search existing inventory by VIN. They must also be able to define any combination of search criteria that returns a list of results and summary data pertaining to that result set. They have also asked that you create a separate sheet to help them calculate how large of loan they can afford and an amortization schedule for the loan. Populate Calculated Fields You need to calculate the number of days each vehicle has been on the lot and if there are any special notes that need to be added to each vehicle. Any vehicle that has been on the lot more than 200 days is eligible for a special employee bonus if they are able to sell that vehicle. Any vehicle that has been on the lot between 125 and 200 days will earn the seller one raffle ticket in the end of the month $1000 bonus. a. Open exam2_data and save as Exam2_LastFirst. b. On the Inventory worksheet, insert a function in H7:H206 to calculate the number of days each vehicle has been on the lot using 1/1/2018 as today’s date. c. Insert a function in I7:I206 that will display “Bonus Eligible” for cars that have been on the lot more than 200 days, “Raffle Eligible” for cars that have been on the lot between 125 and 200 days, and nothing for the remaining vehicles. Search Inventory You need to create a search by VIN and an advanced search of any criteria and return the required results. a. In cell B2, enter the value from A7. b. Insert a function in D2 that will return the arrival date of any vehicle based on which VIN is entered in B2. c. Insert a function in F2 that will return the Special Notes of any vehicle based on which VIN is entered in B2. d. Enter the following values for the advanced search criteria in the range K3S3: · Year = 2018 · Type = Sedan · GPS = Yes e. Create an advanced search using the search criteria in the range K2:S3 and the inventory data where the results will be displayed in the search results (starting in K10). f. Verify results populate as expected.
Explanation / Answer
From below link you can download the solved Excel File. Explanation how to do is described below the link.
https://drive.google.com/open?id=1dz1JMk_VWm7oEXiEAuHwOhPSS_YjNQCn
a. Open exam2_data and save as Exam2_LastFirst.
Answer : this can be done by using Save As option from file menu.
Type new File name : Exam2_LastFirst.xlsx
b. On the Inventory worksheet, insert a function in H7:H206 to calculate the number of days each vehicle has been on the lot using 1/1/2018 as today’s date.
Answer : To get the number of days, need to use datedif function. type in cell H7 =DATEDIF(F7,"01-01-2018","d")
then copy formula from H7 to H8:H206
c. Insert a function in I7:I206 that will display “Bonus Eligible” for cars that have been on the lot more than 200 days, “Raffle Eligible” for cars that have been on the lot between 125 and 200 days, and nothing for the remaining vehicles. Search Inventory You need to create a search by VIN and an advanced search of any criteria and return the required results.
Answer : To get the desired output, need to use nested IF function/statement.
=IF(H7>200,"Bonus Eligible",IF(H7>125,"Raffle Eligible",""))
a. In cell B2, enter the value from A7.
Answer : Type any number from A7 to A206 (example : 23477138)
b. Insert a function in D2 that will return the arrival date of any vehicle based on which VIN is entered in B2.
Answer : in A7 type the formula (index match functions) {=INDEX(F7:F206,MATCH(B2,A7:A206,0))}
1.Match function will return the row number of matching cell value (i.e., 23477138).
2.Index Function will get the data of the matching row number from match function.
3.You need to use Ctrl+Shift+Enter to calculate the function.
Note:
i. Simply typing value in Cell D2, =INDEX(F7:F206,MATCH(B2,A7:A206,0)) will not work. You need to use Ctrl+Shift+Enter, so that function will work properly. because it is an array function type.
ii. Vlookup function can be used to fetch the same output as generated by the above function, but the condition to be noted is the array table to be in sorted order. in the excel data is not sorted in VIN so used INDEX MATCH Function.
c. Insert a function in F2 that will return the Special Notes of any vehicle based on which VIN is entered in B2.
Answer : This is similar to above function instead of date using special notes column (i.e., I7:I206)
=INDEX(I7:I206,MATCH(B2,A7:A206,0))
need to use Ctrl+Shift+Enter after type the above formula in cell F2
d. Enter the following values for the advanced search criteria in the range K3S3: ·
Year = 2018 · Type = Sedan · GPS = Yes
Answer : Type the values as above mentioned in respect cells.
e. Create an advanced search using the search criteria in the range K2:S3 and the inventory data where the results will be displayed in the search results (starting in K10).
Answer : Below function will pull VIN from the given data. here used MATCH function with multiple criteria as mentioned in question.
=INDEX($A$7:$A$206,MATCH($L$3 & $M$3 & $O$3, $B$7:$B$206 & $C$7:$C$206 & $E$7:$E$206, 0))
in this function also need to use Ctrl+Shift+Enter
Dollar Sign ($) is used between Cell names to restrict the value change while copying the function from one cell to another.
f. Verify results populate as expected.
Asnwer : Result can be seen on the excel K3S3 with the data provided on left hand side table.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.