# CSC 210 Concordia University. St. Paul Local Music Studio Project Excel

*label*Programming

*timer*Asked: Apr 7th, 2019

*account_balance_wallet*$10

### Question Description

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. Furthermore, the teachers want to be able to track how many students they have and also to give a free lesson to students for every five paid lessons, with a maximum of two free lessons. You have been asked to help construct a workbook that the owners can use to help manage their data.*

*MAKE SURE TO USE THE SAME FILE*

### Unformatted Attachment Preview

Purchase answer to see full attachment

## Tutor Answer

And my answer is 100% complete done now.

1) The file is saved with the name e03ch05_grader_a1_Lessons_LastFirst.

2) The range A7:K94 is named Student_Data as shown by the following screenshot:

A named range for each column in the Student_Data can be created as follow:

Similarly, we have the names for other columns:

and so on until the last column

3) A named range using Lesson_Pricing as the name can be created for the range A6:E10 of the

DataInputs worksheet as shown by the following screenshot:

Similarly, a name range as Trans_Fees can be created for the range A13:E17 of the DataInputs

worksheet as shown by the following screenshot:

Finally, a name range as Uniform_Fees can be created for the range A20:G24 of the DataInputs

worksheet as shown by the following screenshot:

A name range using Entry_Fee can be created as follow:

4) The COUNTIF function is used in cell E5 to count the Teacher field in the Student_Data table

as shown below:

The DCOUNTA function is added to cell K5 as shown by the following screenshot:

5) The VLOOKUP function nested in an IFERROR function is added for the cell K8 to retrieve the

price per hour as follow:

And the formula is copied through cell K94 as shown below:

6) The MATCH function nested in an INDEX function in cell F3 is added on the LessonData

worksheet as shown below:

And the formula is copied through cell F121 as follow:

7) The MATCH function nested in an INDEX function for the cell G3 can be added as follow:

And the formula is copied through cell G121 as follow:

8) The VLOOKUP function to look up the teacher’s name in the Student_Data table can be added

in cell B3 as follow:

The AVERAGEIF function to calculate the teacher’s (Teacher) average can be used as follow:

The VLOOKUP function is added to cell B5 of the Report worksheet as follow:

9) The COUNTIF function is added to cell B6 of the Report worksheet as shown by the following

screenshot:

The SUMIF function is then added to cell B7 as shown by the following screenshot:

A two nested IF function was added in cell B8 as follow:

10) The VLOOKUP function is added in cell E5 as follow:

The HLOOKUP function is added in cell E7 as follow:

The COUNTIF function is added for cell I13 as follow:

11) The MATCH function nested in an INDEX function was added to cell I14 as follow:

12) A VLOOKUP function was added in cell B12 as follow:

Similarly, a VLOOKUP function can also be added in cell C12 as follow:

13) A VLOOKUP function was added in cell D12 to show whether the student has the permission

slip or not can be shown by the following screenshot:

A VLOOKUP function nested in an IF function was added in cell E12 as follow:

14) An AND function nested in an IF function was added to cell F12 as shown below:

The cells B12:F12 were highlighted and then the formulas were copied through cell range

B38:F38 as shown below:

15) A COUNIF function was added to cell F39 as shown in the below screenshot:

16) A MATCH function nested in an INDEX function was added in cell E8 as follow:

17) The Excel file is completed and saved.

Students

Instrument

Piano

Teacher

Teacher

Competition?

Yes

Jenny

Total Students

Uniform Size

Chuck

Mark

0

StudentID First Name Last Name

Instrument

Teacher

Competition?

Uniform Size

000001

000002

000003

000004

000005

000006

000007

000008

000009

000010

000011

000012

000013

000014

000015

000016

000017

000018

000019

0...

*flag*Report DMCA

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors