Problem 3. The Transportation Security Administration (TSA) has assembled a random list of 40 ft containers passing through 5 US East Coast container ports- Baltimore (Bal), New York/New Jersey (NY-NU), Charleston (Chisn), Jacksonville (xsvl). They are identified by weight in Metric tons (Tonnes), shipper (YK, EG, M. CSCL), and delivery port. Each observation represents manifest information of a vessel call designated for further examined by a forensic group at TSA Perform a multi-level sort of the list (not including the "OBS column) by "Shipper" (A-2). "Port" (A-2), and "# of 40 ft Cntrs (Smallest-Largest). Complete the sort in the order provided ***Caution: Save a copy of the original data. After a sort in a) you will need to return to the original data." a) What is the Tonnes value for the new 18th OBS (remember to not include OBS in the sort)? b) Return to the original unsorted data. Create a Custom Sort for shippers that sorts in the following Custom order--NYK, EG, CSCL, and M (just shippers, not also tonnes). What is the 45th Oss value for Tonnes? (hint: Create a "custom list" under the "order" tab in "custom sort tool. Also, remember to not include oss in c) Which of the following formulas will allow you to accurately count the simultaneous occurrence of records with VA in the Port column and M in the Shipper column of the original Port data? Assume the Port Data is located in the upper left-hand corner of a worksheet (OBS is in cell A1): W COUNTIFS(D2:079,"M", E2:E79,"VA") X-COUNTIF(E2.879,VA")+COUNTIF(02:079, "M") y both W) and X Z) none of the above the sort) I FS NYK OBS Fof 40 Onth Tonnes 1 31,756 1,182,664 2 $2,275 398,361 3 34,168 1.390,618 4 31,385 1.601.501 5 42,800 483,710 6 49.925 1,165,691 7 51070 343,632 8 18.729 491,013 109 23,690 1,372,957 11 10 26,611 1,368,792 11 31,697 448,773 11 12 39.751 976,205 1013 16,511 1,221,523 13,819 1.156,106 16 15 24.572 1,517,366 12 16 31,148 794,388 17 44,549 484,601 1918 40,021 595,983 2019 38.259 993,594 21 20 49,007 525.653 22 21 34,989 1.276,407 222 50,705 1.462.923 2 23 30,810 513,322 25 24 36,040 1,017,254 2 25 47,100 1,037,689 22 25 15,113 396,621 21 27 36,349 041,083 29 28 45,615 1,193,597 29 53,581 978,729 30 40,924 1,304,651 32 31 45,333 35 32 28.397 701,044 34 33 45.455 948,631 15 34 52,598 1,322,153 M35 50,531 574,556 37 38 41,645 630,723 5037 50,992 1,333,123 1938 13,352 1,364.922 39 31.791 393,791 43 40 27.606 1.489,263 4241 20,126 700.998 a 42 38,946 1,346,607 40 43 17,808 1,010,026 45 44 52,061 1,357,886 45 38.221 424,523 46 32.685 728,821 47 632,092 1946 21,625 1,417,378 49 29,010 1.119.654 50 32.342 579,068 50 31 16.146 467.766 115 50,60) 527,846 53 1.214.96 39,660 54 34,962 1.516.654 15 26,249 103.91 19,647 1,050.945 50 1.47.592 33,302 029.233 2.400 18 1,531,32 1993 14 45.79 1403,745 30.733 27.495 GOP063 64 7.7oz 65 11.00 1.000,190 53,672 16 40.01 SOR 657141 27140 225136 70 70 TI 19.000 1465.000 374191 12 104.76 73 34479 165,80 74 1512 430,415 107 1.412.216 26 26,549 22 303,240 2005 443.214 Shipper Port EG NY- NYK NYK NY-NU NYK Chi VA EG Chin NYK VA CSC VA EG VA M NYNU EG NYNU NYK NYU NYK VA NIK VA EG NY NU M NY-NU NYK Chin M Con NYK NY-AU NYK VA NYK NYK NYU M VA M Jav EG Chian EG NYNU EG VA NYK NYNU NYK NY NU M NY NU EG Chian NYK M VA EG NYEN EG Ba EG NYU NYK NY-NUU NYK VA csa NY-NU EG VA NYK CSC NYN NYK VA NYS NY-NU NYK Chian LG NIK Chian NIK N.NU EG EG NYNU G G EA NYENU EG Chion LG NYN NIK VA NIK NY-NU NYX Hd Chan NYU 10 NY-NU NYX Chan NE VA NIK VA NYE NY NO NY NIE NYU 10 NE NY NYN G M NIK PE 6 410 2240 090 19 19 1400. 9 29 1176.400 69 49.17 44,826 SEN NY H CS NE AN IN INAN A WA Problem 3. The Transportation Security Administration (TSA) has assembled a random list of 40 ft containers passing through 5 US East Coast container ports- Baltimore (Bal), New York/New Jersey (NY-NU), Charleston (Chisn), Jacksonville (xsvl). They are identified by weight in Metric tons (Tonnes), shipper (YK, EG, M. CSCL), and delivery port. Each observation represents manifest information of a vessel call designated for further examined by a forensic group at TSA Perform a multi-level sort of the list (not including the "OBS column) by "Shipper" (A-2). "Port" (A-2), and "# of 40 ft Cntrs (Smallest-Largest). Complete the sort in the order provided ***Caution: Save a copy of the original data. After a sort in a) you will need to return to the original data." a) What is the Tonnes value for the new 18th OBS (remember to not include OBS in the sort)? b) Return to the original unsorted data. Create a Custom Sort for shippers that sorts in the following Custom order--NYK, EG, CSCL, and M (just shippers, not also tonnes). What is the 45th Oss value for Tonnes? (hint: Create a "custom list" under the "order" tab in "custom sort tool. Also, remember to not include oss in c) Which of the following formulas will allow you to accurately count the simultaneous occurrence of records with VA in the Port column and M in the Shipper column of the original Port data? Assume the Port Data is located in the upper left-hand corner of a worksheet (OBS is in cell A1): W COUNTIFS(D2:079,"M", E2:E79,"VA") X-COUNTIF(E2.879,VA")+COUNTIF(02:079, "M") y both W) and X Z) none of the above the sort) I FS NYK OBS Fof 40 Onth Tonnes 1 31,756 1,182,664 2 $2,275 398,361 3 34,168 1.390,618 4 31,385 1.601.501 5 42,800 483,710 6 49.925 1,165,691 7 51070 343,632 8 18.729 491,013 109 23,690 1,372,957 11 10 26,611 1,368,792 11 31,697 448,773 11 12 39.751 976,205 1013 16,511 1,221,523 13,819 1.156,106 16 15 24.572 1,517,366 12 16 31,148 794,388 17 44,549 484,601 1918 40,021 595,983 2019 38.259 993,594 21 20 49,007 525.653 22 21 34,989 1.276,407 222 50,705 1.462.923 2 23 30,810 513,322 25 24 36,040 1,017,254 2 25 47,100 1,037,689 22 25 15,113 396,621 21 27 36,349 041,083 29 28 45,615 1,193,597 29 53,581 978,729 30 40,924 1,304,651 32 31 45,333 35 32 28.397 701,044 34 33 45.455 948,631 15 34 52,598 1,322,153 M35 50,531 574,556 37 38 41,645 630,723 5037 50,992 1,333,123 1938 13,352 1,364.922 39 31.791 393,791 43 40 27.606 1.489,263 4241 20,126 700.998 a 42 38,946 1,346,607 40 43 17,808 1,010,026 45 44 52,061 1,357,886 45 38.221 424,523 46 32.685 728,821 47 632,092 1946 21,625 1,417,378 49 29,010 1.119.654 50 32.342 579,068 50 31 16.146 467.766 115 50,60) 527,846 53 1.214.96 39,660 54 34,962 1.516.654 15 26,249 103.91 19,647 1,050.945 50 1.47.592 33,302 029.233 2.400 18 1,531,32 1993 14 45.79 1403,745 30.733 27.495 GOP063 64 7.7oz 65 11.00 1.000,190 53,672 16 40.01 SOR 657141 27140 225136 70 70 TI 19.000 1465.000 374191 12 104.76 73 34479 165,80 74 1512 430,415 107 1.412.216 26 26,549 22 303,240 2005 443.214 Shipper Port EG NY- NYK NYK NY-NU NYK Chi VA EG Chin NYK VA CSC VA EG VA M NYNU EG NYNU NYK NYU NYK VA NIK VA EG NY NU M NY-NU NYK Chin M Con NYK NY-AU NYK VA NYK NYK NYU M VA M Jav EG Chian EG NYNU EG VA NYK NYNU NYK NY NU M NY NU EG Chian NYK M VA EG NYEN EG Ba EG NYU NYK NY-NUU NYK VA csa NY-NU EG VA NYK CSC NYN NYK VA NYS NY-NU NYK Chian LG NIK Chian NIK N.NU EG EG NYNU G G EA NYENU EG Chion LG NYN NIK VA NIK NY-NU NYX Hd Chan NYU 10 NY-NU NYX Chan NE VA NIK VA NYE NY NO NY NIE NYU 10 NE NY NYN G M NIK PE 6 410 2240 090 19 19 1400. 9 29 1176.400 69 49.17 44,826 SEN NY H CS NE AN IN INAN A WA