Digital Academic Solutions

Office 2013 MyITLab MS-Excel Grader Mountain View Realty

6/02/2018 Team Libraay Microsoft Excel 2 Comments

SOLUTION AVAILABLE AT: http://libraay.com/downloads/mountain-view-realty-excel-solution/

Problem:

A coworker developed a spreadsheet listing houses listed and sold during the past several months. She included addresses, location, list price, selling price, listing date, and date sold. You need 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 format the table.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step Instructions Points Possible
1 Start Excel. Open the downloaded file named exploring_e04_grader_h1.xlsx. 0
2 In the Sales Data worksheet, freeze the top row.

Hint: On the VIEW tab, in the Window group, click Freeze Panes and then click Freeze Top Row.

 5
3 In the Sales Data worksheet, convert the data to a table and apply Table Style Medium 17. 6
4 In the Sales Data worksheet, remove duplicate records in the table.

Hint:
Be sure that you use the Remove Duplicates command from the ribbon.  Don’t just delete the row containing duplicate data from the table.  If you delete rows, the automatic grading function in myITLab will get confused and may mark some other things wrong!

 5
5 In the Sales Data worksheet, insert a new field to the right of the Selling Price field. Name the new field Percent of List Price. 6
6 In the Sales Data worksheet, create a formula in cell E2  with structured references to calculate the percent of the list price. Format the field with Percent Style with one decimal place in the range E2:E43.

`Hint:
– As this formula will be located within a table, you should therefore only use unqualified structured references (see Excel Help page: ‘Use structured references in Excel table formulas.’)
– While a valid option, to keep myITlab happy, do not use semi-selection to create the formula (i.e., your formula should not feature the @ sign). Semi-selection (or pointing) is defined as: The process of using the mouse pointer to select cells while building a formula.

 6
7 In the Sales Data worksheet, insert a new field to the right of the Sale Date field. Name the new field Days on Market. Create a formula with structured references to calculate the number of days on the market.

Hint:
Due to a bug, do not use the DAYS() function.

 6
8 In the Sales Data worksheet, add a total row to display the average percent of list price and average number of days on market. Apply the General number format to the average number of days on market as a whole number. Type Averages in cell A44. 10
9 In the Sales Data worksheet, 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.

Hint:
– For full credit on this step, you must use the multiple field sort command from the ribbon.
– Technically speaking, the terms order and sort are used synonymously.

 8
10 In the Sales Data worksheet, select the Listing Date and Sale Date fields and set a column width of 11.00. Wrap the column labels in the range E1:H1. 6
11 In the Sales Data worksheet, repeat the field names on all pages.

Hint: On the PAGE LAYOUT tab, in the Page Setup group, click Print Titles.

 5
12 Display the Sales Data worksheet in Page Break Preview and move the page break to occur between rows 26 and 27, and then change back to Normal view.

Hint: To display Page Break Preview, on the VIEW tab, in the Workbook Views group, click Page Break Preview. To insert the page break, click cell A27 and on the PAGE LAYOUT tab, in the Page Setup group, click Breaks.

 5
13 Display the Filtered Data worksheet. Convert the table to a range of data. 5
14 Filter the data to display the cities of Alpine, Cedar Hills, and Eagle Mountain.

Hint:
– At this point, the table you created earlier no longer exists (as you converted the affected range of cells back to a range). However, an option is to select the cells containing data and create a ‘FILTER’ over a range of cells (On the HOME tab, in the Editing group, click Sort & Filter). So, one can ‘filter’ a range of cells containing data, even if those cells are not within a Table. When you ‘apply’ a Filter, the drop-downs at the top of each field that you typically see when the data is within a Table, reappear, thereby allowing the user to filter/sort the data, just like one can do if the data resides within a Table.

 6
15 Filter the data to display records for houses that were on the market 30 days or more. 5
16 Apply the 3 Arrows (Colored) icon set to the days on market values.

Hint: On the HOME tab, in the Styles group, click Conditional Formatting and then point to Icon Sets.

 5
17 Apply the Light Blue Data Bar conditional formatting in the Gradient Fill section to the selling prices.

Hint: On the HOME tab, in the Styles group, click Conditional Formatting and then point to Data Bars.

 5
18 Create a new conditional format that applies Yellow fill (fourth color in the bottom row) and bold font to values that are higher than 98% for the Percent of List Price column. 6
19 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales Data, Filtered Data.  Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100

SOLUTION AVAILABLE AT: http://libraay.com/downloads/mountain-view-realty-excel-solution/

2 comments

  1. Hello!

    Can you please complete EX16_XL_CH04_GRADER_CAP_AS – Mountain View Realty 1.4. The excel sheet is called exploring_e04_grader_a1.xlsx. It is in chapter 4 of Office 2016 – myitlab:grader. It is similar to the one above only that for it is for Office 2016 MyITLab MS-Excel not 2013. I would be more than glad to pay for it once completed. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *



Solutions Authored by 15+ Years Experienced Industry Expert

Connect with Libraay

Copyright © 2020 Libraay.com. All rights reserved.