RELIABLE PAPERS ONLY PLEASE

Unit 8    [IT153: Spreadsheet Applications] 

 

Assignment Details and Rubric Outcomes addressed in this activity: Unit Outcomes:

 Importing, analyzing and manipulating data  Exporting data to another application (Microsoft Word)

Course outcome(s): IT153-3: Integrate workbooks to consolidate data.

Using a Template and Importing Data Problem:

You work as a teacher at a private school. You teach every subject to the four students in your gifted class except for art, computers, and physical education. To help you in determining a student’s grade for a semester, you ask the art, computers, and physical education instructors to send you grades for your four students, although each data set is in a different format. The art teacher sends you a Web page from the school’s Web system. The computer teacher, who uses an Access database to maintain data, queried the database to create a table for you. The physical education teacher typed all the information in a Word table. At the conclusion of the instructions, the Grades Summary worksheet should appear as shown in Figure below:

Instructions:

1. Open the template Unit 8 Assignment data file from Doc Sharing. Save the template as a workbook using the file name, Unit 8 Assignment 8 Your Name. Make sure Excel Workbook is selected in the ‘Save as type’ list when you save the workbook.

2. Select cell B4. Import the Web page, Art.htm, from the Data Files for Students. Select the HTML table containing the art grades. In the Import Data dialog box, click the Properties button. In the External Data Range Properties dialog box, do not adjust the column width. Import the text data to cell B4 of the existing worksheet. Delete row 4.

3. Select cell B8. Import the Access database file, Computer, from the Data Files for Students. Choose to view the data as a table, and insert the data starting in cell B8 in the existing workbook. Accept all of the default settings to import the data. Right- click any cell in the table, point to Table, and then click Convert to Range. Click the OK button to permanently remove the connection to the query. Delete row 8. Copy the format from B7:D7 to B8:D11.

4. Start Microsoft Word, and then open the Word file, Physical Education, from the Data Files for Students. Copy all except the first column of data in the table. Switch to Excel. Select cell B18, and then use the Paste Special command to paste only the text into the Grades Summary worksheet. Close Word without saving any changes. Copy the range B18:E20. Select cell B12, and then use the

Unit 8    [IT153: Spreadsheet Applications] 

 

Paste Special command to paste and transpose the data. Adjust the column widths as necessary to display all of the data.

5. Delete the range B18:E20, and then delete row 16. Copy the formatting of cells C11:D11 and apply the formatting to the range C12:D15. Add the data as shown in Cells A4, A8, and A12. Calculate the Average of the Midterm and Final grades in cell E4 and copy the formula down to cell E15. Use the SteDev function to obtain the standard deviation for the Midterm and final Grades in cell F4, and copy this formula to down to Cell F15.

6. Change the worksheet header to include your name, course number, and the date.

7. Create an embedded Line Chart to span the cells A17 through F34 plotting the Names and Standard Deviations.

8. Change the style of the Chart to style 36, Change the Chart Layout to Layout 3 and remove the legend. The finished worksheet should resemble the screenshot below.

Unit 8    [IT153: Spreadsheet Applications] 

 

9. Copy the worksheet and name the new Worksheet Tab Grades Average, color the tab Red.

10. Add the Label Class to cell A3 and delete the chart.

11. Copy Art, Computers and Physical Education to the blank cells12. Using the SubTotal feature in the Data Ribbon, Compute the Average for each Class. The finished worksheet should resemble the screenshot below.

13. Save and Submit the assignment to the Unit 8 Assignment Dropbox.

All deliverables should be professionally formatted and should be free of spelling errors. Points deducted from the grade for each error are at your instructor’s discretion.

Unit 8 Assignment grading rubric = 50 points

Assignment Requirements Points Possible

Points Earned

Data from the Web page Lab 7-3 Art.htm is imported to cell B4, and row 4 is deleted.

0 - 5

Unit 8    [IT153: Spreadsheet Applications] 

 

The Access database file Lab 7-3 Computer is imported so that the data is inserted starting in cell B8, and row 8 is deleted.

0 - 5

All Data, except for the first column, is copied from the Word file Lab 7-3 Physical Education and pasted into the Grades Summary worksheet.

0 - 5

The range B18:E20 is transposed to cell B12. 0 - 5

Delete the range B18:E20, and then delete row 16. Copy the formatting of cells C11:D11 and apply the formatting to the range C12:D15. Add the data as shown in Cells A4, A8, and A12. Calculate the Average of the Midterm and Final grades in cell E4 and copy the formula down to cell E15. Use the SteDev function to obtain the standard deviation for the Midterm and final Grades in cell F4, and copy this formula to down to Cell F15.

0 - 6

The document properties are changed and the worksheet header has the specified information.

0 - 2

An embedded Line Chart to span the cells A17 through F34 plotting the Names and Standard Deviations was created.

0 - 5

The style of the Chart was set to style 36; Change the Chart Layout to Layout 3 and the legend was removed.

0 - 3

Copy the worksheet and named Grades Average, with the Tab color Red. 0 - 2

Add the Label Class to cell A3 and delete the chart.

The labels Art, Computers and Physical Education were copied to the blank cells.

0 - 2

The SubTotal feature in the Data Ribbon, Computing the Average for each Class was completed.

0 - 5

The assignment reflects knowledge of the concepts for the unit’s readings and Step-by-steps.

0 - 5

TOTAL POSSIBLE POINTS: 0 - 50

Points deducted for spelling or formatting

Adjusted total points