Create a formula with structured references

Assignment Help Database Management System

Reference no: EM131382402

Mountain View Realty

Project Description:

You work at Mountain View Realty. A coworker developed a spreadsheet cataloging houses listed and sold during the past several months. She included addresses, location, list price, selling price, listing date, and date sold. You want to convert the data to a table. You will manage the large worksheet, prepare the worksheet for printing, sort and filter the table, include calculations, and then format the table.

Step Instructions

1 Open the download file exploring_e04_grader_a1.xlsx.

2 Freeze the first row on the Sales Data worksheet.

3 Convert the data to a table and apply the Table Style Medium 17.

4 Remove duplicate records.

5 Insert a new field to the right of the Selling Price field. Name the new field Percent of List Price.

6 Create a formula with structured references to calculate the percentage of the list price.

7 Format the field with Percent Style with one decimal place.

8 Insert a new field to the right of the Sale Date field. Name the new field Days on Market.

9 Create a formula with structured references to calculate the number of days on the market. Apply the General number format to the values.

10 Add a total row to display the average percentage of list price and average number of days on market. Format the average number of days on market as a whole number. Use an appropriate label for the total row.

11 Sort the table by city in alphabetical order and add a second level to sort by days on market with the houses on the market the longest at the top within each city.

12 Adjust column widths so that all data is visible. Wrap the column labels.

13 Repeat the field names on all pages.

14 Add a page break at Row 36 so that city data does not span between pages, and change back to Normal view.

15 Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.

16 Display the Filtered Data worksheet. Filter the data to display the cities of Alpine, Cedar Hills, and Eagle Mountain.

17 Filter the data to display records for houses that were on the market 30 days or more.

18 Apply the 3 Arrows (Colored) icon set to the Days on Market values.

19 Apply theLight Blue Data Bars conditional formatting in the Gradient Fill section to the selling prices.

20 Create a new conditional format that applies yellow fill and bold font to values that contain 98% or higher for the Percent of List Price column.

21 Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.

22 Select Landscape orientation and set appropriate margins so that the data will print on one page.

23 Save and close the file.

Attachment:- exploring_e04_grader_a1.xlsx

Reference no: EM131382402

Questions Cloud

Identify at least four current trends that affect recreation : Identify at least four current trends that affect recreation, travel, and tourism within your selected destination city. Describe each trend. What effect do these trends have on hospitality operations in your selected destination city?

Develop the full allowable tensile load : Design the fillet welds parallel to the applied load to develop the full allowable tensile load of the 6-in.-by- 3 8 -in. ASTM A36 steel plate shown. The electrode is an E70.

Give the answer of muliple choice question : Bill is working on a project involving the upgrading of a management information system. The project is being managed by the information systems department with the coordination of other departments occurring through normal channels. He is working..

Design an end connection using longitudinal welds : Design an end connection using longitudinal welds and an end transverse weld to develop the full tensile capacity of the angle shown. Use A36 steel and E70 electrodes.

Create a formula with structured references : Create a formula with structured references to calculate the percentage of the list price. Format the field with Percent Style with one decimal place.

Give the answer of muliple choice question : How is project scope defined?There are 5 steps involved when defining a project, which of the following is the first?One of the primary jobs of a project manager is to manage the trade-offs associated with the project. Which of the following is NOT ..

Calculate number of days between listing date and sale date : In cell J2, enter a formula to calculate the number of days between the listing date and sale date. Use the Subtotal feature to calculate the average selling price, percentage of asking price, and days on market by city.

What percent of all ticket holders are no-shows : What's the probability that a customer who didn't show had an advance-purchase ticket?

What’s the probability that chuck is on the job : You go to Dan's for supper one night and hear a dish break at the sink. What's the probability that Chuck is on the job?

User Account

All Pages