YO$22\_$Excel$\_$Ch$05\_$Assessment$\_$Music$\_$Lessons

Project Description:

A local music studio, Fiddlers, has a large number of clients who take lessons on a regular basis. Additionally, some students are members of Fiddlers' competition band, which travels throughout the country and competes with some of the best musicians in the world. The owners need to be able to calculate the revenue generated through lessons and specific students. Additionally, they need to be able to calculate how much each member of the competition band will have to pay toward the next trip. The owners also need to be able to keep track of the cost of competition band uniforms, permission slips, and attendees. You have been asked to help construct a workbook that the owners can use to help manage their data.

Steps to Perform:

$\backslash$table$[[$Step$,$Instructions,$\backslash$table$[[$Points$],[$Possible$]]],[1,\backslash$table$[[$Start Excel. Download and open the file named Excel$\_$Ch$05\_$Assessment$\_$MusicLessons.xlsx$.],[$Grader has automatically added your last name to the beginning of the filename.$]],0],[2,\backslash$table$[[$The DataTables worksheet contains $4$ tables listing the various prices and fees that Fiddlers$],[$charges their clients. It is common practice to have all pricing tables on one worksheet.$],[$Creating named ranges for these tables will make it easier to refer to them from other$],[$worksheets$.],[$On the DataTables worksheet, created the following named ranges:$],[$Range: B$5$:E$8$; Name: Lesson$\_$Pricing$],[$Range: H$4$:J$5$; Name: Entry$\_$Fees$],[$Range: B$12$:E$15$; Name: Trans$\_$Fees$],[$Range: B$19$:G$22$; Name: Uniform$\_$Fees$]],5],[3,\backslash$table$[[$The StudentData worksheet contains data about each of Fiddlers student clients. The lesson$],[$price per hour varies based on the student's skill level.$]],7]]$

In cell $13,$ enter an INDEX function with a$,$MATCH function to retrieve the lesson price for $1$ hour, located in column $2$ in the Lesson$\_$Pricing named range, for each student on the basis of the student's skill level, located in the Skill level field. 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 $134.$

$4$ Creating a named range for all of the student data will make it easier to retrieve data about

$3$

each student in the table from other worksheets.

On the StudentData worksheet, created a named range, Student$\_$Data, for cells A$3$:I$34.$

The LessonData worksheet contains the records for select students. However, their skill level

$5$

is not included in the data set but is essential to calculate how much they owe for their lesson.

In cell F$3,$ enter a VLOOKUP function that will retrieve the skill level for the StudentID in cell B$3$ from the Student$\_$Data named range.

If the student does not have a skill level entered in the Student$\_$Data named range, the VLOOKUP will return a value of $0.$ Incorporate an IF function in the formula so that if the value returned is a $0,$ display a blank value $("\text{'}$I$)$ instead, otherwise display the value returned by the VLOOKUP function.

Copy the formula down through F$29.$