Can I get the formulas 12-18

Heady Pasta U , A 2.4 12 The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On the Transactions worksheet, in cell G10, replace the static value by entering a formula that will return Yes if the category of the item sold is not Accessories, otherwise retum No. Use the fill handle to copy the formula down through G30. 2.4 13 I To provide incentives for employees to sell more goods, Aleeta has set sales goals for each employee for items belonging to various categories. On the Employee Analysis worksheet, you will use an INDEX function to retrieve the correct sales goal based on the Employeeld and the desired Category. Because INDEX functions need to be told what row and column number to go to, you will use MATCH functions to do the counting an determine the correct row and column numbers for the INDEX to use. On the Employee Analysis worksheet, in cell J23, enter a MATCH function that will return the row number of whichever category is selected in cell H20. Use the fill handle to copy the formula down through J27 In cell K23, enter a MATCH function that will return the column number of the Employeeld in cell F23. Use the fill handle to copy the formula down through K27. In cell H23, enter an INDEX function, using the range G5:59 as the array, and referencing the values in J23 and K23 for the row and column numbers. Use the fill handle to copy the formula down through H27. Change the category in cell H20 to Accessories to make sure the function retrieves the correct Page 3 of 4 1713 words English United States Focus 14 3 15 3 Aleeta would like to reward the customer who spends the most money at the Pro Shop and needs to be able to easily retrieve their Customer D. CustomerName SalesVolume, and TotalSales for the customer with the largest TotalSales value. On the CustomerAnalysis worksheet, in cell B20, enter an INDEX function that will retrieve the Customers of the customer with the highest TotalSales from the array A4:G16. Use the fill handle to copy the formula down through B23. Shipping costs vary depending on method of shipping and the zone number of the destination Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the Shipping Costs worksheet create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell 17, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table array so that the customer can choose Express or Priority shipping. Change the shipping method from Priority to Express to ensure the correct shipping cost is retrieved. Step Instructions Points Possible 16 0.9 17 1.5 Several formulas on the Transactions worksheet resulted in #N/A where the lookup value is blank. The #N/A is not only distracting, it is confusing to users because they may believe that there are calculation errors when in actuality the errors are valid errors. On the Transactions worksheet, incorporate the IFERROR function in cells G10, H10, and J10 so that a blank value (") is retumed for the value if error. Use the fill handle to copy each formula down through row 30. Aleeta would like to know the net revenue generated from each transaction as well as the total discounts for each transaction. On the Transactions worksheet, in cell L10, calculate the net revenue by multiplying the gross revenue by 1 - the sum of all discounts. If the calculation returns an error, replace the error with a blank value (**). Use the fill handle to copy the formula down through L30. In cell M10, calculate the total discount for the transaction by subtracting the net revenue from the gross revenue. If the calculation retums an error, replace the error with a blank value (""} Use the fill handle to copy the formula down through M30. 18 Save and close the workbook Excel_Cho5_Prepare_ProShop Sales.xlsx. Exit Excel. Submit the file as directed 0 Total Points 30 $% 9 Marge & Carter Condional Formal Formatting At Table Format ilil Styles Puth B TV M 1 Dalyser Accessories Clothing Clubi Shoes Total EMPORE 5 300.00 $ 20000 S 5000 $ S 150.00 5 1,150.00 EMP90038 EMP-00024 EMP60015 LMP-00026 200.003 CO DOS 100.00 200 DO 250.00 0.00 SO DOS 700.00 700.00 300.00 150.00 250.00 $ 250.00 5 1000 1,210.00 $ 1.400 $ 1.450.00 10000 123 xfx-MATCH(25,81:63.0) C D 1 Employees 2 Golf Trainer Certification 4 IMPID HE Mau 5 MP BONG Rama Hope 65 Cro 6 FMP-0003 35 Mt Cartiled 7 EMP000134 Tyrone secer Mod Wine 67 Creed 9 EP00026 Christyan 25 Not Certified 10 11 12 POS Training 13 IMPID Level 14 IM00036 Harpe 45eet 15 Master 35 Print 16 IM00034 Tyrone Thurman 3D Proficient 17 Meislerrold Wine 20 segner 18 M600 Od 25 Proficient 19 20 21 Employee Daily Commission 22 IMPID Terve Common 23 EMP00075 urar S 3.532 63 177 74 IMG0038 Astrage $ 4602 230 25 IMG Runde $ 2018 101 26 Mens 5451117 27 EM0006 Amodate JES 82411 41 Comites REVE 5 $ 2.500S 3.OS 9 4,00 11N SN dute Sales God Cal Num Rew Nm VA Category Employee Sales Goals EMPID EMPO016 Ramal Hample IMPOO Per EMPOO Tror Thema EMO015 Der Wide MOON Chnayam 30 31 34 25 33 30 Employee Analysis Customer Analysis Transactions Shipping Costs Products + Styles F4 xfx714.8455 A B C H 1 M 1 Customer Analysis Member? No No Yes No Yes NO Coupons 10% OFF 15NO 20NON 2 3 Cust 4 CUST6218 5 CUST-508 6 CUST-8820 7 CUST:1340 8 CUST-704 9 CUST-7076 10 CUST-1285 11 CUST-2446 12 CUST-8440 13 CUST-3816 14 CUST-588 15 CUST:3267 16 CUST-5600 17 Customer Name Samud Tapp Arthur Cracker Christine Cardinal John Ketcha Krista Sun Karen Corcoran Doue Vandenburg Oale Foxhoven Sherry Callahan Lula dar Peg Moore Stephanie Raymond Weber Volume TotalSales Value 111 $ 714.RS low 1015 661.61 High 16 $ 569.25 High 19 s 1,338.75 High SS 212.29 Low &$ 168 8S Low sls 275.70 Low 11 1.558.51 6 $ 16187 Low 12 5 2010.44 95 61.32 Low 25 2,15682 High 125 543.75 Low NO NO No Coupon SON ON 15% OFF 20% OFF 15% OFF 10% OFF 10% OF 10NO 15NOT NON OR 20% OFF 10% OFF 15OH 1050 NS No Yes 19 test Customer (most amount spent 20 CD 21 Customer Name 22 Volume 23 TotalSales 24 25 26 27 28 29 30 31 32 33 35 36 39 Employee Analysis Customer Analysis Transactions Shipping Costs Products + P) 1253 Excel Fledermet Foma Tool Data Window Hep BE 3GOT. 000 tel.CHOS Pre. Prothesest Horne Dr Page Layout Fores Data Red View New Tab New Tellime X Cartel ' ' w General S 9 Com . One GID Sort R ter Transaction Analysis TI 171 EL 18 BE 11 ALLA DER WE BB332 100 C 15 30 1 IT DOBU 2 BELIEEE1E12 11 141 7 HE 30 CHE 7 2 3 $ 4 % 5 & 7 1 0 8 9 a W E R T Y U o S D F G H JK N V C c V B N M OW 9 Excel File Edit Format Tools Data View Insert BE Window Help con ahmed Excel Chos Prepare Proshop Sales (0) Autoswe Home New Tab Insert Draw Page Layout Formulas Data Review View New Tab New Tab Tell me Calibri (Body 18 - A A 2 Wrap Tea v General $ % ) Inae BK Dale B TU a. Au Merge Center ai Cenditons om Call Formatting Table Styles Forn A1 x fx Shipping Costs A D F G . 1 K 4 M N P g Shipping costs Moril MMI Zone 2 Zone3 $635 $6.75 $1615 $6.29 $62 $701 $9.29 $853 $824 $461 $8.95 $10 18 Zone 1 $635 $6.35 $6.89 $7.29 $824 $8.95 2anet $6. $6 $7.10 5942 $109 $11.12 Zones 4.50 $6.98 $7.91 $1377 Priority 5.5 Jane Shipping Method Well Zene Shipping Cart $164 $1951 2 3 Weled 5 0.5 6 3 7 3 9 10 10 11 12 13 10 Weight 18 16 2 11 18 19 20 10 21 Zone $24) $742 $123 SA Exprew MIR Zone 2 Zone $76) $15 $767 $8.15 $82 $844 S1024 $11 $10.14 $122 Zone 5823 58 5862 $11 $11.19 $1234 Zone SB SA $9.01 $16.12 S80 $10.24 $2141 21 24 25 21 28 14 Autosave 3 DY. Formulas Data Home New Tab 0000ahmed Excel Chos Prepare Pro Shop Sales (1) View New Tab New Tab Tell me Insert Draw Page Layout Review Calibri (Bodyl 18 - A A General Kindert Paste B I U a.A. 23 Wrap Test Merpes Carta PE $ % ) 23 Cardional Format Cal Formatting Table Slys x Delete 11 Format Al Xfx Product List E F G H 1 1 K M 0 P Product list Q Category Dertion Winchester Wineren Prohanded Club As Shoes Trimble Comforts time wriders Resort Tees Merdivelychan Calle ce dering dong Resort polo shirt 3 m 4349 $ APER 60581011 7C4T23 #058329 1 MRP24 DO TEK11 11 6175157 12 CR19 13 049202 14 CSR11 15 55729 15 3224 17 MT1114 18 74029 19 2323 10 CB 21 39157 22 23 24 25 26 23 20 29 Ret Price 5 49.55 S 419.00 5 12.00 5 49.00 S 59.00 5 2.00 5 5.55 5 15.45 14.95 S 1995 5 113 5 1295 $ 32.95 3295 5 45100 129.00 $ $ 97.00 Clothing Clothing of button shirt pollen wir potensi Wirden pre harded Resort peber Ramal Out Access RO 10 13 11 30 32 29 Employee Analyse Theo oping costs Products + OG Heady Pasta U , A 2.4 12 The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On the Transactions worksheet, in cell G10, replace the static value by entering a formula that will return Yes if the category of the item sold is not Accessories, otherwise retum No. Use the fill handle to copy the formula down through G30. 2.4 13 I To provide incentives for employees to sell more goods, Aleeta has set sales goals for each employee for items belonging to various categories. On the Employee Analysis worksheet, you will use an INDEX function to retrieve the correct sales goal based on the Employeeld and the desired Category. Because INDEX functions need to be told what row and column number to go to, you will use MATCH functions to do the counting an determine the correct row and column numbers for the INDEX to use. On the Employee Analysis worksheet, in cell J23, enter a MATCH function that will return the row number of whichever category is selected in cell H20. Use the fill handle to copy the formula down through J27 In cell K23, enter a MATCH function that will return the column number of the Employeeld in cell F23. Use the fill handle to copy the formula down through K27. In cell H23, enter an INDEX function, using the range G5:59 as the array, and referencing the values in J23 and K23 for the row and column numbers. Use the fill handle to copy the formula down through H27. Change the category in cell H20 to Accessories to make sure the function retrieves the correct Page 3 of 4 1713 words English United States Focus 14 3 15 3 Aleeta would like to reward the customer who spends the most money at the Pro Shop and needs to be able to easily retrieve their Customer D. CustomerName SalesVolume, and TotalSales for the customer with the largest TotalSales value. On the CustomerAnalysis worksheet, in cell B20, enter an INDEX function that will retrieve the Customers of the customer with the highest TotalSales from the array A4:G16. Use the fill handle to copy the formula down through B23. Shipping costs vary depending on method of shipping and the zone number of the destination Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the Shipping Costs worksheet create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell 17, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table array so that the customer can choose Express or Priority shipping. Change the shipping method from Priority to Express to ensure the correct shipping cost is retrieved. Step Instructions Points Possible 16 0.9 17 1.5 Several formulas on the Transactions worksheet resulted in #N/A where the lookup value is blank. The #N/A is not only distracting, it is confusing to users because they may believe that there are calculation errors when in actuality the errors are valid errors. On the Transactions worksheet, incorporate the IFERROR function in cells G10, H10, and J10 so that a blank value (") is retumed for the value if error. Use the fill handle to copy each formula down through row 30. Aleeta would like to know the net revenue generated from each transaction as well as the total discounts for each transaction. On the Transactions worksheet, in cell L10, calculate the net revenue by multiplying the gross revenue by 1 - the sum of all discounts. If the calculation returns an error, replace the error with a blank value (**). Use the fill handle to copy the formula down through L30. In cell M10, calculate the total discount for the transaction by subtracting the net revenue from the gross revenue. If the calculation retums an error, replace the error with a blank value (""} Use the fill handle to copy the formula down through M30. 18 Save and close the workbook Excel_Cho5_Prepare_ProShop Sales.xlsx. Exit Excel. Submit the file as directed 0 Total Points 30 $% 9 Marge & Carter Condional Formal Formatting At Table Format ilil Styles Puth B TV M 1 Dalyser Accessories Clothing Clubi Shoes Total EMPORE 5 300.00 $ 20000 S 5000 $ S 150.00 5 1,150.00 EMP90038 EMP-00024 EMP60015 LMP-00026 200.003 CO DOS 100.00 200 DO 250.00 0.00 SO DOS 700.00 700.00 300.00 150.00 250.00 $ 250.00 5 1000 1,210.00 $ 1.400 $ 1.450.00 10000 123 xfx-MATCH(25,81:63.0) C D 1 Employees 2 Golf Trainer Certification 4 IMPID HE Mau 5 MP BONG Rama Hope 65 Cro 6 FMP-0003 35 Mt Cartiled 7 EMP000134 Tyrone secer Mod Wine 67 Creed 9 EP00026 Christyan 25 Not Certified 10 11 12 POS Training 13 IMPID Level 14 IM00036 Harpe 45eet 15 Master 35 Print 16 IM00034 Tyrone Thurman 3D Proficient 17 Meislerrold Wine 20 segner 18 M600 Od 25 Proficient 19 20 21 Employee Daily Commission 22 IMPID Terve Common 23 EMP00075 urar S 3.532 63 177 74 IMG0038 Astrage $ 4602 230 25 IMG Runde $ 2018 101 26 Mens 5451117 27 EM0006 Amodate JES 82411 41 Comites REVE 5 $ 2.500S 3.OS 9 4,00 11N SN dute Sales God Cal Num Rew Nm VA Category Employee Sales Goals EMPID EMPO016 Ramal Hample IMPOO Per EMPOO Tror Thema EMO015 Der Wide MOON Chnayam 30 31 34 25 33 30 Employee Analysis Customer Analysis Transactions Shipping Costs Products + Styles F4 xfx714.8455 A B C H 1 M 1 Customer Analysis Member? No No Yes No Yes NO Coupons 10% OFF 15NO 20NON 2 3 Cust 4 CUST6218 5 CUST-508 6 CUST-8820 7 CUST:1340 8 CUST-704 9 CUST-7076 10 CUST-1285 11 CUST-2446 12 CUST-8440 13 CUST-3816 14 CUST-588 15 CUST:3267 16 CUST-5600 17 Customer Name Samud Tapp Arthur Cracker Christine Cardinal John Ketcha Krista Sun Karen Corcoran Doue Vandenburg Oale Foxhoven Sherry Callahan Lula dar Peg Moore Stephanie Raymond Weber Volume TotalSales Value 111 $ 714.RS low 1015 661.61 High 16 $ 569.25 High 19 s 1,338.75 High SS 212.29 Low &$ 168 8S Low sls 275.70 Low 11 1.558.51 6 $ 16187 Low 12 5 2010.44 95 61.32 Low 25 2,15682 High 125 543.75 Low NO NO No Coupon SON ON 15% OFF 20% OFF 15% OFF 10% OFF 10% OF 10NO 15NOT NON OR 20% OFF 10% OFF 15OH 1050 NS No Yes 19 test Customer (most amount spent 20 CD 21 Customer Name 22 Volume 23 TotalSales 24 25 26 27 28 29 30 31 32 33 35 36 39 Employee Analysis Customer Analysis Transactions Shipping Costs Products + P) 1253 Excel Fledermet Foma Tool Data Window Hep BE 3GOT. 000 tel.CHOS Pre. Prothesest Horne Dr Page Layout Fores Data Red View New Tab New Tellime X Cartel ' ' w General S 9 Com . One GID Sort R ter Transaction Analysis TI 171 EL 18 BE 11 ALLA DER WE BB332 100 C 15 30 1 IT DOBU 2 BELIEEE1E12 11 141 7 HE 30 CHE 7 2 3 $ 4 % 5 & 7 1 0 8 9 a W E R T Y U o S D F G H JK N V C c V B N M OW 9 Excel File Edit Format Tools Data View Insert BE Window Help con ahmed Excel Chos Prepare Proshop Sales (0) Autoswe Home New Tab Insert Draw Page Layout Formulas Data Review View New Tab New Tab Tell me Calibri (Body 18 - A A 2 Wrap Tea v General $ % ) Inae BK Dale B TU a. Au Merge Center ai Cenditons om Call Formatting Table Styles Forn A1 x fx Shipping Costs A D F G . 1 K 4 M N P g Shipping costs Moril MMI Zone 2 Zone3 $635 $6.75 $1615 $6.29 $62 $701 $9.29 $853 $824 $461 $8.95 $10 18 Zone 1 $635 $6.35 $6.89 $7.29 $824 $8.95 2anet $6. $6 $7.10 5942 $109 $11.12 Zones 4.50 $6.98 $7.91 $1377 Priority 5.5 Jane Shipping Method Well Zene Shipping Cart $164 $1951 2 3 Weled 5 0.5 6 3 7 3 9 10 10 11 12 13 10 Weight 18 16 2 11 18 19 20 10 21 Zone $24) $742 $123 SA Exprew MIR Zone 2 Zone $76) $15 $767 $8.15 $82 $844 S1024 $11 $10.14 $122 Zone 5823 58 5862 $11 $11.19 $1234 Zone SB SA $9.01 $16.12 S80 $10.24 $2141 21 24 25 21 28 14 Autosave 3 DY. Formulas Data Home New Tab 0000ahmed Excel Chos Prepare Pro Shop Sales (1) View New Tab New Tab Tell me Insert Draw Page Layout Review Calibri (Bodyl 18 - A A General Kindert Paste B I U a.A. 23 Wrap Test Merpes Carta PE $ % ) 23 Cardional Format Cal Formatting Table Slys x Delete 11 Format Al Xfx Product List E F G H 1 1 K M 0 P Product list Q Category Dertion Winchester Wineren Prohanded Club As Shoes Trimble Comforts time wriders Resort Tees Merdivelychan Calle ce dering dong Resort polo shirt 3 m 4349 $ APER 60581011 7C4T23 #058329 1 MRP24 DO TEK11 11 6175157 12 CR19 13 049202 14 CSR11 15 55729 15 3224 17 MT1114 18 74029 19 2323 10 CB 21 39157 22 23 24 25 26 23 20 29 Ret Price 5 49.55 S 419.00 5 12.00 5 49.00 S 59.00 5 2.00 5 5.55 5 15.45 14.95 S 1995 5 113 5 1295 $ 32.95 3295 5 45100 129.00 $ $ 97.00 Clothing Clothing of button shirt pollen wir potensi Wirden pre harded Resort peber Ramal Out Access RO 10 13 11 30 32 29 Employee Analyse Theo oping costs Products + OG