Question

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. 7 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:05 so that the border in cell C5 is preserved.

Select Draw with Touch Ink to Ink to Shape Math Ink Replay Replay input Mode Drawing Tools Convert B2 С D E F 1 J M G Total Sold Prices Number Sold Total Sold Highest Sold Price А A B Average Sold 1 Location Price 2 Alpine 3 American Fork 4 Cedar Hills 5 Lei 6 Total Commission by Agent 7 Carey B Hernandez 9 Puan 10 Dual Agent Carey Hernandez Pijuan Agent Codes and Commission Rates |ВА Buyer's Agent 2.75% DA Dual Agency 5 509 SA Seller's Agent 3.25% Input for Bonus Percent of List Days on Market Bonus Amount $ 100% 30 1000 11 12 13 15 16 17 18 19 20 Number City 1 Cedar Hills 4 Lehi 5 American Fork 6 Alpine 7 LeN 11 American Fork 12 Alpine 17 Alpine 19 American Fork 21 Lei 24 American Fork 27 Anina Details Map Loan Selling Agent Agent Type Agent Type Pjuan DA Dual Agency Pijuan DA Dual Agency Pijuan DA Dual Agency Carey SA Seller's Agent Puan BA Buyer's Agent Hernandez DA Dual Agency Puan DA Dual Agency Carey DA Dual Agency Pjuan BA Buyer's Agent Pijuan SA Seller's Agent Puan SA Seller's Agent Dician NA Dual Anne List Price Sold Price 725,000 $ 705,000 $ 350.799 $ 350,000 $ 385,900 $ 385.900 $ 500,000 $ 495,800 $ 345,999 $ 345,000 $ 325,000 $ 320,000 $ 750,250 S 725 900 S 645.000 643.000 $ 425,000 $ 415,000 $ 325,000 $ 330,000 $ 425,815$ 418,000 700 000 $ 705 non Listing Date Sale Date 2021/4/1 2021/6/16 2021/4/1 2021/5/25 2021/4/1 2021/4/30 2021/4/3 2021/6/10 2021/4/3 2021/6/1 2021/4/12 2021/5/30 2021/4/12 2021/11/1 2021/4/18 2021/6/23 2021/4/18 2021/6/30 2021/4/28 2021/6/1 2021/4/28 2021/9/15 2021/4/2017/10 Days on Agent Market Bonus Commission 76 5 38,775.00 $ 54 5 19,250.00 $ 20$ 21,224.50 $ 1,000 685 16,113.50 $ 50 $ 9,487 50 5 48 S 17,600.00 $ 2035 39,924 50 $ 66 $ 35,365.00 $ 735 11,412.50 $ 34 $ 10.725.00 $ 140 $ 13,585.00 $ HA 7750 - 22 3

louch Shape Math Mode Drawing Tools Replay Replay Convert f A 7 11 12 17 19 21 24 27 30 32 34 35 36 43 46 48 53 61 69 70 73 75 78 80 B Lehi American Fork Alpine Alpine American Fork Lehi American Fork Alpine Lehi Cedar Hills Cedar Hills Cedar Hills American Fork American Fork Cedar Hills Alpine Lehi Lehi Alpine American Fork Cedar Hills Alpine American Fork American Fork С Pjuan Hernandez Pjuan Carey Pijuan Pijuan Pijuan Pijuan Hernandez Carey Carey Hernandez Hernandez Carey Carey Hernandez Carey Carey Hernandez Hernandez Carey Carey Carey Pijuan D BA DA DA DA BA SA SA DA SA SA SA DA BA BA SA DA SA DA DA DA BA BA DA SA Buyer's Agent Dual Agency Dual Agency Dual Agency Buyer's Agent Seller's Agent Seller's Agent Dual Agency Seller's Agent Seller's Agent Seller's Agent Dual Agency Buyer's Agent Buyer's Agent Seller's Agent Dual Agency Seller's Agent Dual Agency Dual Agency Dual Agency Buyer's Agent Buyer's Agent Dual Agency Seller's Agent $ S $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ F 345,999 $ 325,000 $ 750,250 $ 645,000 $ 425,000 $ 325,000 $ 425,815 S 700,000 $ 365,000 $ 885,500 $ 610,000 $ 560,700 $ 450,000 $ 375,000 $ 650,000 $ 545,000 $ 315,750 $ 300,000 $ 589.000 $ 345,670 $ 555,000 $ 475,000 $ 400,000 $ 380.500 5 G 345,000 320.000 725,900 643,000 415,000 330,000 418,000 705,000 363,000 800,000 600,000 565,000 450,000 372,500 625,750 560,000 320,000 300,000 595,000 345,000 550,000 485.000 400,000 365.750 H 2021/4/3 2021/6/1 2021/4/12 2021/5/30 2021/4/12 2021/11/1 2021/4/18 2021/6/23 2021/4/18 2021/6/30 2021/4/28 2021/6/1 2021/4/28 2021/9/15 2021/4/30 2021/6/19 2021/5/1 2021/6/18 2021/5/1 2021/6/6 2021/5/1 2021/6/26 2021/5/1 2021/5/30 2021/5/2 2021/9/30 2021/5/6 2021/5/15 2021/5/15 2021/6/9 2021/5/22 2021/6/16 2021/5/28 2021/6/18 2021/6/2 2021/9/2 2021/6/18 2021/7/8 2021/6/20 2021/9/30 2021/6/24 2021/6/30 2021/6/24 2021/9/18 2021/6/30 2021/7/15 2021/6/30 2021/9/1 59 $ 48 $ 203 $ 66 $ 73 $ 34 $ 140 $ 50 $ 48 $ 36 $ 56 S 29 $ 151 $ 9 $ 25 $ 25 $ 21 $ 92 $ 20 $ 102 S 6 $ 86 $ 15 $ 63 $ K 9,487.50 $ 17,600.00 $ 39,924.50 $ 35 365.00 $ 11,412.50 $ 10,725.00 $ 13,585.00 $ 38,775.00 $ 11.797.50 $ 26,000.00 $ 19,500.00 $ 31.075.00 $ 1.000 12.375.00 $ 10,243.75 5 20,336.88 $ 30,800.00 $+ 1,000 10,400.00 $ 1,000 16,500.00 $ 32,725.00 $ 1,000 18,975.00 $ 15,125.00 $ 13,337.50 $ 22,000.00 $ 1,000 11,886 88 $ Details Map Loan 在这里输入你要搜索的内容 G 3 dx

Answer 1

1)formula

=AVERAGEIF(B12:B46,$A2,G12:G46)

If we copy the formula by tapping at bottom-right of vertex of cell then it automatically sets relative reference so to make absolute reference we use $ sign.In Criteria ,$A is absolute reference and 2 is relative reference so it is called mixed reference.

AVERAGEIF(Range of criteria,criteria which we want, range of average)

This function returns average of range for which criteria which is given

2)formula

=COUNTIF(B12:B46,$A2)

COUNTIF(Range of criteria, Criteria)

This function returns counting values of range for which criteria which is given