Notice: Trying to get property 'display_name' of non-object in /home/essadpjw/cheapessaysite.com/wp-content/plugins/wordpress-seo/src/presenters/meta-author-presenter.php on line 40
  

Answer & Explanation:Rubic and data file attached
rubic.pdf

unit_5_data_file.xlsx

Don't use plagiarized sources. Get Your Custom Essay on
Expert answer:Prepare data and consolidate excel workbook, assig
Just from $10/Page
Order Essay

Unformatted Attachment Preview

Unit 5: Assignment
Outcomes addressed in this unit:
Unit Outcomes
 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.
Scenario:
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
Dropbox.
Worksheet with Functions Complete
Worksheet with Sorting and Filtering Applied
Pivot Table Results
Unit 5 Assignment grading rubric = 65 points
Assignment Requirements
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
and counts.
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)
Points
Possible
0-6
0-6
0-6
0-6
0-6
0-6
Points
Earned
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
unused.(1)
Total Points
0-6
0-6
0-6
0-6
0-5
0-65
Annual Pledge List
Pledge ID
721890
638192
452618
781020
610298
491827
821928
521989
756719
471829
Lname
Fritz
Delta
Chucky
Leone
Forkner
Chan
Frye
Counte
Lopes
Trello
Total Male Pledge
Total Female Pledge
Male Count
Female Count
Fname
Julie
Lyn
Kate
Sally
Meg
Suzy
Janice
Bill
Hector
Len
Gender
F
F
F
F
F
F
F
M
M
M
Age
22
28
25
25
25
23
23
19
18
23
Pledge Amount GPA
$
3,250
0.42
$
1,275
1.15
$
6,217
2.15
$
11,600
2.77
$
9,825
3.92
$
2,250
4.23
$
9,200
4.30
$
7,125
2.90
$
2,995
3.15
$
3,210
3.32
Grade Table
Grade
GPA
0.00
1.00
1.25
1.75
2.00
2.25
2.75
3.00
3.25
3.50
3.90
4.00
4.25
Grade
F
DD
D+
CC
C+
BB
B+
AA
A+

Purchase answer to see full
attachment

cheap essay site

Order your essay today and save 30% with the discount code ESSAYSHELP