Can someone pretty please show me how to do the following excel steps?

2 5 The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer's Agent, Dual Agent, Seller's Agent). You want to switch the codes for the actual descriptions. In cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cell D12. Include mixed cell references to the codes and roles in the range J2:54 for the values and results arguments. use all cell references in the function. Copy the function to the range E13:E39. 3 5 Now you want to calculate the number of days between the list date and sale date. In cell J12, insert the DAYS function to calculate the number of days between the Listing Date and the Sale Date. Copy the function to the range J13:339. You want to calculate agent commissions based on their role. In cell K12, insert the IFS function to calculate the agent's commission based on the agent code and the applicable rates in the range L2:L4. Use relative and mixed references correctly. Copy the function to the range K13:K39. 4 5 5 5 You want to calculate a bonus if the sold price was at least equal to the listing price, and if the house sold within 30 days after being listed. In cell L 12, insert an IF function with a nested AND function to calculate a bonus. The AND function should ensure both conditions are met: Sold Price divided by the Listing Price is greater than or equal to 100% (cell L7) and the Days on Market are less than or equal to 30 (cell L8). If both conditions are met, the bonus is $1,000 (cell L9). Otherwise, the bonus is $0. Use mixed cell references to the input values in the range L7:L9. Copy the function to the range L12:L39. 6 5 The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition) In cell B2, insert the AVERAGEIF function to calculate the average Sold Price for houses in the city of Alpine. Use mixed references for the range, use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range B3:B5 so that the bottom border in cell B5 is preserved. L6 Jx F 1 J K G H Total Sold Highest Sold Prices Price Total # Sold Agent Codes and Commission Rates | Buyer's Agent 2.75% DA 5.50% SA Seller's Agent 3.25% Dual Agency Input for Bonus Percent of List Days on Market Bonus Amount $ 100% 30 1,000 Days on Market Agent Commission Bonus . B D E Average Sold 1 Location Price Number Sold Dual Agent 2 Alpine Carey 3 American Fork Hernandez 4 Cedar Hills Pijuan 5 Lehi 6 Total Commission by Agent 7 Carey 8 Hernandez 9 Pijuan 10 11 Number City Selling Agent Agent Type Agent Type 12 1 Cedar Hills Pijuan DA 13 4 Lehi Pijuan DA 14 5 American Fork Pijuan DA 15 6 Alpine Carey SA 16 7 Lehi Pijuan BA 17 11 American Fork Hernandez DA 18 12 Alpine Pijuan DA 19 17 Alpine Carey DA 20 19 American Fork Pijuan BA 21 21 Lehi Pijuan SA 22 24 American Fork Pijuan SA 23 27 Alpine Pijuan DA 24 30 Lehi Hernandez SA 25 32 Cedar Hills Carey SA 26 34 Cedar Hills Carey SA 27 35 Cedar Hills Hernandez DA 28 36 American Fork Hernandez BA 20 American Cock DA Lo Price SA List Price Sold Price Listing Date Sale Date $ 725,000 $ 705,000 4/1/2021 6/16/2021 $ 350,799 $ 350,000 4/1/2021 5/25/2021 $ 385,900 $ 385,900 4/1/2021 4/30/2021 $ 500,000 $ 495,800 4/3/2021 6/10/2021 $ 345,999 $ 345.000 4/3/2021 6/1/2021 $ 325,000 $ 320,000 4/12/2021 5/30/2021 $ 750,250 $ 725,900 4/12/2021 11/1/2021 $ 645,000 $ 643,000 4/18/2021 6/23/2021 $ 425,000 $ 415,000 4/18/2021 6/30/2021 $ 325,000 $ 330,000 4/28/2021 6/1/2021 $ 425,815 $ 418,000 4/28/2021 9/15/2021 $ 700,000 $ 705,000 4/30/2021 6/19/2021 $ 365,000 $ 363,000 5/1/2021 6/18/2021 $ 885,500 $ 800,000 5/1/2021 6/6/2021 S 610,000 $ 600,000 5/1/2021 6/26/2021 $ 560,700 $ 565,000 5/1/2021 5/30/2021 $ 450,000 $ 450,000 5/2/2021 9/30/2021 270 con CICI021 C/10/2021 4 AP P 11 Pot 21 C } Snip sa Select 42 27C an 1 . B D E F Average Selling Price $ 601,386 $ 385,794 $ 640,958 $ 334,667 1 Location Postal Code 2 Alpine 84004 3 American Fork 84003 4 Cedar Hills 84062 5 Lehi 84043 6 7 8 9 10 11 12 D E F Principal B 1 Loan (PV) $300,000.00 2 APR 4.50% 3 Monthly Rate 0.38% 4 NPER 360 5 Monthly Payment $1,520.06 6 7 Payment Number Beg Balance Interest 8 1 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 9 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What if the goal is $1,400 monthly payment? 26 Monthly Payment $ 1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 2 5 The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer's Agent, Dual Agent, Seller's Agent). You want to switch the codes for the actual descriptions. In cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cell D12. Include mixed cell references to the codes and roles in the range J2:54 for the values and results arguments. use all cell references in the function. Copy the function to the range E13:E39. 3 5 Now you want to calculate the number of days between the list date and sale date. In cell J12, insert the DAYS function to calculate the number of days between the Listing Date and the Sale Date. Copy the function to the range J13:339. You want to calculate agent commissions based on their role. In cell K12, insert the IFS function to calculate the agent's commission based on the agent code and the applicable rates in the range L2:L4. Use relative and mixed references correctly. Copy the function to the range K13:K39. 4 5 5 5 You want to calculate a bonus if the sold price was at least equal to the listing price, and if the house sold within 30 days after being listed. In cell L 12, insert an IF function with a nested AND function to calculate a bonus. The AND function should ensure both conditions are met: Sold Price divided by the Listing Price is greater than or equal to 100% (cell L7) and the Days on Market are less than or equal to 30 (cell L8). If both conditions are met, the bonus is $1,000 (cell L9). Otherwise, the bonus is $0. Use mixed cell references to the input values in the range L7:L9. Copy the function to the range L12:L39. 6 5 The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition) In cell B2, insert the AVERAGEIF function to calculate the average Sold Price for houses in the city of Alpine. Use mixed references for the range, use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range B3:B5 so that the bottom border in cell B5 is preserved. L6 Jx F 1 J K G H Total Sold Highest Sold Prices Price Total # Sold Agent Codes and Commission Rates | Buyer's Agent 2.75% DA 5.50% SA Seller's Agent 3.25% Dual Agency Input for Bonus Percent of List Days on Market Bonus Amount $ 100% 30 1,000 Days on Market Agent Commission Bonus . B D E Average Sold 1 Location Price Number Sold Dual Agent 2 Alpine Carey 3 American Fork Hernandez 4 Cedar Hills Pijuan 5 Lehi 6 Total Commission by Agent 7 Carey 8 Hernandez 9 Pijuan 10 11 Number City Selling Agent Agent Type Agent Type 12 1 Cedar Hills Pijuan DA 13 4 Lehi Pijuan DA 14 5 American Fork Pijuan DA 15 6 Alpine Carey SA 16 7 Lehi Pijuan BA 17 11 American Fork Hernandez DA 18 12 Alpine Pijuan DA 19 17 Alpine Carey DA 20 19 American Fork Pijuan BA 21 21 Lehi Pijuan SA 22 24 American Fork Pijuan SA 23 27 Alpine Pijuan DA 24 30 Lehi Hernandez SA 25 32 Cedar Hills Carey SA 26 34 Cedar Hills Carey SA 27 35 Cedar Hills Hernandez DA 28 36 American Fork Hernandez BA 20 American Cock DA Lo Price SA List Price Sold Price Listing Date Sale Date $ 725,000 $ 705,000 4/1/2021 6/16/2021 $ 350,799 $ 350,000 4/1/2021 5/25/2021 $ 385,900 $ 385,900 4/1/2021 4/30/2021 $ 500,000 $ 495,800 4/3/2021 6/10/2021 $ 345,999 $ 345.000 4/3/2021 6/1/2021 $ 325,000 $ 320,000 4/12/2021 5/30/2021 $ 750,250 $ 725,900 4/12/2021 11/1/2021 $ 645,000 $ 643,000 4/18/2021 6/23/2021 $ 425,000 $ 415,000 4/18/2021 6/30/2021 $ 325,000 $ 330,000 4/28/2021 6/1/2021 $ 425,815 $ 418,000 4/28/2021 9/15/2021 $ 700,000 $ 705,000 4/30/2021 6/19/2021 $ 365,000 $ 363,000 5/1/2021 6/18/2021 $ 885,500 $ 800,000 5/1/2021 6/6/2021 S 610,000 $ 600,000 5/1/2021 6/26/2021 $ 560,700 $ 565,000 5/1/2021 5/30/2021 $ 450,000 $ 450,000 5/2/2021 9/30/2021 270 con CICI021 C/10/2021 4 AP P 11 Pot 21 C } Snip sa Select 42 27C an 1 . B D E F Average Selling Price $ 601,386 $ 385,794 $ 640,958 $ 334,667 1 Location Postal Code 2 Alpine 84004 3 American Fork 84003 4 Cedar Hills 84062 5 Lehi 84043 6 7 8 9 10 11 12 D E F Principal B 1 Loan (PV) $300,000.00 2 APR 4.50% 3 Monthly Rate 0.38% 4 NPER 360 5 Monthly Payment $1,520.06 6 7 Payment Number Beg Balance Interest 8 1 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 9 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What if the goal is $1,400 monthly payment? 26 Monthly Payment $ 1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31