12 13 Import the tab delimited text file, ex02 TransactionData.txt, into the Client Transactions worksheet, starting in cell A2. Apply the Table Style Light 14 to the range A2:K58, removing the connection to the text file. On the Client Transactions worksheet, in cell 13, use the VLOOKUP function to retrieve the per ticket cost for each transaction based on the trip city from the data on the Travel Packages worksheet multiplied by the number of guests. Format as Accounting with 2 decimals. 5 LO 6 14 In cell J3, use a VLOOKUP function to determine the discount earned based on the cost of trip and the data on the Agency Data and Analysis worksheet. Format as Percentage with 1 decimal place. 6 15 In cell K3, calculate the cost of the trip after the discount has been applied. Format as Accounting with 2 decimals. 4 16 On the Agency Data and Analysis worksheet, in cell E3, use a SUMIF function to calculate the commission earned for Jonas Lamar, using the data on the Travel Packages worksheet and the commission rate in cell D3. Autofill the formula down through E5. 5 17 In cell D8, use the COUNTIF function to calculate the number of transactions that fell below average (below $4,000), in the Total Spent name 3.5 d range. 18 In cell D9, use the COUNTIFS function to calculate the number of transactions that fell within the average (between $4,000 and $5,000), in the Total Spent named range. 5 19 In cell D10, use the COUNTIF function to calculate the number of transactions that fell above average (above $5,000), in the Total Spent named range. 20 20 Modify the Advanced filter on the Agency Clients worksheet to only show records with a total spent of greater than $5.000 from agent Charlie. Copy and paste the results of the filter onto 3.5 4