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