Ensure the Sales worksheet is active. Enter a function in cell B8 to create a cu
ID: 379501 • Letter: E
Question
Ensure the Sales worksheet is active. Enter a function in cell B8 to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C8 combined with the quantity in cell D8 and the first initial of the payment type in cell E8. Use Auto Fill to copy the function down, completing the data in column B.
Enter a nested function in cell G8 that displays the word Flag if the Payment Type is Credit and the Amount is greater than or equal to $4000. Otherwise, the function will display a blank cell. Use Auto Fill to copy the function down, completing the data in column G.
Create a data validation list in cell D5 that displays Quantity, Payment Type, and Amount.
Type the Trans# 30038C in cell B5, and select Quantity from the validation list in cell D5.
Enter a nested lookup function in cell F5 that evaluates the Trans # in cell B5 as well as the Category in cell D5, and returns the results based on the data in the range A8:F32.
Create a PivotTable based on the range A7:G32. Place the PivotTable in cell I17 on the current worksheet. Place Payment Type in the Rows box and Amount in the Values box. Format the Amount with Accounting Number Format.
Insert a PivotChart using the Pie chart type based on the data. Place the upper-left corner of the chart inside cell I22. Format the Legend of the chart to appear at the bottom of the chart area. Format the Data Labels to appear on the Outside end of the chart.
Note, Mac users, select the range I18:J20, on the Insert tab, click Recommended Charts, and then click Pie. Format the legend, and apply the data labels as specified.
Insert a Slicer based on Date. Place the upper-left corner of the Slicer inside cell L8.
Ensure the Inventory worksheet is active. Import the Access database eApp_Cap2_Inventory.accdb into the worksheet starting in cell A3.
Note, Mac users, download and import the delimited Inventory.txt file into the worksheet starting in cell A3.
Sales Sheet
Beta Manufacturing
Ensure the Sales worksheet is active. Enter a function in cell B8 to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C8 combined with the quantity in cell D8 and the first initial of the payment type in cell E8. Use Auto Fill to copy the function down, completing the data in column B.
Enter a nested function in cell G8 that displays the word Flag if the Payment Type is Credit and the Amount is greater than or equal to $4000. Otherwise, the function will display a blank cell. Use Auto Fill to copy the function down, completing the data in column G.
Create a data validation list in cell D5 that displays Quantity, Payment Type, and Amount.
Type the Trans# 30038C in cell B5, and select Quantity from the validation list in cell D5.
Enter a nested lookup function in cell F5 that evaluates the Trans # in cell B5 as well as the Category in cell D5, and returns the results based on the data in the range A8:F32.
Create a PivotTable based on the range A7:G32. Place the PivotTable in cell I17 on the current worksheet. Place Payment Type in the Rows box and Amount in the Values box. Format the Amount with Accounting Number Format.
Insert a PivotChart using the Pie chart type based on the data. Place the upper-left corner of the chart inside cell I22. Format the Legend of the chart to appear at the bottom of the chart area. Format the Data Labels to appear on the Outside end of the chart.
Note, Mac users, select the range I18:J20, on the Insert tab, click Recommended Charts, and then click Pie. Format the legend, and apply the data labels as specified.
Insert a Slicer based on Date. Place the upper-left corner of the Slicer inside cell L8.
Ensure the Inventory worksheet is active. Import the Access database eApp_Cap2_Inventory.accdb into the worksheet starting in cell A3.
Note, Mac users, download and import the delimited Inventory.txt file into the worksheet starting in cell A3.
Sales Sheet
Beta Manufacturing
March Transactions Search Results Trans # Category Results Date Trans # Item Quantity Payment Type Amount Status 3/1/2018 3003 8 Credit $4,800 3/2/2018 5005 4 Financed $2,800 3/3/2018 5005 1 Credit $700 3/3/2018 5005 10 Financed $7,000 3/5/2018 1001 6 Credit $1,500 3/7/2018 5005 5 Credit $3,500 3/9/2018 1001 5 Credit $1,250 3/10/2018 5005 3 Credit $2,100 3/10/2018 2002 1 Financed $500 3/10/2018 4004 4 Financed $2,600 3/11/2018 2002 9 Financed $4,500 3/11/2018 3003 8 Credit $4,800 3/12/2018 1001 9 Financed $2,250 3/13/2018 2002 8 Financed $4,000 3/13/2018 2002 9 Credit $4,500 3/14/2018 3003 9 Credit $5,400 3/18/2018 3003 1 Credit $600 3/24/2018 4004 1 Credit $650 3/24/2018 4004 3 Financed $1,950 3/24/2018 4004 10 Cash $6,500 3/28/2018 2002 10 Credit $5,000 3/28/2018 1001 9 Cash $2,250 3/30/2018 3003 8 Credit $4,800 3/30/2018 4004 6 Financed $3,900 3/30/2018 2002 9 Cash $4,500Explanation / Answer
The formulae used are:
The final sheet is:
Date Trans # Item Quantity Payment Type Amount Status 43103 =CONCATENATE(C8,D8,LEFT(E8,1)) 3003 8 Credit 4800 =IF(F8>=4000,IF(E8 ="Credit","FLAG"," " )," " ) 43134 =CONCATENATE(C9,D9,LEFT(E9,1)) 5005 4 Financed 2800 =IF(F9>=4000,IF(E9 ="Credit","FLAG"," " )," " ) 43162 =CONCATENATE(C10,D10,LEFT(E10,1)) 5005 1 Credit 700 =IF(F10>=4000,IF(E10 ="Credit","FLAG"," " )," " ) 43162 =CONCATENATE(C11,D11,LEFT(E11,1)) 5005 10 Financed 7000 =IF(F11>=4000,IF(E11 ="Credit","FLAG"," " )," " ) 43223 =CONCATENATE(C12,D12,LEFT(E12,1)) 1001 6 Credit 1500 =IF(F12>=4000,IF(E12 ="Credit","FLAG"," " )," " ) 43284 =CONCATENATE(C13,D13,LEFT(E13,1)) 5005 5 Credit 3500 =IF(F13>=4000,IF(E13 ="Credit","FLAG"," " )," " ) 43346 =CONCATENATE(C14,D14,LEFT(E14,1)) 1001 5 Credit 1250 =IF(F14>=4000,IF(E14 ="Credit","FLAG"," " )," " ) 43376 =CONCATENATE(C15,D15,LEFT(E15,1)) 5005 3 Credit 2100 =IF(F15>=4000,IF(E15 ="Credit","FLAG"," " )," " ) 43376 =CONCATENATE(C16,D16,LEFT(E16,1)) 2002 1 Financed 500 =IF(F16>=4000,IF(E16 ="Credit","FLAG"," " )," " ) 43376 =CONCATENATE(C17,D17,LEFT(E17,1)) 4004 4 Financed 2600 =IF(F17>=4000,IF(E17 ="Credit","FLAG"," " )," " ) 43407 =CONCATENATE(C18,D18,LEFT(E18,1)) 2002 9 Financed 4500 =IF(F18>=4000,IF(E18 ="Credit","FLAG"," " )," " ) 43407 =CONCATENATE(C19,D19,LEFT(E19,1)) 3003 8 Credit 4800 =IF(F19>=4000,IF(E19 ="Credit","FLAG"," " )," " ) 43437 =CONCATENATE(C20,D20,LEFT(E20,1)) 1001 9 Financed 2250 =IF(F20>=4000,IF(E20 ="Credit","FLAG"," " )," " ) 3/13/2018 =CONCATENATE(C21,D21,LEFT(E21,1)) 2002 8 Financed 4000 =IF(F21>=4000,IF(E21 ="Credit","FLAG"," " )," " ) 3/13/2018 =CONCATENATE(C22,D22,LEFT(E22,1)) 2002 9 Credit 4500 =IF(F22>=4000,IF(E22 ="Credit","FLAG"," " )," " ) 3/14/2018 =CONCATENATE(C23,D23,LEFT(E23,1)) 3003 9 Credit 5400 =IF(F23>=4000,IF(E23 ="Credit","FLAG"," " )," " ) 3/18/2018 =CONCATENATE(C24,D24,LEFT(E24,1)) 3003 1 Credit 600 =IF(F24>=4000,IF(E24 ="Credit","FLAG"," " )," " ) 3/24/2018 =CONCATENATE(C25,D25,LEFT(E25,1)) 4004 1 Credit 650 =IF(F25>=4000,IF(E25 ="Credit","FLAG"," " )," " ) 3/24/2018 =CONCATENATE(C26,D26,LEFT(E26,1)) 4004 3 Financed 1950 =IF(F26>=4000,IF(E26 ="Credit","FLAG"," " )," " ) 3/24/2018 =CONCATENATE(C27,D27,LEFT(E27,1)) 4004 10 Cash 6500 =IF(F27>=4000,IF(E27 ="Credit","FLAG"," " )," " ) 3/28/2018 =CONCATENATE(C28,D28,LEFT(E28,1)) 2002 10 Credit 5000 =IF(F28>=4000,IF(E28 ="Credit","FLAG"," " )," " ) 3/28/2018 =CONCATENATE(C29,D29,LEFT(E29,1)) 1001 9 Cash 2250 =IF(F29>=4000,IF(E29 ="Credit","FLAG"," " )," " ) 3/30/2018 =CONCATENATE(C30,D30,LEFT(E30,1)) 3003 8 Credit 4800 =IF(F30>=4000,IF(E30 ="Credit","FLAG"," " )," " ) 3/30/2018 =CONCATENATE(C31,D31,LEFT(E31,1)) 4004 6 Financed 3900 =IF(F31>=4000,IF(E31 ="Credit","FLAG"," " )," " ) 3/30/2018 =CONCATENATE(C32,D32,LEFT(E32,1)) 2002 9 Cash 4500 =IF(F32>=4000,IF(E32 ="Credit","FLAG"," " )," " )Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.