Answer & Explanation:Rubic and data file attached
Unformatted Attachment Preview
Unit 5: Assignment
Outcomes addressed in this unit:
Use the VLOOKUP function to find values.
Apply filtering to data.
Prepare data to convert charts.
Format a worksheet as a table.
Use the SUMIF and COUNTIF functions.
Create a Pivot Table.
Add a clustered bar pivot chart.
Course outcome(s) practiced in this unit:
IT153-3: Prepare workbooks to consolidate data.
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 in column G.
Instructions: Perform the following tasks:
1. Download the Unit 5 data file from Doc Sharing.
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 (or similar color)..
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.
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
16. Save the workbook as Unit_ 5_Assignment _Your Name and submit to the Unit 5
Worksheet with Functions Complete
Worksheet with Sorting and Filtering Applied
Pivot Table Results
Unit 5 Assignment grading rubric = 65 points
1. The Worksheet is formatted as a Table
(3) and named PledgeAmount.(3)
2. The VLOOKUP Function is used to
determine the Letter Grade.
3. The Table has a Total Row
option enabled (3) and
functions set as instructed.(3)
4. The SUMIF, (3)and COUNTIF
(3)functions were used to
determine the Pledge amounts
5. Sort is performed using the First
Level of Gender (Ascending
Order)( 3 ) and Second Level of
GPA (Descending Order).(3)
6. Table is filtered by the gender of
Females (3) and Pledge Amount
greater than $5000. (3)
7. Pivot Table is created as instructed
(2)(Cells B3 (2) and A4 (2)are
changed as specified.
8. Pivot Table is filtered for Female.
9. Pivot Table Design Style is set to
Pivot Style Medium 4 (2) and
Currency Style (2) without and
Decimals is applied.(2 )
10. Add a Clustered Bar Pivot Chart, (4)
change the style,(1) and position
below Pivot Table.(1)
11. Worksheet and Pivot Table Sheets
are renamed, (2)colors set and set
in the correct order.(2) Delete any
Annual Pledge List
Total Male Pledge
Total Female Pledge
Pledge Amount GPA
Purchase answer to see full