3. Use Consolidate to create a summary of real estate sales for all property types. Place the consolidated data on the Summary worksheet in cells B3:C15. Use the Average function to consolidate the data from cells B2:C14 in the Condo, Townhouse, and Single Family worksheets. Include links to the source data. 4. Import individual sales data from the tab-delimited text file Select Sales Data 2019-2020. Import the data as a table in cell A1 of the Select Sales worksheet. NOTE: If you are unable to find the Select Sales Data 2019-2020 text file or are unable to complete this step due to your school's computer lab restrictions, download the file from the Resources link. Open the file and continue to complete step. 5. 6. Remove the data connection when the import is complete. In the Select Sales worksheet, add data validation to restrict values in the House Type columns (cells B2:B139) to the values in the cell range named Property Types. Include a drop-down list of values in the cells to assist in data correction. 7. Add the following input message to cells B2:B139: Select a property type from the list. 8. Add the following error alert to cells B2:B139: Not a valid property type. 9. Apply data validation circles to any values that violate the data validation rules you just created. 10. Add a comment to cell B1 in the Select Sales worksheet that reads: This needs to be fixed. 11. Add a hyperlink from cell B1 in the Select Sales worksheet to link to the named range (defined name) Property Types. Test the link. 12. Use Flash Fill to add a new column of data to the Select Sales worksheet to display the number of bedrooms/the number of bathrooms. a. In the Select Sales worksheet, cell F1, type the heading: BR/BA b. Complete the column of data using this pattern in cell F2: 1 BR/1 BA You will need to enter at least two samples in order for Flash Fill to suggest the correct autofill data. Check your work carefully. 13. Apply worksheet protection to the Select Sales worksheet so users can only select cells, not edit them. Do not unlock any cells. 14. 15. 16. Check for any possible compatibility issues in this workbook. Mac users: Skip this step and move on to Step 15. Check for accessibility issues and fix the merged cells issue found. Mark the workbook as final. Mac users: Skip this step and move on to Step 17. 17.Save and close the workbook. 18. Upload and save your project file. 19. Submit project for grading. 1 B1 Clipboard A Font fx Sales Summary 2019-2020 B C Sales Summary 2019-2020 2 Number of Sales 3 One BR, One BA 4 One BR, Two BA + 5 Two BR, One BA 6 Two BR, Two BA 7 Two BR, Two BA + 8 Three BR, One BA 9 Three BR, Two BA 10 Three BR, Three BA + 11 Four BR, One BA 12 Four BR, Two BA 13 Four BR, Three BA+ 14 Five BR+, One BA 15 Five BR+, Two BA+ 16 17 Average Selling Price Alignment D E F G H 1 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 42 Summary Condo Townhouse Single Family Select Sales Property Types + Nu B1 A fx Number of Sales B Number of Sales Average Selling Price 2 1 BR, 1 BA 14 $ 332,067 3 1 BR, 2 BA + 19 $ 306,861 4 2 BR, 1 BA 0 $ 383,199 5 2 BR, 2 BA 2 $ 316,284 6 2 BR, 2 BA+ 11 $ 165,769 7 3 BR, 1 BA 1 $ 181,726 8 3 BR, 2 BA 7 $ 211,902 9 3 BR, 3 BA + 6 $ 254,679 10 4 BR, 1 BA 16 $ 354,925 11 4 BR, 2 BA 13 $ 303,992 12 4 BR, 3 BA+ 9 $ 390,899 13 5 BR+, 1 BA 13 $ 274,979 14 5 BR+, 2 BA+ 4 $ 238,278 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 D E F G H J K L Summary Condo Townhouse Single Family Select Sales Property Types + Ready E5 A x fx B C D E F G H J K L 1 Number of Sales Average Selling Price 2 1 BR, 1 BA 5 $ 430,872 3 1 BR, 2 BA+ 10 $ 482,954 4 2 BR, 1 BA 2 $ 479,896 5 2 BR, 2 BA 2 $ 304,878 6 2 BR, 2 BA + 3 $ 287,546 7 3 BR, 1 BA 17 $ 509,124 8 3 BR, 2 BA 6 $ 405,788 9 3 BR, 3 BA + 2 $ 317,397 10 4 BR, 1 BA 16 $ 340,085 11 4 BR, 2 BA 13 $ 401,437 12 4 BR, 3 BA+ 17 $ 372,143 13 5 BR+, 1 BA 19 $ 560,943 14 5 BR+, 2 BA+ 13 $ 556,431 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Summary Condo Townhouse Single Family Select Sales Property Types + Alignment D E F H J K D7 Clipboard A 1 2 1 BR, 1 BA 3 1 BR, 2 BA + 4 2 BR, 1 BA 5 2 BR, 2 BA B fx Font Number of Sales Average Selling Price 14 $ 10 $ 12 $ 9 $ 16 $ 8 $ Th 8 $ 368,210 525,477 282,077 519,800 554,020 342,218 345,351 286,947 466,560 13 $ 263,587 4 $ 399,456 3 5 BR+, 1 BA 10 $ 19 $ 271,373 548,163 62 BR, 2 BA + 73 BR, 1 BA 8 3 BR, 2 BA 93 BR, 3 BA + 04 BR, 1 BA 14 BR, 2 BA 2 4 BR, 3 BA+ 13 $ 15 $ 4 5 BR+, 2 BA+ 5 6 7 8 9 20 21 22 23 24 25 26 27 28 29 30 $1 2 3 34 35 36 7 38 9 0 1 2 3 Summary Condo Townhouse Single Family Select Sales Property Types + Ready H30 A 1 2 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 fx B C D E F G H J K L M N Summary Condo Townhouse Single Family Select Sales Property Types Ready + Property... A Property Types 1 2 Condo 3 Townhouse 4 Single Family 5 Multi-Unit 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29. 30 31 32 33 34 35 36 37 38 39 40 41 42 43 fx Condo B D F G H K M N Summary Condo Townhouse Single Family Select Sales Property Types + Ready