RELIABLE PAPERS ONLY PLEASE

Unit 5    [IT153: Spreadsheet Applications] 

 

Assignment Details and Rubric Outcomes addressed in this activity:

Unit Outcomes:

 Use the VLOOKUP function to find values  Use the Sort function  Demonstrate the ability to Filter data.  Convert data to charts

Course Outcome: IT153-2: Create formulas and functions.

Creating, Sorting, Filtering a Table, and Creating a Pivot Table

Purpose: To demonstrate the ability to create, sort and filter a table in Excel 2010.

Problem: You are a member of the volunteer pledges for your college campus-volunteering club, a club for young adults interested in helping the less fortunate. The president has asked for a volunteer to create a table of the club’s members (Figure E5A-1). You decide it is a great opportunity to show your Excel skills. Besides including a member’s GPA in the table, the president also would like a GPA letter grade assigned to each member based on the GPA value in column G.

Instructions: Perform the following tasks:

1. Download the Unit 5 data file from Doc Sharing and save the workbook using the file name: Unit 5 Assignment Your Name.

2. Format as a Table the range A7 through H17 using a Table style of your Choice. Name the Table PledgeAmount. Rename the Sheet1 tab as Annual Pledge List. Color the Tab to Gold, Accent 3.

3. Using the Grade table in the range J6:K20. In cell H8, enter the function Use the VLOOKUP Function to determine the letter grade that corresponds to the GPA in cell G8 (do not forget to make the Table Array Absolute). Copy the function in cell H8 to the range H9:H17.

4. Select the Total Row option on the Contextual Design tab for the Table on the Ribbon to determine the maximum for the age, the sum for the pledge amount, average for the GPA, and the record count in the Grade column in row 18.

Unit 5    [IT153: Spreadsheet Applications] 

 

5. Use the SUMIF and COUNTIF functions to determine the totals and counts for the Pledge amount with each Gender in the range C21:C24.

6. Sort the Table by the First Level as Gender (Ascending Order) and Second Level as GPA (Descending Order).

7. Filter the Table by the Gender of Females and Pledge Amount greater than $5000.

8. Create a pivot Table using the data in the table. Change the Sheet Name to Pivot Table Color the Tab to Green and move the Tab so it is to the Right of the Annual Pledge List Worksheet.

9. Set the fields as follows: a. Report=Gender; Row=LName; Column=Age; and Value=Pledge Amount. b. Filter the Pivot table for Female.

10. Change cell B3 to Age and A4 to Last Name.

11. Set the Pivot Table Style to Pivot Style Medium 4.

12. Set all Data within the Pivot Table for the Style of Currency without any decimal places (Hint:

Do Not Include the Age Field).

14. Add a Clustered Bar Pivot Chart and change the style to one of your choice. 15. Delete any unused worksheets and Save the workbook as Unit 5 Assignment Your Name.

Unit 5    [IT153: Spreadsheet Applications] 

 

Worksheet with Functions Complete

Worksheet with Sorting and Filtering Applied

Unit 5    [IT153: Spreadsheet Applications] 

 

Pivot Table Results

Unit 5    [IT153: Spreadsheet Applications] 

 

Unit 5 Assignment grading rubric = 50 points

Assignment Requirements Points possible

Points earned

You are a member of the volunteer pledges for your college campus-volunteering club, a club for young adults interested in helping the less fortunate. The president has asked for a volunteer to create a table of the club’s members. You decide it is a great opportunity to show your Excel skills. Besides including a member’s GPA in the table, the president also would like a GPA letter grade assigned to each member based on the GPA value.

1. The Worksheet is formatted as a Table and named PledgeAmount.

0 - 2

2. The VLOOKUP Function is used to determine the Letter Grade.

0 - 5

3. The Table has a Total Row option enabled and functions set as instructed.

0 - 8

4. The SUMIF, and COUNTIF functions were used to determine the Pledge amounts and counts.

0 - 5

5. Sort is performed using the First Level of Gender (Ascending Order) and Second Level of GPA (Descending Order).

0 - 5

6. Table is Filtered by the gender of Females and Pledge Amount greater than $5000.

0 - 5

7. Pivot Table is created as instructed Cells B3 and A4 are changes as specified.

0 - 5

8. Pivot Table is Filtered for Female. 0 - 2

9. Pivot Table Design Style is Set to Pivot Style Medium 4 and Currency Style without and Decimals is applied.

0 - 2

10. Add a Clustered Bar Pivot Chart, change the style, and position below Pivot Table.

0 - 2

11. Worksheet and Pivot Table Sheets are renamed, colors set and set in the correct order. Delete any unused worksheets and Save the workbook as Unit 5 Assignment Your Name.

0 - 4

12. Ensure your assignment shows a clear understanding of the concepts covered in the Unit as applied in this assignment from the Required Readings and Step-by-Step Examples in the text. Ensure that the work

0 - 5

Unit 5    [IT153: Spreadsheet Applications] 

 

presented reflects appropriate use of Microsoft Excel's features, Functions and Professional Formatting.

** Upload to the Unit 5A Dropbox prior to Tuesday Evening at 11:59 P.M. Eastern Time **

TOTAL POSSIBLE POINTS: 0 - 50

Points deducted for spelling or formatting

Adjusted total points