Step Instructions Points Possible 7 5 8 9 5 10 You want to count the number of houses in one city in cell C2, insert the COUNTIF function to count the number of houses in the city of Alpine Use mixed references for the range, and use a relative reference to cell A2 Copy the function and use the Paste Formulas option to paste the function in the range C3 C5 so that the border in cell C5 is preserved You want to calculate the total commissions for each agent (the condition) In cell B7, insert the SUMIF function to total the commissions by agent Use mixed references for the ranges, and use a relative reference to cell A7. Copy the function and use the Paste Formulas option to paste the function in the range B8 B9 so that the borders are preserved The top-middle section of the spreadsheet is designed for summary statistics for multiple conditions. You will calculate the number of houses sold for each agent when he or she served as a Dual Agent (DA), Use mixed references for ranges and the agent code condition in cell 33. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting In cell F2, insert the COUNTIFS function in cell F2 to count the number of houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Use all cell references in the function. Copy the function to the range F3, F4 and preserve the bottom border for cel F4. You are ready to calculate the total value of those houses for each agent when he or she served as a Dual Agent (DA) Use mixed references for ranges and the agent code condition in cell 33. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting In cell G2, insert the SUMIFS function to sum the selling prices of the houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range 63. 64 and preserve the bottom border for cell G4 Now, you will calculate the highest price house highest price house sold for each agent when he or she served as a Dual Agent (DA) Use mixed references for ranges and the agent code condition in cell 3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting In cell H2, insert the MAXIFS function in cell H2 to display the highest-price house sold by the first agent (cell E2) who was a Dual Agent (DA) (JB) for that house. Copy the function to the range H3 H4 and preserve the borders in the range H3 H4 The Map worksheet contains a list of cities, postal codes, and average house sales. You will insert a map chart to depict the averages visually using the default gradient fin colors Display the Map worksheet, select the tange B1-C5 and insert a map chart Cut the map chart and paste it in cell A7. Set a 2.31" height and 3.67" width You want to enter a meaningtur tile for the map Change the map title to Average Selling Price by Zip Code Display the Format Data Serien task pane, select the option to display only regions with data, and show all labels. Close the task pane 11 5 12 13 5 14 2 15 3 16 D E N Number Sold Total Sold Highest Sold Prices Price Total Solid A B Average Sold Location Price 2 Alpine American Fork Cedar Hills Le Total Commission by Agent Coy Hemandez 9 Puan 10 Dual Agent Carey Hemandez Pijuan Agent Codes and Commission Rates BA Buyer's Agent 275% DA Dual Agency 5.50% SA Seller's Agent 3.26% Input for Hones Percent of Ust Days on Market Bonus Amounts 100% 30 1.000 Days on Market Agent Commission Bonus 385.900 Puan 11 Number City Selling Agent Agent Type Agent Type 12 1 Cedar Hila Pijuan DA 13 4 Lely Pijuan DA 14 5 Aminok Pion DA 15 5 Alpine Carey SA 10 7 P BA 12 11 American Fork Hamidez DA 12 Alpina Pijuan DA 19 Alp Carey DA 20 19 American Fork Puu BA 29 Loh SA American Pon SA 27 A DA Lah Handez SA 32 Cedar Hills Carry SA 26 3 Ceam Carey SA 39 Cedar HOA Hemandez DA 28 36 Amanok Hand BA 29 43 American Fork Gary BA 46 Casa His Carey SA 48 Alpine Han DA 55 Leh CHUY SA 61 La Catey DA 69 Alpine Hemandez 10 American Fork Header CHUS Carey 97 25 Alpine Carey 28 Amencan Fork Carey 2 80 American Fork 40 41 42 List Price Sold Price Listing Dato Sale Date $ 125.000 705 000 4/1/2021 6/16-2021 $ 350,799 $ 350 000 4/1/2021 5/25/202 $ 385.900 4/1/2021 4/30/2021 $ 500,000 $ 495,800 4/3/2021 6/10/2021 5 345,999 $ 345,000 42/2021 6/1/2021 $ 325,000 $ 320,000 4/12/2021 9/30/2021 5 750,250 $ 725.900 4/12/202111/1/2021 $ 545,000 $ 643,000 4/10/2021 6/23/2021 5 425,000 $ 415,000 4/18/2021 6/30/2021 $ 325,000 30.000 4/28/2021 6/1/2021 S 425.8155 418.000 4/20/2021 9/15/2021 5 700.000 705.000 4/30/2021 6/19/2021 $ 365.000 363000 51/2021 518 5 895,500 5 800,000 5/1/2021 S 610,000 $ 500,000 1/2021 5 560,200 $ 565.000 $ 450.000 450 DOO 5/2/2021 9/30/2021 5 375.000 372.00 50/202 5/5/202 5 650 000 8253750 5 545.000 S60000 5 315250 120.000 5 300,000 300.000 S 549.000 695,000 5/18/22 1782031 345.6703 345.000 2020/2021 555,000 550 000 9/24/20/2021 475.000 24/12/202 OD DOD 400.000 600217/15/2021 BOBO 365.768 63022221 485.000 45 AL Details LAR AL Location A B D E F G 1 Location Postal Code 2 Alpine 84004 3 American Fork 84003 4 Cedar Hills 84062 5 Lehi 84043 6 7 8 9 10 Average Selling Price $ 601,386 $ 385,794 $ 640,958 $ 334,667 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A1 f Loan (PV) D E G H Principal B C 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 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What the goal is 51 400 monthly payment? 26 Monthly Payment $1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Step Instructions Points Possible 7 5 8 9 5 10 You want to count the number of houses in one city in cell C2, insert the COUNTIF function to count the number of houses in the city of Alpine Use mixed references for the range, and use a relative reference to cell A2 Copy the function and use the Paste Formulas option to paste the function in the range C3 C5 so that the border in cell C5 is preserved You want to calculate the total commissions for each agent (the condition) In cell B7, insert the SUMIF function to total the commissions by agent Use mixed references for the ranges, and use a relative reference to cell A7. Copy the function and use the Paste Formulas option to paste the function in the range B8 B9 so that the borders are preserved The top-middle section of the spreadsheet is designed for summary statistics for multiple conditions. You will calculate the number of houses sold for each agent when he or she served as a Dual Agent (DA), Use mixed references for ranges and the agent code condition in cell 33. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting In cell F2, insert the COUNTIFS function in cell F2 to count the number of houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Use all cell references in the function. Copy the function to the range F3, F4 and preserve the bottom border for cel F4. You are ready to calculate the total value of those houses for each agent when he or she served as a Dual Agent (DA) Use mixed references for ranges and the agent code condition in cell 33. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting In cell G2, insert the SUMIFS function to sum the selling prices of the houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range 63. 64 and preserve the bottom border for cell G4 Now, you will calculate the highest price house highest price house sold for each agent when he or she served as a Dual Agent (DA) Use mixed references for ranges and the agent code condition in cell 3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting In cell H2, insert the MAXIFS function in cell H2 to display the highest-price house sold by the first agent (cell E2) who was a Dual Agent (DA) (JB) for that house. Copy the function to the range H3 H4 and preserve the borders in the range H3 H4 The Map worksheet contains a list of cities, postal codes, and average house sales. You will insert a map chart to depict the averages visually using the default gradient fin colors Display the Map worksheet, select the tange B1-C5 and insert a map chart Cut the map chart and paste it in cell A7. Set a 2.31" height and 3.67" width You want to enter a meaningtur tile for the map Change the map title to Average Selling Price by Zip Code Display the Format Data Serien task pane, select the option to display only regions with data, and show all labels. Close the task pane 11 5 12 13 5 14 2 15 3 16 D E N Number Sold Total Sold Highest Sold Prices Price Total Solid A B Average Sold Location Price 2 Alpine American Fork Cedar Hills Le Total Commission by Agent Coy Hemandez 9 Puan 10 Dual Agent Carey Hemandez Pijuan Agent Codes and Commission Rates BA Buyer's Agent 275% DA Dual Agency 5.50% SA Seller's Agent 3.26% Input for Hones Percent of Ust Days on Market Bonus Amounts 100% 30 1.000 Days on Market Agent Commission Bonus 385.900 Puan 11 Number City Selling Agent Agent Type Agent Type 12 1 Cedar Hila Pijuan DA 13 4 Lely Pijuan DA 14 5 Aminok Pion DA 15 5 Alpine Carey SA 10 7 P BA 12 11 American Fork Hamidez DA 12 Alpina Pijuan DA 19 Alp Carey DA 20 19 American Fork Puu BA 29 Loh SA American Pon SA 27 A DA Lah Handez SA 32 Cedar Hills Carry SA 26 3 Ceam Carey SA 39 Cedar HOA Hemandez DA 28 36 Amanok Hand BA 29 43 American Fork Gary BA 46 Casa His Carey SA 48 Alpine Han DA 55 Leh CHUY SA 61 La Catey DA 69 Alpine Hemandez 10 American Fork Header CHUS Carey 97 25 Alpine Carey 28 Amencan Fork Carey 2 80 American Fork 40 41 42 List Price Sold Price Listing Dato Sale Date $ 125.000 705 000 4/1/2021 6/16-2021 $ 350,799 $ 350 000 4/1/2021 5/25/202 $ 385.900 4/1/2021 4/30/2021 $ 500,000 $ 495,800 4/3/2021 6/10/2021 5 345,999 $ 345,000 42/2021 6/1/2021 $ 325,000 $ 320,000 4/12/2021 9/30/2021 5 750,250 $ 725.900 4/12/202111/1/2021 $ 545,000 $ 643,000 4/10/2021 6/23/2021 5 425,000 $ 415,000 4/18/2021 6/30/2021 $ 325,000 30.000 4/28/2021 6/1/2021 S 425.8155 418.000 4/20/2021 9/15/2021 5 700.000 705.000 4/30/2021 6/19/2021 $ 365.000 363000 51/2021 518 5 895,500 5 800,000 5/1/2021 S 610,000 $ 500,000 1/2021 5 560,200 $ 565.000 $ 450.000 450 DOO 5/2/2021 9/30/2021 5 375.000 372.00 50/202 5/5/202 5 650 000 8253750 5 545.000 S60000 5 315250 120.000 5 300,000 300.000 S 549.000 695,000 5/18/22 1782031 345.6703 345.000 2020/2021 555,000 550 000 9/24/20/2021 475.000 24/12/202 OD DOD 400.000 600217/15/2021 BOBO 365.768 63022221 485.000 45 AL Details LAR AL Location A B D E F G 1 Location Postal Code 2 Alpine 84004 3 American Fork 84003 4 Cedar Hills 84062 5 Lehi 84043 6 7 8 9 10 Average Selling Price $ 601,386 $ 385,794 $ 640,958 $ 334,667 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A1 f Loan (PV) D E G H Principal B C 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 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What the goal is 51 400 monthly payment? 26 Monthly Payment $1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 32 33 34 35 36 37 38 39 40 41 42