Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel. Open the downloaded Excel file named e03_grader_h1_ShareClub.xlsx. Save the file with the name e03_grader_h1_ShareClub_LastFirst, replacing LastFirst with your last and first name.	0
2	On the ClubInformation worksheet, format the range A5:J16 as a Table. Assign named ranges using the Top row.	10
3	A grade table appears in the spreadsheet assigning letter grades to grade point averages. In cell J6, use a VLOOKUP function to determine a letter grade for the grade point average in cell I6. Use absolute references in your formula as necessary.	6
4	Insert a new column to the left of column H. In cell H5, type Courses Completed. Resize the column as needed. Assign the named range using the top row.	3
5	In cell H6, use an IF function with a nested AND function to create a function that analyzes the content of F6 and G6. Return Complete if both cells contain a Y or Incomplete if one or both cells contain an N.	8
6	In cells B22:B24, using named ranges, create functions that count the number of students if their year in school matches the years listed in A22:A24. In cells C22:C24, using named ranges, create functions that total the number of volunteer hours if their year in school matches the years listed in A22:A24. In cells C25:C26, using named ranges, create functions that total the number of volunteer hours if the criteria n A26:A27 is met.	9
7	In cell A38, type Totals. Create a formula in cells B38:E38 to find the total sum for the Value, Donated, Desktops, and Laptops columns respectively.	4
8	In cell F33, create an IF function that evaluates the total number of computers donated from Safety Inc. If a minimum of 30 computers have been donated, the function will return Goal Met. If fewer than 30 computers are donated, the function will return Under Goal. Use AutoFill to copy the function to cell range F34:F37.	8
9	Using the data in the table, insert a PivotTable in cell A10 on the PivotAnalysis worksheet. Use the following criteria to create the PivotTable: Add the Major and GPA fields to the FILTERS area (in that order). Add the Grade, Year, and FirstName fields to the ROWS area (in that order). Add the Volunteer Hours and LastName fields to the VALUES area (in that order). Display subtotals at the bottom of each group. In cell A10, type Grades. In cell B10, type Total Hours. In cell C10, type Total Students. Resize the columns as needed.	10
10	Insert a slicer for the Grade field. Format the slicer with 3 columns. Resize the slicer to remove the extra white space at the bottom. Move the slicer so the top-left corner is in cell F10.	8
11	In the slicer, select A, A-, B+, and B-. View the PivotTable data to determine how many students have a grade of an A or a B. Click cell A2, click the drop-down arrow, and then select the number of students.	8
12	Clear the filter in the slicer, and then in the slicer select the grade of an A. In cell B8, filter the data so the student with the highest GPA displays. View the PivotTable data to determine which student has the highest GPA. Click cell A4, click the drop-down arrow, and the select the name of the student with the highest GPA displayed.

NEED STEPS 9-12

D G K L M N D R 5 T T U V W Guilford College Repair and Share Club To N 5 Laskame Fier Majer 30456 Bouch Senior L Computer Science re ? SEOS Luca Sarer CUMHUS 56742 Tinda Thames luniar MIS 9 48795 x Cheryl Sophomore on 10 28597 Linde Senior Notworking 21 21486 Betail Senior PIS :: B 58 : Jurier Cumpuni 13 46 Shu Lin al Sachar Carrier Science 74 5erad Mini luniar Bhpineering 15 46546 Zachers lohn Sophomore prin 26 41:23 Online Liu Junior MIS 27 11 Programming Course Network Cours Course Compied Volunteer HO GP Grad Y Complu. 152 3.21 F Y Lampu. ... 162 4.00 U Y Incomplete 111 4.210 Y Comples Sa 2G Y Comples 110 3.91 C N N N Incomplete 149 4.15 Y Y - Lomi 143 2.10 C+ Compi 94 N Incomplete 72 Y Complete 112 N Incompleto 109 2.40 A w GPA Grade 0.20F 1.00 D. 1.35 D 1.75 - 2.00 2.23 2./ C+ 1.00 - - 3.25 3.50 2+ 3.80 A 4.00 A At - Y N N 20 bludent Report Analysis Headcount 21 Total Total Hours Value Status Company Dorntion in the Donated Desktops 4,620 23 20 4501 22 535 25 1 11 1.950 13 4 2,620 B 2 Laptops 15 11 23 74 15 26 27 28 29 12 30 31 32 23 34 da 15 16 37 28 39 10 41 42 12 44 45 46 17 1A 49 50 10 5 5 5 5 9 LE G H M N O a R S V W BC D E G 1. How many students have an A or B grade? 2 3 2. Which student has the highest GPA? 4 5 3. Which student lopged the most volunteer hours? Which student logged the least? 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 45 46 47 48 49 Clubinformation PivotAnalysis + D G K L M N D R 5 T T U V W Guilford College Repair and Share Club To N 5 Laskame Fier Majer 30456 Bouch Senior L Computer Science re ? SEOS Luca Sarer CUMHUS 56742 Tinda Thames luniar MIS 9 48795 x Cheryl Sophomore on 10 28597 Linde Senior Notworking 21 21486 Betail Senior PIS :: B 58 : Jurier Cumpuni 13 46 Shu Lin al Sachar Carrier Science 74 5erad Mini luniar Bhpineering 15 46546 Zachers lohn Sophomore prin 26 41:23 Online Liu Junior MIS 27 11 Programming Course Network Cours Course Compied Volunteer HO GP Grad Y Complu. 152 3.21 F Y Lampu. ... 162 4.00 U Y Incomplete 111 4.210 Y Comples Sa 2G Y Comples 110 3.91 C N N N Incomplete 149 4.15 Y Y - Lomi 143 2.10 C+ Compi 94 N Incomplete 72 Y Complete 112 N Incompleto 109 2.40 A w GPA Grade 0.20F 1.00 D. 1.35 D 1.75 - 2.00 2.23 2./ C+ 1.00 - - 3.25 3.50 2+ 3.80 A 4.00 A At - Y N N 20 bludent Report Analysis Headcount 21 Total Total Hours Value Status Company Dorntion in the Donated Desktops 4,620 23 20 4501 22 535 25 1 11 1.950 13 4 2,620 B 2 Laptops 15 11 23 74 15 26 27 28 29 12 30 31 32 23 34 da 15 16 37 28 39 10 41 42 12 44 45 46 17 1A 49 50 10 5 5 5 5 9 LE G H M N O a R S V W BC D E G 1. How many students have an A or B grade? 2 3 2. Which student has the highest GPA? 4 5 3. Which student lopged the most volunteer hours? Which student logged the least? 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 45 46 47 48 49 Clubinformation PivotAnalysis +