On the Student Data worksheet, select the range A7:K17, and then name the range Student_Data. With range A7:K17 selected, create a named range for each column in the Student_Data named range using the top row as the range values.	3.000
Click the Data Inputs worksheet tab. Select the range A3:B3, and then create a named range using the left column as the name. Select the range A6:E10, and then create a named range using Lesson_Pricing as the name. Select the range A13:E17, and then create a named range using Trans_Fees as the name. Select the range A20:G24, and then create a named range using Uniform_Fees as the name. Select the range D3:F4, and then create a named range using Entry_Fee as the name.	5.000
Click the Student Data worksheet tab. In cell E5, use the COUNTIF function to count the Teacher field in the Student_Data table that meets the filter criteria in cell E4. Copy the formula in cell E5 over to cell I5. In cell K5, use the DCOUNTA function to count the StudentID field in the Student_Data table that meets the filter criteria in the range D1:K2.	8.000
In cell K8, use the VLOOKUP function nested in an IFERROR function to retrieve the price per hour located in column C in the Lesson_Pricing named range for each student based on the student?s skill level located in the Skill_level field. The value should be looking for an exact match. To prevent an error from being displayed when the skill level is not known, use the IFERROR function to leave the cell blank. Copy the formula in cell K8 down to K17.	8.000
Click the Lesson Data worksheet tab. In cell F3, add a MATCH function nested in an INDEX function to retrieve the skill level of each student listed in column B. To prevent zeros from displaying when the skill level is not known, nest the INDEX and MATCH functions in an IF function to leave the cell blank when the skill level equals 0. Copy the formula down to F17.	8.000
In cell G3, add a MATCH function nested in an INDEX function to retrieve the total fee located in the Lesson_Pricing table on the Data Inputs worksheet for each student listed in column B. To prevent an error from being displayed when the skill level is not known, nest the INDEX and MATCH functions inside an IFERROR function and display the default total fee of $50. Copy the formula in cell G3 down to G17.	8.000
Click the Report worksheet tab. In cell B3, use the VLOOKUP function to look up the teacher?s name in the Student_Data table for the student listed in cell B2. The value should be looking for an exact match. In cell B4, use the AVERAGEIF function to calculate the teacher?s average fee in the Price_Per_Hour field for the teacher listed in cell B3.In cell B5, use the VLOOKUP function to look up the student?s skill level in the Student_Data table for the student listed in cell B2. The value should be looking for an exact match.	10.000
In cell B6, use the COUNTIF function to count the StudentID field on the Lesson Data worksheet that meet the filter criteria in cell B2.In cell B7, use the SUMIF function to sum the Total Fee field on the Lesson Data worksheet that meet the filter criteria in cell B2.In cell B8, use a nested IF function to determine how many free lessons the student has earned. If the student has had less than 5 total lessons, display 0 earned. If the student has had between 5 and 10 lessons, display 1 earned; otherwise, display 2 earned.	10.000
In cell E5, use the VLOOKUP function to look up the student?s uniform size in the Student_Data table for the student listed in cell B2. The value should be looking for an exact match. In cell E7, use the HLOOKUP function to retrieve the student?s registration fee in the Entry_Fee named range for the registration type in cell E3. The value should be looking for an exact match. In cell I13, use the COUNTIF function to count the number of uniforms in the Student_Data table that meet the filter criteria in cell H12.	10.000
In cell I14, add a MATCH function nested in an INDEX function to retrieve the uniform fee located in the Uniform_Fee table on the Data Inputs worksheet that meet the filter criteria in cells H12 and I13. To prevent an error from displaying when no uniforms of the size located in H12 are needed, nest the INDEX and MATCH functions inside an IFERROR function to leave the cell blank.	10.000
In cell B12, use the VLOOKUP function to retrieve the student?s first name in the Student_Data table that meets the filter criteria in cell A12. The value should be looking for an exact match. Copy the formula in B12 down to B17.In cell C12, use the VLOOKUP function to retrieve the student?s last name in the Student_Data table that meet the filter criteria in cell A12. The value should be looking for an exact match. Copy the formula in C12 down to C17.	6.000
In cell D12, use the VLOOKUP function to retrieve whether or not the student has a permission slip on file in the Student_Data table that meets the filter criteria in cell A12. The value should be looking for an exact match. Copy the formula in D12 down to D17.In cell E12, use the VLOOKUP function nested in an IF function to retrieve the student?s uniform size. To prevent zeros from being displayed when the uniform size is not known, nest the VLOOKUP function in an IF function to leave the cell blank when the skill level equals 0. The value should be looking for an exact match. Copy the formula in E12 down to E17.	5.000
In cell F12, use the AND function nested in an IF function to determine if the student has a permission slip and a uniform. If the student has a permission slip and the uniform size is not blank, then display Yes in the field; otherwise, display No. Copy the formula in F12 down to F17.	3.000
In cell F18, use the COUNTIF function to determine how many students are currently traveling to the competition as indicated by Yes in cell range F12:F17.	3.000
In cell E8, use the MATCH function nested in an INDEX function to retrieve the transportation fee located in the Trans_Fee table on the Data Inputs worksheet that meet the filter criteria in cell E4 and F18.	3.000
Ensure that the worksheets are correctly named and placed in the following order in the workbook: Student Data, Lesson Data, Data Inputs, Report. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.	0.000
Total Points	100.000

Student Schedule Date StudentID 5/17/2015 000001 11/5/2015 000001 7/28/2015 000002 8/1/2015 000002 3/10/2015 000002 3/30/2015 000004 10/10/2015 000006 4/16/2015 000006 6/15/2015 000006 7/30/2015 000006 1/18/2015 000007 10/5/2015 000008 9/24/2015 000009 1/16/2015 000010 5/21/2015 000010 Student Zia Zia Pandora Pandora Pandora Jael Summer Summer Summer Summer Quyn Violet Clark Hyat Hyat Teacher Anne Jenny Anne Jenny Mark Anne Anne Chuck Mark Mark Jenny Chuck Chuck Chuck Mark In Lesson Length Skill Level hours 2.00 0.50 1.00 2.00 2.00 0.50 0.50 2.00 1.00 1.00 1.50 1.50 2.00 1.50 0.50 Total Fee Data Inputs Annual Competition Competition Entry Fee Early Bird Regular Late $ 250 $ 500 $ 750 12/1/2015 Lesson Pricing 0.5 Beginner Intermediate Advanced Expert $ 1.0 15 $ 20 25 35 1.5 30 $ 25 30 40 2.0 45 $ 30 35 45 60 35 40 50 Transportation Fees per Person Flight Bus Charter Drive 30 $ 592 $ 86 $ 125 $ 190 20 426 84 120 190 10 382 82 115 190 0 277 80 110 190 Uniform Fees XS 0 $ 42.23 $ 15 40.59 20 39.01 25 38.17 S 43.21 $ 41.57 39.99 39.15 M 44.19 $ 42.55 40.97 40.13 L 45.17 $ 43.53 41.95 41.11 XL XXL 46.15 $ 47.13 44.51 45.49 42.93 43.91 42.09 43.07 Student Report Student ID Teacher Average Fee Skill Level Total Lessons Total Paid this Year Bonus Lessons 000008 Competiton Fees Registration Type Travel Type Uniform Size Uniform Fee $ Registration Fee Transportation Fee Total $ Early Bird Regular Flight Regular Bus Bus Late - Charter Drive - Competition Report Student ID 000001 000002 000003 000006 000008 000010 Student First Name Student Last Name Permission Slip? Uniform Size Ready to go! Total Uniforms Ordered XL Quantity needed Price per uniform Total Students: XS S M L XL XXL