Start Excel. Open the downloaded Excel file namede03ch05_grader_a1_Lessons.xlsx. Save the file with the namee03ch05_grader_a1_Lessons_LastFirstreplacingLastFirstwith your last and first name. In the Security Warning bar, click Enable Content.

002On the StudentData worksheet, select the range A7:K94, and then name the rangeStudent_Data. start a named range for each column in the Student_Data named range, using the top row as the range values.

123On the DataInputs worksheet, select the range A3:B3, and then start anamed range using the left column as the name.

Select the range A6:E10, and then start

a named range usingLesson_Pricingas the name.

Select the range A13:E17, and then start a named range usingTrans_Feesas the name.

Select the range A20:G24, and then start a named range usingUniform_Feesas the name.

Select the range H6:J7, and then start a named range usingEntry_Feeas the name.

3.544On the StudentData worksheet, in cell E5, add a COUNTIF function to count theTeacherfield 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 theStudent_Datatable that meets the filter criteria in the range D1:K2.

085In cell K8, add a VLOOKUP function nested in an IFERROR function to retrieve the price per hour, located in column 3 in theLesson_Pricingnamed 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.

0.686On 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.

0.587On 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.

088On the Report worksheet, in cell B3, add a VLOOKUP function to look up the teacher's name in theStudent_Datatable 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 thePrice_Per_Hourfield for the teacher listed in cell B3.

In cell B5, add a VLOOKUP function to look up the student's skill level in theStudent_Datatable for the student listed in cell B2. The value should be looking for an exact match.

089In 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 than5total lessons, display0 earned. If the student has hadless than10lessons, display1 earned; otherwise, display2 earned.

0910In cell E5, add a VLOOKUP function to look up the student's uniform size in theStudent_Datatable 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 theEntry_Feenamed 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 theUniform_Sizecolumn of the Student Data table that meet the filter criteria in cell H12.

0811In 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.)

0812In cell B12, add a VLOOKUP function to retrieve the student's first name in theStudent_Datatable 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 theStudent_Datatable that meets the filter criteria in cell A12. The value should be looking for an exact match.

0813In cell D12, add a VLOOKUP function to retrieve whether or not the student has a permission slip on file in theStudent_Datatable 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.

0.25614In 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 displayYesin the field; otherwise, displayNo.

Highlight B12:F12 and then copy the formulas through cell range B38:F38.

0615In cell F39, add a COUNTIF function to determine how many students are currently traveling to the competition as indicated byYesin cell range F12:F38.

0516In 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.

0417Click Save, exit Excel, and then submit your file as directed by your instructor.

00

Score Summary

Report Run November 17, 2019

Total Score

5%