BIS 155 Endless Education /uophelp.com BIS 155 Endless Education /uophelp.com | Page 18

C. Sort the data by selling agent( A to Z) and then by asking price( largest to smallest). D. Convert the sorted data back into a range, and create a summary report showing the total asking price and total selling price by selling agent. E. Create a column chart that shows the difference by agent between asking price and selling price. STEP 4: Filter the Data A. Create another copy of the original sales data. Rename the sheet Filtered Data. B. Convert the range to a table, and then filter to show only those properties sold in Miami by agent Carey. STEP 5: Apply Conditional Formatting You determine that one measure of how well the company " gets your price " is to set a scale. Excellent performance is at or above the asking price. Good performance is between 95 % and 100 %. Below 95 % is average performance. You will create conditional formatting rules to display graphically the performance level for each sale. Make a copy of the original sales data chart. Name the new sheet Conditional Format. A. Select the data range and insert a table. B. Use Advanced Formatting to set up the following conditional formatting rules for the field Percent of Asking Price.
• Greater than or equal to 100 % = GREEN
• 95 % to 99.9 % = YELLOW
• Less than 95 % = RED 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.