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

User Generated

znwobe00

Computer Science

Concordia University

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

Students Instrument Piano Teacher Total Students StudentID 000001 000002 000003 000004 000005 000006 000007 000008 000009 000010 000011 000012 000013 000014 000015 000016 000017 000018 000019 000020 000021 000022 000023 000024 000025 000026 000027 000028 000029 000030 000031 000032 000033 000034 000035 000036 000037 000038 000039 First Name Zia Pandora Inez Jael Minerva Summer Quyn Violet Clark Hyatt Plato Ishmael Len Paki Baxter Pamela Tana Kennan Matthew Anastasia Camille Rina Meghan Erich Lee Cameran Cameron Amena Joshua Meredith Marcia Tiger Belle Kimberly Elmo Dara Ginger Steven Adara Last Name White Brock Clements Boyer Burton Sweeney Ortiz Ward Ward Waters Lee Valenzuela Schmidt Kline Maddox Lambert Hardin Meyer Pickett Marshall Berger Stanley Serrano Miller Hayden Harvey Burgess Sanchez Caldwell Summers Kirk Haley Solomon Yates Sexton Watts Decker Petty Mcclain Instrument Piano Drums Viola Bass Trumpet Drums Trumbone Guitar Viola Drums Violin Violin Oboe Guitar Trumbone Cello Viola Piano Drums Cello Guitar Guitar Piano Flute Violin Drums Clarinet Guitar Tuba Guitar Piano Cello Trumbone Piano Viola Oboe Flute Cello Flute Teacher Competition? Yes Jenny Teacher Jenny Chuck Chuck Chuck Mark Anne Chuck Chuck Mark Jenny Jenny Jenny Jenny Jenny Jenny Jenny Anne Chuck Mark Jenny Jenny Chuck Chuck Anne Chuck Chuck Debbie Anne Mark Jenny Debbie Mark Chuck Mark Jenny Debbie Mark Mark Chuck Uniform Size Chuck Competition? Yes Yes Yes No No Yes No Yes Yes Yes No Yes No Yes Mark Uniform Size XL M L M L XL XS XL XL XL XXL S XS L M M L XXL XS XL XXL Yes No Yes No Yes No No No XXL XXL XXL XXL S XXL M Yes No No No No XS XL L L XL XS 000040 000041 000042 000043 000044 000045 000046 000047 000048 000049 000050 000051 000052 000053 000054 000055 000056 000057 000058 000059 000060 000061 000062 000063 000064 000065 000066 000067 000068 000069 000070 000071 000072 000073 000074 000075 000076 000077 000078 000079 000080 000081 000082 000083 000084 000085 000086 Charlotte Laith Madeson Bethany Mari Gail Melvin Lucas Charissa Grace Octavia Steel Gemma Germaine Alea Stephen Marshall Merrill Ali Rinah Lisandra Norman Nicholas Jerry Nicole Fitzgerald Alexis Carlos Daquan Rogan James Oprah Lani Axel Evangeline Drake Rylee Kim Priscilla Risa Slade Mara Davis Galvin Paula Bertha Inez Powell Sanford Salazar Lowery Sweet Valentine Merrill Stout Sharpe Reeves Frederick Cleveland Case Mcmillan Pickett Cross Jordan Hull Bernard Parks Lane Grimes Sloan Payne Clemons Stephens Barker Rose Cabrera Hebert Franklin Snow Golden Ewing Gaines Munoz Freeman Howard Bush Lowery Hancock Mosley Schmidt Todd Eaton Rios Chapman Viola Violin Cello Trumpet Violin Viola Cello Viola Clarinet Oboe Viola Flute Drums Flute Bass Guitar Clarinet Clarinet Oboe Tuba Violin Cello Tuba Tuba Trumbone Tuba Tuba Tuba Drums Drums Flute Bass Flute Trumpet Clarinet Trumpet Cello Flute Trumpet Flute Cello Trumbone Guitar Piano Guitar Bass Bass Chuck Jenny Anne Chuck Jenny Debbie Anne Mark Chuck Chuck Chuck Mark Anne Chuck Chuck Anne Debbie Jenny Jenny Jenny Jenny Jenny Jenny Debbie Chuck Debbie Mark Jenny Jenny Anne Mark Jenny Anne Chuck Anne Chuck Chuck Chuck Debbie Jenny Jenny Jenny Jenny Debbie Chuck Chuck Mark No Yes No No No Yes Yes No No Yes Yes Yes Yes Yes Yes Yes No No Yes No Yes No No No No No Yes Yes No XXL XXL M L S S M XXL M M L L M M S S XXL XXL XXL M XXL S L M S S M L XL XXL XS XS XXL S XL XS XXL S S L XL L XL XS L M L 000087 Xerxes Atkins Tuba Chuck No M Permission Slip Anne Permission Slip No Yes Yes Yes Yes Yes Yes Yes Yes Yes No Yes No Yes No Yes Yes No Yes Yes Yes No No Yes No Yes No Student Since Level Debbie Student Since 7/8/2015 2/18/2018 11/4/2015 12/29/2015 7/13/2014 7/9/2017 6/9/2016 11/7/2015 5/30/2015 11/29/2017 11/27/2014 4/6/2016 10/20/2017 3/28/2016 3/12/2017 4/4/2016 3/14/2018 5/28/2015 6/17/2016 1/3/2015 2/6/2016 1/3/2015 6/1/2018 12/29/2017 9/24/2015 12/19/2014 11/18/2016 5/24/2015 10/12/2017 12/19/2017 6/20/2018 5/10/2017 3/7/2015 2/22/2018 9/1/2016 12/27/2014 6/26/2016 12/25/2017 3/9/2017 Price Per Hour Total Skill level Advanced Intermediate Intermediate Expert Beginner Expert Advanced Advanced Expert Beginner Expert Expert Beginner Intermediate Expert Beginner Intermediate Beginner Advanced Advanced Beginner Advanced Advanced Expert Expert Advanced Beginner Expert Intermediate Intermediate Expert Intermediate Price Per Hour Yes Yes No No Yes Yes Yes Yes Yes Yes No Yes Yes No Yes No Yes No No No No Yes No No Yes No Yes Yes No 3/20/2017 5/5/2016 12/10/2016 11/5/2015 9/15/2017 5/8/2017 7/1/2015 9/13/2016 12/23/2014 2/16/2015 6/23/2016 9/22/2017 3/8/2017 1/17/2016 7/15/2016 12/4/2016 6/22/2016 9/29/2015 10/10/2017 11/4/2016 6/12/2018 6/9/2017 2/6/2016 10/16/2016 8/23/2015 12/31/2014 8/17/2015 2/28/2018 9/6/2015 12/15/2016 8/16/2015 2/15/2018 3/20/2015 5/7/2017 3/22/2017 10/13/2015 9/20/2015 7/13/2017 12/2/2015 11/4/2016 4/19/2016 12/4/2015 10/2/2016 1/31/2016 7/1/2015 7/7/2015 8/9/2017 Intermediate Advanced Intermediate Beginner Beginner Intermediate Expert Advanced Expert Advanced Intermediate Intermediate Beginner Advanced Expert Advanced Advanced Advanced Advanced Intermediate Beginner Intermediate Beginner Intermediate Beginner Beginner Intermediate Intermediate Advanced Advanced Intermediate Advanced Advanced Intermediate Advanced Advanced Intermediate Beginner Advanced Advanced No 12/17/2015 Expert Student Schedule Date 1/3/2018 1/4/2018 1/7/2018 1/8/2018 1/12/2018 1/16/2018 1/17/2018 1/18/2018 1/18/2018 1/19/2018 1/21/2018 1/25/2018 1/26/2018 1/28/2018 1/31/2018 2/3/2018 2/9/2018 2/10/2018 2/11/2018 2/14/2018 2/16/2018 2/20/2018 2/23/2018 3/10/2018 3/18/2018 3/20/2018 3/20/2018 3/22/2018 3/22/2018 3/29/2018 3/30/2018 4/2/2018 4/2/2018 4/11/2018 4/13/2018 4/15/2018 4/15/2018 4/16/2018 4/19/2018 4/19/2018 4/20/2018 4/28/2018 5/5/2018 5/6/2018 StudentID 000034 000027 000018 000001 000033 000010 000018 000012 000007 000028 000022 000023 000086 000015 000011 000008 000019 000011 000032 000008 000001 000016 000031 000002 000007 000034 000023 000021 000014 000014 000004 000023 000006 000032 000031 000009 000022 000006 000018 000013 000033 000032 000032 000008 Student Kimberly Cameron Kennan Zia Belle Hyatt Kennan Ishmael Quyn Amena Rina Meghan Inez Baxter Plato Violet Matthew Plato Tiger Violet Zia Pamela Marcia Pandora Quyn Kimberly Meghan Camille Paki Paki Jael Meghan Summer Tiger Marcia Clark Rina Summer Kennan Len Belle Tiger Tiger Violet Teacher Debbie Mark Jenny Chuck Chuck Chuck Anne Jenny Jenny Chuck Debbie Jenny Debbie Anne Debbie Debbie Chuck Jenny Chuck Chuck Mark Chuck Debbie Mark Debbie Debbie Anne Mark Mark Mark Anne Chuck Debbie Chuck Chuck Debbie Jenny Chuck Chuck Anne Chuck Debbie Jenny Anne Lesson Length Skill Level 1.50 0.50 0.50 1.50 0.50 1.50 2.00 1.50 1.50 1.00 1.00 1.50 1.00 1.50 0.50 1.00 1.50 1.00 1.00 0.50 1.00 1.00 1.00 2.00 0.50 0.50 0.50 2.00 2.00 1.00 0.50 1.00 1.50 1.00 0.50 1.50 1.00 2.00 2.00 1.50 1.50 1.00 1.00 1.00 Total Fee 5/7/2018 5/7/2018 5/8/2018 5/9/2018 5/12/2018 5/15/2018 5/17/2018 5/17/2018 5/21/2018 5/21/2018 5/23/2018 5/29/2018 6/2/2018 6/4/2018 6/9/2018 6/11/2018 6/12/2018 6/15/2018 6/16/2018 6/18/2018 6/23/2018 6/29/2018 7/6/2018 7/18/2018 7/18/2018 7/22/2018 7/22/2018 7/26/2018 7/26/2018 7/27/2018 7/28/2018 7/28/2018 7/30/2018 8/1/2018 8/1/2018 8/3/2018 8/4/2018 8/5/2018 8/6/2018 8/6/2018 8/9/2018 8/13/2018 8/13/2018 8/14/2018 8/24/2018 8/25/2018 8/30/2018 000018 000025 000008 000028 000016 000016 000029 000001 000010 000017 000021 000001 000026 000022 000008 000028 000028 000006 000010 000026 000006 000031 000033 000028 000033 000034 000007 000018 000016 000015 000002 000001 000006 000020 000002 000024 000001 000022 000002 000022 000025 000019 000019 000021 000016 000008 000008 Kennan Lee Violet Amena Pamela Pamela Joshua Zia Hyatt Tana Camille Zia Cameran Rina Violet Amena Amena Summer Hyatt Cameran Summer Marcia Belle Amena Belle Kimberly Quyn Kennan Pamela Baxter Pandora Zia Summer Anastasia Pandora Erich Zia Rina Pandora Rina Lee Matthew Matthew Camille Pamela Violet Violet Anne Mark Debbie Chuck Anne Mark Anne Anne Mark Chuck Anne Debbie Debbie Anne Chuck Debbie Anne Mark Chuck Chuck Debbie Anne Mark Mark Anne Jenny Debbie Debbie Mark Mark Anne Mark Mark Debbie Jenny Anne Anne Debbie Jenny Chuck Debbie Chuck Debbie Chuck Jenny Anne Chuck 2.00 0.50 2.00 2.00 1.00 1.50 0.50 2.00 0.50 2.00 0.50 1.00 1.50 0.50 0.50 1.50 1.50 1.00 1.00 2.00 1.00 2.00 1.50 0.50 0.50 0.50 2.00 2.00 1.50 1.00 1.00 0.50 1.00 1.50 2.00 1.50 0.50 1.00 1.50 2.00 1.00 1.00 1.50 0.50 2.00 2.00 0.50 8/31/2018 9/3/2018 9/4/2018 9/5/2018 9/5/2018 9/7/2018 9/8/2018 9/11/2018 9/18/2018 9/24/2018 9/25/2018 9/29/2018 10/3/2018 10/5/2018 10/5/2018 10/10/2018 10/16/2018 10/18/2018 10/18/2018 10/23/2018 10/24/2018 10/25/2018 10/26/2018 11/5/2018 11/5/2018 11/12/2018 11/13/2018 11/19/2018 000035 000029 000013 000026 000012 000011 000015 000025 000007 000009 000017 000008 000025 000031 000008 000006 000033 000086 000011 000034 000004 000002 000023 000029 000001 000026 000032 000010 Elmo Joshua Len Cameran Ishmael Plato Baxter Lee Quyn Clark Tana Violet Lee Marcia Violet Summer Belle Inez Plato Kimberly Jael Pandora Meghan Joshua Zia Cameran Tiger Hyatt Debbie Mark Jenny Mark Mark Chuck Mark Mark Debbie Chuck Jenny Debbie Jenny Debbie Chuck Anne Debbie Debbie Debbie Mark Anne Mark Jenny Debbie Jenny Debbie Chuck Chuck 1.50 1.00 1.00 2.00 2.00 0.50 0.50 0.50 1.50 2.00 2.00 2.00 1.00 0.50 1.50 0.50 1.50 1.00 1.50 1.00 1.00 1.00 0.50 0.50 0.50 1.50 1.00 1.00 Data Inputs Annual Competition 12/1/2018 Lesson Pricing 0.5 Beginner Intermediate Advanced Expert $ 1.0 15 $ 20 25 35 1.5 30 $ 25 30 40 Competition Entry Fee Early Bird $ 250 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 0 $ 15 20 25 XS 42.23 $ 40.59 39.01 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 ompetition Entry Fee Regular $ 500 Late $ 750 Student Report Student ID Teacher Average Fee Skill Level Total Lessons Total Paid this Year Bonus Lessons 000018 Competiton Fees Registration Type Travel Type Uniform Size Uniform Fee $ Registration Fee Transportation Fee Total $ Regular Bus - - Competition Report Student ID 000001 000002 000003 000006 000008 000010 000016 000018 000020 000022 000025 000028 000033 000044 000049 000053 000057 000059 000060 000061 000062 000067 000068 000072 000074 000083 000084 Student First Name Student Last Name Permission Slip? Uniform Size Total Students: Ready to go! Total Uniforms Ordered XXL Quantity needed Price per uniform Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_CH05_GRADER_PS2_AS - Lessons 1.4 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. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the downloaded Excel file named e03ch05_grader_a1_Lessons.xlsx. Save the file with the name e03ch05_grader_a1_Lessons_LastFirst replacing 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 4 5 6 7 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. 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. 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. 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. 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. Updated: 05/02/2017 1 4.000 8.000 8.000 8.000 8.000 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step 8 9 Excel Project Points Possible Instructions 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. 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. 8.000 9.000 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. 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. 10 11 12 13 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. 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.) 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. 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. 8.000 8.000 8.000 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 Updated: 05/02/2017 2 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 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 Updated: 05/02/2017 3 100.000 Current_Instruction.docx
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & 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...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags