Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1 Start Excel. Open the downloaded Excel file named e03ch05_grader_a1_Lessons.xlsx. Save the file with the name e03ch05_grader_a1_Lessons_LastFirstreplacing LastFirst with your last and first name. In the Security Warning bar, click Enable Content.
0.000
2 On the StudentData worksheet, select the range A7:K94, and then name the range Student_Data. Create a named range for each column in the Student_Data named range, using the top row as the range values.
2.000
3 On the DataInputs worksheet, 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 H6:J7, and then create a named range using Entry_Fee as the name.
4.000
4 On the StudentData worksheet, in cell E5, add a COUNTIF function to count the Teacher field in the Student_Data table that meets the filter criteria in cell E4. Copy the formula through cell I5.
In cell K5, add a DCOUNTA function to count the StudentID field in the Student_Data table that meets the filter criteria in the range D1:K2.
8.000
5 In cell K8, add a VLOOKUP function nested in an IFERROR function to retrieve the price per hour, located in column 3 in the Lesson_Pricing named range, for each student on the basis of 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 through cell K94.
8.000
6
On the LessonData worksheet, in cell F3, add a MATCH function nested in an INDEX function to retrieve Skill_level of each student listed in column B (StudentID). 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 through cell F121.
8.000
7 On the LessonData worksheet, 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 DataInputs worksheet for each student listed in column B depending on their lesson length and skill level. 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 down to G121.
8.000
8 On the Report worksheet, in cell B3, add a 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, add an AVERAGEIF function to calculate the teacher’s (Teacher) average in the Price_Per_Hour field for the teacher listed in cell B3.
In cell B5, add a 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.
8.000
9 In cell B6, add a COUNTIF function to count the StudentID field on the LessonData worksheet that meets the filter criteria in cell B2.
In cell B7, add a SUMIF function to sum the Total Fee field on the LessonData worksheet that meets the filter criteria in cell B2.
In cell B8, add a two nested IF function to determine how many free lessons the student has earned. If the student has had fewer than 5 total lessons, display 0 earned. If the student has had less than 10 lessons, display 1 earned; otherwise, display 2 earned.
9.000
10 In cell E5, add a VLOOKUP function to look up the student’s uniform size in the Student_Data table for the student listed in cell B2. The function should be looking for an exact match.
In cell E7, add a HLOOKUP function to retrieve the student’s registration fee in the Entry_Fee named range for the registration type in cell E3. The function should be looking for an exact match.
In cell I13, add a COUNTIF function to count the number of uniforms in the Uniform_Size column of the Student Data table that meet the filter criteria in cell H12.
8.000
11 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 DataInputs worksheet that meets 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. (Hint: Set the match type to 1.)
8.000
12 In cell B12, add a 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.
In cell C12, add a VLOOKUP function to retrieve the student’s last name in the Student_Data table that meets the filter criteria in cell A12. The value should be looking for an exact match.
8.000
13 In cell D12, add a 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.
In cell E12, add a 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. The value should be looking for an exact match.
6.000
14 In cell F12, add an AND function nested in an IF function to determine whether 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.
Highlight B12:F12 and then copy the formulas through cell range B38:F38.
6.000
15 In cell F39, add a COUNTIF function to determine how many students are currently traveling to the competition as indicated by Yes in cell range F12:F38.
5.000
16 In cell E8, add a MATCH function nested in an INDEX function to retrieve the transportation fee located in the Trans_Fee table on the DataInputs worksheet that meets the filter criteria in cell E4 and F39.
4.000
17 Click Save, exit Excel, and then submit your file as directed by your instructor.
0.000
Total Points
100.000
ciciliany