ComputerScienceExpert
$18/per page/
I am currently working on an Excel project. I am confused on  Subtotal  Feature to Calculate the average selling price. I will attached file. Both the instructions, and the excel speadsheet.
Â
Ofce 2013 – myitlab:grader – InstructionsExcel ProjectEXP ECH05 H2 - Mountain View Realty 2.1Project Description:You are a real estate analyst who works for Mountain View Realty in the North Utah County area.You have consolidated a list of houses sold during the past few months and need to start analyzingthe data. For a simple analysis, you will outline the data and use the Subtotal feature. Then you willcreate a PivotTable to give you a way to perform more in-depth analysis.Instructions:For the purpose o± grading the project you are required to per±orm the ±ollowing tasks:StepInstructionsPointsPossible1Openexploring_e05_grader_h2_RealEstate.xlsxand save it asexploring_e05_grader_h2_RealEstate_LastFirst.0.0002Make sure the Sales Subtotals worksheet is the active sheet. Insert a column between the Selling Priceand Listing Date columns. Enter the heading% of Asking Priceand double-click between the columnG and H headings to increase the column width.2.0003Insert a formula in cell G2 to calculate the selling price percentage of the asking price, format it withPercent Style with one decimal place, and then copy the formula down the column.8.0004Enter the headingDays on Marketon the right side of the last column and double-click between thecolumn J and K headings to increase the column width. In cell J2, calculate the number of days betweenthe listing date and sale date. Copy the formula down the column.8.0005Sort the list by city in alphabetical order, then by selling agent in alphabetical order, and finally bylisting date in chronological order (oldest to newest).9.0006Use the Subtotal feature to calculate the average selling price, percentage of asking price, and days onmarket by city. Ensure that Summary below data is selected, and then click OK. Group the data usingthe Auto Outline feature. Collapse the outline to hide the listing and sale dates. Click the appropriatebutton to display the grand average and city average rows only.16.0007Format the average days on market to zero decimal places. Apply wrap text, 10.00 column width, andincrease the (row) height of cells G1 and J1 to38.25.8.0008Click the Sales Data worksheet and create a PivotTable on a new worksheet. Name the new worksheetPivotTable.10.0009Display the cities in the Rows area of the PivotTable, selling agents in the Columns area, and asking andselling prices (in that order) in the Values area. Modify the PivotTable. Display averages rather thansums with Accounting Number Format with zero decimal places. Pivot the data by placing the City fieldin the Columns area (above Values) and the Selling Agent field in the Rows area.9.00010Add a group filter to display onlyAlpineandCedar Hills.8.000Updated: 01/21/20151Current_Instruction.docx
NumberAddressCitySelling AgentAsking PriceSelling Price4881 Island Drive SouthAlpineBethune$147,800 $150,00067 Kingston CourtAlpineCarey$500,000 $465,00012575 South ChoctawAlpineGoodrich$750,250 $700,00040401 Pinecone CircleAlpineGoodrich$1,500,120 $1,400,00075876 West HolidayAlpineGoodrich$475,000 $455,00091370 Pinellas RoadAlpineLugo$219,000 $215,000168030 Steeplechase DriveAlpineMerkin$450,000 $382,500172006 Cutwater CourtAlpineMinkus$345,000 $339,999204081 Lybyer AvenueAlpineMinkus$325,000 $308,75065520 E Spring StreetAlpineMinkus$189,900 $186,102698923 Harris DriveAlpineMinkus$589,000 $575,00027224 Rockaway StreetAlpinePijuan$400,000 $375,000441414 N Sheridan RoadAlpinePijuan$1,250,000 $1,225,00050605 Reservoir DriveAlpineReuter$310,000 $291,400211 West Oak CircleAmerican ForkCarey$350,000 $340,00014143 North Mountain ViewAmerican ForkCarey$314,250 $304,000551892 North 250 WestAmerican ForkCarey$315,250 $300,0006034 West Oakley DriveAmerican ForkCarey$345,000 $330,00078123 Oak AvenueAmerican ForkCarey$400,000 $400,0003314 Timp View DriveAmerican ForkGoodrich$418,000 $400,0005750 South Apple WayAmerican ForkGoodrich$385,900 $385,90019321 West Walnut GroveAmerican ForkGoodrich$425,000 $415,00025714 Timp View LaneAmerican ForkGoodrich$250,000 $232,0004361 East Walnut GroveAmerican ForkGoodrich$375,000 $376,00045487 Blue Skies DriveAmerican ForkGoodrich$365,750 $355,00066678 Mountain CircleAmerican ForkGoodrich$335,000 $330,00080144 Oak AvenueAmerican ForkGoodrich$380,500 $365,00058876 South California WayAmerican ForkHen$316,000 $316,0001115 West Oak CircleAmerican ForkHernandez$325,000 $320,00070876 West Hadley WayAmerican ForkHernandez$345,670 $345,00024831 South WeberAmerican ForkLugo$425,815 $400,0001529 East Oak CircleAmerican ForkMerkin$555,000 $500,0008321 North ChoctawAmerican ForkMinkus$565,000 $535,000363461 East Lindon WayAmerican ForkReuter$450,000 $400,000Answer these questions:3411971 SW 269 TerraceCedar HillsCarey$410,000 $397,7004610700 Lake Shore LaneCedar HillsCarey$650,000 $598,000561629 NW 43rd StreetCedar HillsCarey$475,000 $450,0007310 Vestal DriveCedar HillsCarey$555,000 $565,00018687 Kenwood RoadCedar HillsHernandez$725,000 $645,250351857 Pine DriveCedar HillsHernandez$560,700 $550,000711132 SW 52nd StreetCedar HillsHernandez$254,500 $236,6853716235 Orange BoulevardCedar HillsLugo$395,000 $380,000684916 Rock Spring RoadCedar HillsLugo$275,000 $264,0005921 Compass RoadCedar HillsMerkin$289,900 $279,000329408 Forest Hills CircleCedar HillsReuter$185,500 $179,000811600 Reeves StreetCedar HillsReuter$245,900 $233,6055177 East Oak StreetEagle MountainBethune$345,000 $335,00052400 Ivy LaneEagle MountainBethune$375,000 $330,000543418 North Sunset LaneEagle MountainBethune$450,000 $400,00074425 East OakEagle MountainBethune$275,900 $250,00063160 West 5th StreetEagle MountainCarey$410,000 $350,75033245 Ivy LaneEagle MountainGoodrich$395,000 $375,00062140 East 3rd StreetEagle MountainHen$399,000 $350,000189876 South Sunset AvenueEagle MountainHernandez$300,000 $300,00028260 East Jefferson WayEagle MountainHernandez$450,000 $400,000
Attachments: