Advanced Project 4-7

Advanced Project 4-7

These instructions are compatible with both Microsoft Windows and Mac operating systems.

Placer Hills Real Estate wants its latest PivotTable to show sales by agent listing. In the source data, you highlight certain listings for insurance purposes. In a separate table, you create a calculation column to determine the number of market days.

[Student Learning Outcomes 4.1, 4.2, 4.8]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

File Needed: PlacerHills-04.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 4-7.xlsx

Skills Covered in This Project

• Set conditional formatting with a formula.
• Pivot fields in a PivotTable.
• Format fields in a PivotTable.
• Add a calculated field to a table.

1. Open the PlacerHills-04 workbook from your student data files and save it as [your initials] Excel 4-7.
2. Apply conditional formatting with a formula.
   1. Select cells E5:E26 on the Listings sheet.
   2. Build a conditional formatting formula to display the address cell in light tan for frame construction. The formula is =k5=“frame” and light tan is the third column in the color palette.
3. Pivot fields in a PivotTable and complete formatting.
   1. Select the PivotTable sheet.
   2. Deselect the City field in the PivotTable Fields pane.
   3. Show the Agent ID field in the Rows area.
   4. Show the Sale Price field two times in the Values area.
   5. Format the first occurrence of the Sale Price field with the custom name Total Sales and Currency with zero decimals.
   6. Format the second occurrence of the Sale Price field with the name Average Sale, choose Average as the calculation, and use Currency with zero decimals.
   7. Apply Light Gray, Pivot Style Light 15.
   8. Apply All Borders to cells A3:C9.
4. Add a calculated field to a table.
   1. Select the Table sheet.
   2. Type Days in cell O4 and press Enter.
   3. In cell O5, build an IF formula to display the number of days on the market. Define a logical_test to calculate if the sale date is greater than the list date.
   4. The value_if_true argument is the sale date minus the list date.
   5. For the value_if_false argument, press Spacebar once in the entry box in the Function Arguments dialog box; this displays a space in the cell when no sale date is available. (If you type the formula, enclose a space in quotation marks for the value_if_false argument.)
5. Build a PivotTable.
   1. Select cell A4 and create a PivotTable in a new worksheet. Confirm that the Add this data to the Data Model box near the bottom of the dialog box is not selected. (If you select this box, you will not be able to create a calculated field in step 6.)
   2. Name the sheet PivotTable 2.
   3. Place the Listing ID field in the Rows area and the List Price and Sale Price fields in the Values area.
   4. Edit the labels in row 3 to Listing, List, and Sale.
   5. Format the list and sale prices as Currency with no decimals.
6. Insert a calculated field in a PivotTable.
   1. Insert a calculated field named Difference with a formula that subtracts the sale price from the list price.
   2. Change the display label for the calculated field to Variance.
   3. Format the Variance values to show negative numbers in red with parentheses.
   4. Select all the cells in the PivotTable and apply All Borders.
7. Save and close the workbook (Figure 4-125).