BIS 155 Course Great Wisdom / tutorialrank.com BIS 155 Course Great Wisdom / tutorialrank.com | Page 56

C. Use Percent of Asking Price as the Sort Key, and sort by color with
green first, then yellow, and then red Step 6. Create a Pivot Table
Now that you have sorted, subtotaled, filtered, and conditionally
formatted the data, you have a few more questions to answer. You
want to see average percentage of selling price by agent and by city.
This will allow you to see whether there are any patterns based on
the city or based on the salesperson. To do this kind of comparison,
you decide to create a pivot table.
A. Open the Sales Data worksheet. Select the Sales Data range
(A3:H33). From the Formulas tab, select Define Name from the
Defined Names group. Name the range of cells SalesData.
B. Create a pivot table based on the named range, SalesData. Create
the pivot table in a new worksheet.
C. Select Selling Agent as your Row Labels and City as your Column
Labels. Select Percent of Sales as your Values. Change the Summary
typeb for Percent of Sales to average, the Number Format to
percent, and the Name to Average Percent of Asking Price. Apply
Data Bars Conditional Formatting to the percentages. Hide the Field
Headers.
D. Rename the sheet PivotTable, and move it so that it is the last tab
in the worksheet.
Step 7. Draw a conclusion
On the Documentation Sheet, add a section called Analysis. Based on
your data analysis, do you believe that Barr Realty should be allowed
to advertise that they "Get your price?" Justify your answer by
referring to the data analysis you have just completed.