INFO6069 – HEALTH REPORTING TOOLS
Lab 3 – Summer 2023
Download the file Lab3.xlsx and save it to your computer.
Rename the file Lab3_YourFirstName (It is important that you save the file with the required name)
The file has one worksheet named Data.
Task 1
Using the VLOOKUP function and the Table in the M2:O9 range (CODES)
•
•
•
•
•
Complete the worksheet by filling in the areas in light blue – Product Code and Sales Price
Using the Sales Price and Units sold, calculate the Sales Value
Compute the commission using the rate provided in O11 – use absolute referencing.
Add a total in row 102 to display the Total Sales Value and the Total Commission
Insert a footer with your full name.
Save the Workbook
Create four additional Worksheets – this could be done by clicking and holding the CTRL key and
dragging the data worksheet to the right with your mouse. (Alternately, you could right click on the
Worksheet Tab Data, select Move or Copy, Move to End and check mark ‘Create a Copy’)
Rename the four worksheets – Sub-Total, Custom-Sort, Functions and Pivot. You should now have five
sheets in total.
Task 2:
Using the Sub-Total Worksheet
•
Create a sub-total to show the Sum of Sales Value and the Sum of Commission for each Product
Group
Task 3:
Using the Custom-Sort Worksheet
•
•
Sort the data in the following order: North, South, East and West
Filter the data to show only Sales Value > 200,000
Task 4:
Using the Functions Worksheet:
Enter the following text starting from M14 and ending in M23
•
•
•
Total Sales Value of Consoles sold in the North
Total Sales Value of Sales in the North >20,000
Total Commission paid in the East region
1
July 2023
INFO6069 – HEALTH REPORTING TOOLS
•
•
•
•
•
•
•
Average Commission paid for TV sales in the West.
Average Commission paid to Employees older than 30 years.
Average Sales Value of Sales in the East made by Employees >= 35 years
Number of Employees under 25 years in the North
Number of Employees who sold Home Security products.
Maximum Sales by employees under 25 years of age (MAXIFS)
Lowest Sales Value in the West (MINIFS)
After recording the text in cells M14:M23
•
Use the appropriate functions in cells L14:L23 to determine the results associated with the text
to the right.
Task 5
Using the Pivot work sheet, create two Pivot tables and accompanying Pivot Charts
In the first table
•
•
•
•
•
Show the Sum of Sales by Product Group and Region
Format the table to show the Sales Values in currency.
Include the highest 5 Product Groups by Sales Values
Create a Pivot Chart – appropriate for the data; insert a header; remove the Field Buttons
Name the sheet Chart 1
In the second table
•
•
•
•
•
•
•
Show the Average Commission by Age
Group the age in class-intervals of 5
Create a Pie Chart of the data.
Show the percentages outside the Pie slices.
Give the chart an appropriate title.
Remove the field buttons
Rename the Worksheet Chart 2
Your workbook should now have seven Worksheets.
After completing all Tasks, save and upload your Lab3_YourName.xlsx file to the submission folder.
Refer to your calendar for due date.
2
July 2023
First Name
Last Name
Age
Date Joined
Region
Code
Husein
Gigi
Oran
Gunar
Crissie
Dennison
Enoch
Constantino
Augar
Bohling
Buxcy
Cockshoot
Cordel
Crosswaite
Dowrey
Espley
30
33
38
31
32
26
27
30
1/12/2019 East
1/8/2019 East
3/18/2020 East
5/11/2019 East
8/19/2019 East
9/16/2019 East
8/17/2019 East
8/14/2019 East
WH
CO
CO
CO
HO
GA
WH
WH
Allene
My
Hogan
Kassi
Andria
Bev
Karlen
Shari
Jan
Kaine
Vic
Jehu
Verney
Shayne
Teressa
Kellsie
Curtice
Rafaelita
Ches
Camilla
Agnes
Florinda
Esmaria
Murry
Barr
Glennis
Lindy
Mollie
Hannis
Alta
Halimeda
Ewart
Drusy
Virginia
Beverie
Marney
Gobbet
Hanscome
Iles
Jonson
Kimpton
Lashley
McCaffrey
McNee
Morforth
Padly
Radolf
Rudeforth
Sloegrave
Stegel
Udden
Waby
Advani
Blaksland
Bonnell
Castle
Collicott
Crace
Denecamp
Dryburgh
Faughny
Fussen
Guillet
Hanway
January
Kaszper
Kuscha
Laphorn
MacCombe
McConville
Moffet
O’Breen
36
33
30
32
30
29
34
21
28
20
24
34
19
42
33
20
30
38
37
33
27
32
27
32
39
21
22
34
29
27
30
27
28
22
28
21
5/29/2019 East
11/27/2019 East
4/16/2020 East
4/19/2020 East
11/20/2019 East
9/28/2019 East
3/20/2019 East
8/29/2019 East
2/5/2019 East
3/20/2019 East
7/14/2019 East
8/20/2019 East
4/4/2019 East
12/19/2019 East
6/2/2019 East
1/2/2020 East
8/5/2019 North
3/5/2020 North
11/28/2019 North
1/26/2020 North
11/5/2019 North
1/23/2019 North
7/6/2019 North
5/19/2019 North
2/6/2020 North
5/28/2019 North
3/7/2019 North
11/22/2019 North
2/5/2020 North
7/27/2019 North
4/27/2020 North
10/30/2019 North
3/16/2020 North
3/11/2019 North
12/13/2019 North
5/17/2020 North
WH
TV
GA
TA
TA
HO
CO
GA
CO
TA
CO
TV
TA
CO
WH
TV
TA
PH
TV
CO
TA
CO
GA
PH
TA
HO
WH
GA
TA
TV
PH
WH
TV
WH
TA
GA
Product Group
Merrilee
Tracy
Niall
Oby
Tawnya
Madelene
Leilah
Erin
Kath
Hyacinthie
Caro
Torrance
Kaye
Dyna
Sibyl
Archibald
Moore
Janene
Tatum
Brigid
Simon
Hinda
Mahalia
Kissiah
Myer
Dell
Wilone
Zach
Ebonee
Bili
Roddy
Van
Mallorie
Cherlyn
Brien
Gretchen
Elia
Violante
Hoyt
Murry
Valentia
Bernie
Mollie
Collin
Benny
Lilyan
Orton
Plenty
Renad
Selesnick
Sorrel
Tickel
Upcott
Yesinin
Androsik
Bletsoe
Braybrooke
Chappel
Collier
Crocroft
Doucette
Dunkirk
Filliskirk
Gligoraci
Hairsine
Hush
Jeffrey
Kembery
Label
Larcher
Maydway
McCory
Molloy
O’Kielt
Polon
Roxburgh
Sizey
Speechley
Tuxwell
Waber
Barter
Boise
Callow
Cockton
Courtonne
D’Alesco
Dryburgh
Etteridge
Gorges
Hanway
Jagson
Karolovsky
Klimpt
Livick
40
36
29
34
36
25
34
33
25
20
40
33
24
31
33
35
32
28
28
34
40
25
27
23
30
26
43
26
30
43
33
25
30
28
31
21
33
34
32
37
37
28
20
24
37
19
21
6/28/2020 North
3/29/2019 North
5/30/2019 North
10/2/2019 North
4/11/2019 North
12/14/2019 North
3/6/2019 North
11/20/2019 South
1/6/2019 South
6/7/2019 South
1/4/2019 South
9/25/2019 South
2/28/2019 South
12/12/2019 South
3/5/2020 South
12/15/2019 South
2/11/2019 South
4/17/2019 South
4/28/2020 South
2/2/2019 South
4/22/2020 South
12/17/2019 South
8/17/2019 South
3/1/2019 South
4/16/2020 South
9/12/2019 South
10/29/2019 South
2/3/2019 South
11/20/2019 South
8/28/2019 South
9/7/2019 South
11/13/2019 South
10/15/2019 South
9/29/2019 West
10/12/2019 West
10/27/2019 West
2/18/2020 West
12/9/2019 West
3/26/2019 West
11/20/2019 West
3/12/2019 West
9/10/2019 West
11/10/2019 West
11/5/2019 West
8/14/2019 West
3/8/2020 West
8/2/2019 West
TA
GA
PH
CO
GA
WH
PH
GA
GA
WH
PH
CO
TV
TV
GA
PH
TV
CO
WH
GA
GA
TA
TA
TA
HO
GA
HO
HO
TA
GA
WH
TV
PH
GA
HO
CO
WH
CO
CO
PH
TA
TV
HO
CO
HO
TA
WH
Madge
Gisella
Ambros
Bennie
William
Gray
Dotty
Maximo
Kelci
McCloughen
Mewe
Murthwaite
Pepis
Reeveley
Seamon
Strutley
Ungerecht
Walkden
32
23
46
36
33
36
31
30
21
2/1/2020 West
6/17/2019 West
1/16/2020 West
4/20/2020 West
5/9/2019 West
4/25/2019 West
6/29/2019 West
4/24/2020 West
1/20/2020 West
HO
GA
PH
CO
PH
PH
HO
GA
TV
Average
Sales Price
Units Sold
198
106
210
110
46
230
45
54
158
270
30
96
38
254
30
800
81
286
45
61
292
250
95
30
354
595
115
175
151
181
200
234
321
75
340
170
300
195
139
315
57
187
174
165
Sales Value
Commission
Code
CO
GA
HO
PH
TA
TV
WH
Product Group
Consoles
Gaming PCs
Home Security
Phones
Tablets
TVs
Whiteware
Commission
632
400
525
80
40
95
74
280
170
69
19
25
354
50
70
93
106
75
82
135
15
72
521
137
82
170
254
30
364
140
75
14
214
410
25
162
256
140
150
247
56
60
74
55
256
132
46
150
150
153
300
268
254
80
335
27
Average
Sales Price
1,500.00
1,200.00
2,750.00
850.00
800.00
1,200.00
1,650.00
2%
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
