Statistics help for my university child

[Kristy Cathro]

Hi,

I am of NO help to my daughter who is doing a Bachelor of Business studies. She has come home with statistics homework and the only thing I could do to help was create a pivot table. Please can you help us and me to explain how to do the following tasks.

Question 1 below, I have done what I thought was correct. Question 2 below; I have no idea how to help. Can you help me please?

Thanks
Kristy

This is the data she was given.

Carat	Colour	Clarity	Lab	Price(US$1000)
0.52	H	VVS1	DHC	2.191
0.56	F	VS2	DHC	2.2414
0.57	F	VS2	DHC	2.2792
0.52	E	VS2	DHC	2.3422
0.57	H	VVS1	DHC	2.3905
0.6	G	VS2	DHC	2.3947
0.5	G	VVS1	DHC	2.4024
0.55	G	VVS2	DHC	2.4703
0.62	I	VVS2	DHC	2.5305
0.61	H	VVS2	DHC	2.5312
0.65	I	VVS2	DHC	2.5501
0.56	F	VS1	DHC	2.5669
0.53	F	VVS2	DHC	2.5907
0.5	F	VVS1	DHC	2.6446
0.64	H	VVS2	DHC	2.6495
0.58	H	IF	DHC	2.6544
0.51	F	VVS1	DHC	2.6957
0.6	H	VVS1	DHC	2.7475
0.6	G	VS1	DHC	2.7475
0.53	F	VVS1	DHC	2.7965
0.66	H	VVS1	DHC	3.01
0.7	H	VS2	DHC	3.0422
0.71	H	VVS2	DHC	3.6351
0.73	F	VS2	DHC	3.7331
0.7	G	VVS2	DHC	3.857
0.72	H	VVS1	DHC	3.9634
0.7	G	VVS1	DHC	4.06
0.74	H	VVS1	DHC	4.0705
0.86	H	VS2	DHC	4.0845
0.73	G	VVS1	DHC	4.2287
0.8	H	VS1	DHC	4.2357
0.7	E	VVS2	DHC	4.3995
0.71	G	IF	DHC	4.4604
0.8	H	VVS2	DHC	4.4912
0.81	G	VS1	DHC	4.5465
0.82	F	VS2	DHC	4.6004
0.85	F	VS2	DHC	4.7635
0.7	E	VVS1	DHC	4.8069
0.8	G	VVS2	DHC	4.8335
0.8	F	VS1	DHC	4.8335
0.81	F	VS1	DHC	4.8916
0.81	E	VS2	DHC	4.8916
0.82	G	VVS2	DHC	4.9504
0.81	H	IF	DHC	5.1506
0.85	G	VVS1	DHC	5.3977
1	I	VS2	DHC	5.4726
1	I	VS1	DHC	5.6665
1.01	I	VS1	DHC	5.7225
0.85	F	VVS1	DHC	5.8513
1	I	VVS2	DHC	5.8604
0.8	F	IF	DHC	6.0277
0.81	E	VVS1	DHC	6.1005
1	I	VVS1	DHC	6.1516
1	H	VS2	DHC	6.1516
1.01	I	VVS1	DHC	6.2111
1.02	H	VS2	DHC	6.2713
1	H	VS1	DHC	6.3455
1.09	I	VVS2	DHC	6.3749
1.01	H	VS1	DHC	6.4071
1	G	VS2	DHC	6.4421
1.01	G	VS2	DHC	6.5051
1.01	H	VVS2	DHC	6.6031
1	H	VVS1	DHC	6.636
1.06	H	VVS2	DHC	6.923
1	G	VVS2	DHC	6.9272
1.01	G	VVS2	DHC	6.9951
1.01	F	VS1	DHC	7.1904
1	G	VVS1	DHC	7.315
1	F	VVS2	DHC	7.4116
1	E	VS1	DHC	7.4116
1.01	E	VS1	DHC	7.4844
1.02	F	VVS2	DHC	7.5572
1	E	VVS2	DHC	7.9933
1.01	E	VVS2	DHC	8.0717
1	F	VVS1	DHC	8.1872
1.01	F	VVS1	DHC	8.2677
1	D	VVS2	DHC	9.6425
1.01	D	VVS2	DHC	9.7363
1	E	VVS1	DHC	9.8357
0.33	I	VS2	GEM	0.7686
0.31	I	VS1	GEM	0.7882
0.31	H	VS2	GEM	0.7882
0.32	G	VS2	GEM	0.8414
0.34	H	VS2	GEM	0.8554
0.35	H	VS2	GEM	0.8785
0.3	G	VS1	GEM	0.882
0.34	G	VS2	GEM	0.8883
0.3	D	VS2	GEM	0.9114
0.34	H	VS1	GEM	0.9212
0.33	E	VS2	GEM	0.9289
0.34	G	VS1	GEM	0.987
0.37	H	VS1	GEM	0.994
0.37	F	VS2	GEM	0.994
0.31	G	VVS2	GEM	0.9989
0.31	F	VS1	GEM	0.9989
0.35	G	VS1	GEM	1.0129
0.32	F	VS1	GEM	1.0276
0.36	H	VVS2	GEM	1.0395
0.3	G	VVS1	GEM	1.057
0.3	E	VS1	GEM	1.057
0.4	H	VS1	GEM	1.0675
0.34	F	VS1	GEM	1.0857
0.31	E	VS1	GEM	1.0885
0.45	I	VS1	GEM	1.1004
0.35	F	VS1	GEM	1.1151
0.36	F	VS1	GEM	1.1445
0.31	D	VS1	GEM	1.1487
0.34	E	VS1	GEM	1.1851
0.35	E	VS1	GEM	1.2166
0.43	H	VVS2	GEM	1.2229
0.4	F	VS1	GEM	1.3377
0.41	F	VS1	GEM	1.3692
0.48	G	VVS2	GEM	1.7724
0.56	I	VVS2	GEM	2.0244
0.46	E	VVS2	GEM	2.0594
0.5	G	VS1	GEM	2.1112
0.51	G	VS1	GEM	2.2435
0.5	F	VS1	GEM	2.3051
0.53	G	VVS2	GEM	2.3849
0.57	H	VVS1	GEM	2.3905
0.52	F	VS1	GEM	2.3926
0.5	G	VVS1	GEM	2.4024
0.53	F	VS1	GEM	2.436
0.52	D	VS2	GEM	2.443
0.5	F	VVS2	GEM	2.4507
0.5	E	VS1	GEM	2.4507
0.5	E	VS1	GEM	2.4507
0.5	E	VS1	GEM	2.4507
0.55	G	VVS2	GEM	2.4703
0.51	F	VVS2	GEM	2.4969
0.55	H	IF	GEM	2.5235
0.55	F	VS1	GEM	2.5235
0.52	F	VVS2	GEM	2.5445
0.52	E	VS1	GEM	2.5445
0.57	G	VVS2	GEM	2.5557
0.56	F	VS1	GEM	2.5669
0.53	F	VVS2	GEM	2.5907
0.54	E	VS1	GEM	2.6369
0.59	G	VVS2	GEM	2.6411
0.51	F	VVS1	GEM	2.6957
0.7	I	VS2	GEM	2.7027
0.56	E	VS1	GEM	2.73
0.53	D	VS1	GEM	2.7447
0.54	F	VVS1	GEM	2.8462
0.55	E	VVS2	GEM	2.8966
0.73	I	VS1	GEM	2.9547
0.6	H	IF	GEM	3.0037
0.6	F	VS1	GEM	3.0037
0.66	H	VVS1	GEM	3.01
0.75	I	VS1	GEM	3.0485
0.63	G	VVS2	GEM	3.0807
0.74	H	VS2	GEM	3.2095
0.75	I	VVS2	GEM	3.2669
0.73	I	VVS1	GEM	3.3089
0.64	G	VVS1	GEM	3.3313
0.8	I	VS2	GEM	3.3824
0.82	I	VS2	GEM	3.4636
0.73	H	VS1	GEM	3.521
0.73	H	VS1	GEM	3.521
0.7	H	VVS2	GEM	3.5854
0.7	H	VVS2	GEM	3.5854
0.7	G	VS1	GEM	3.5854
0.7	G	VS1	GEM	3.5854
0.7	F	VS2	GEM	3.5854
0.7	F	VS2	GEM	3.5854
0.71	H	VVS2	GEM	3.6351
0.71	H	VVS2	GEM	3.6351
0.71	F	VS2	GEM	3.6351
0.72	F	VS2	GEM	3.6841
0.78	H	VS1	GEM	3.7702
0.8	I	VVS2	GEM	3.8087
0.7	F	VS1	GEM	3.857
0.71	F	VS1	GEM	3.9102
0.72	H	VVS1	GEM	3.9634
0.84	H	VS2	GEM	3.9935
0.73	E	VS2	GEM	4.0166
0.7	E	VS1	GEM	4.06
0.74	G	VVS2	GEM	4.0705
0.62	E	VVS1	GEM	4.0915
0.71	F	VVS2	GEM	4.1167
0.71	E	VS1	GEM	4.1167
0.72	E	VS1	GEM	4.1727
0.7	F	VVS1	GEM	4.3995
0.71	D	VS1	GEM	4.4604
0.63	E	IF	GEM	4.5584
0.9	I	VVS2	GEM	4.6774
0.89	H	VS1	GEM	4.6963
0.85	F	VS2	GEM	4.7635
0.86	H	VVS2	GEM	4.8174
0.77	F	VVS1	GEM	4.8433
0.83	E	VS2	GEM	5.0092
0.75	D	VVS2	GEM	5.1576
0.9	F	VS1	GEM	5.376
1	I	VS2	GEM	5.4726
1.01	I	VS2	GEM	5.5265
1	I	VS1	GEM	5.6665
1.01	I	VVS2	GEM	5.9185
1.05	I	VVS2	GEM	6.1467
1	H	VS2	GEM	6.1516
1.01	I	VVS1	GEM	6.2111
1.01	H	VS2	GEM	6.2111
1.07	I	VVS2	GEM	6.2615
1.01	H	VS1	GEM	6.4071
1	G	VS2	GEM	6.4183
1	G	VS2	GEM	6.4421
1.06	H	VS2	GEM	6.5114
1	F	VS2	GEM	6.636
1.04	I	IF	GEM	6.6941
1.01	F	VS2	GEM	6.7011
1	G	VS1	GEM	6.7333
1.1	H	VS2	GEM	6.7522
1.02	F	VS2	GEM	6.7662
1.06	G	VS2	GEM	6.8201
1	E	VS2	GEM	6.8299
1.01	E	VS2	GEM	6.8971
0.76	D	IF	GEM	6.9195
1	G	VVS2	GEM	6.9272
1.02	G	VVS2	GEM	7.063
1.01	F	VS1	GEM	7.1904
1.02	F	VS1	GEM	7.2604
1.04	F	VS1	GEM	7.3997
1	F	VVS2	GEM	7.4116
1	E	VS1	GEM	7.4116
1.01	E	VS1	GEM	7.4844
1	F	VS1	GEM	7.4991
1.03	E	VS1	GEM	7.63
1	D	VS1	GEM	7.9933
1	F	IF	GEM	9.7391
1	D	VVS1	GEM	10.9074
1.01	D	VVS1	GEM	11.2056
0.2	G	VS2	INT	0.4466
0.18	G	VVS2	INT	0.4935
0.2	G	VS1	INT	0.4935
0.18	H	IF	INT	0.5075
0.19	H	IF	INT	0.5306
0.18	F	VVS2	INT	0.5355
0.19	G	VVS1	INT	0.56
0.19	F	VVS2	INT	0.56
0.18	G	IF	INT	0.5621
0.18	G	IF	INT	0.5621
0.18	F	VVS1	INT	0.5761
0.19	G	IF	INT	0.5894
0.19	F	VVS1	INT	0.6041
0.2	G	IF	INT	0.616
0.2	D	VS1	INT	0.616
0.21	G	IF	INT	0.6433
0.21	D	VS1	INT	0.6433
0.19	F	IF	INT	0.6769
0.19	D	VVS2	INT	0.6769
0.23	G	IF	INT	0.6965
0.19	E	IF	INT	0.735
0.21	F	IF	INT	0.7399
0.25	I	IF	INT	0.7574
0.24	H	IF	INT	0.7756
0.26	I	IF	INT	0.7847
0.23	F	IF	INT	0.8029
0.21	E	IF	INT	0.8043
0.25	H	IF	INT	0.8043
0.22	E	IF	INT	0.8386
0.28	I	IF	INT	0.8393
0.3	H	VVS2	INT	0.8526
0.27	H	IF	INT	0.8631
0.29	I	IF	INT	0.8666
0.23	E	IF	INT	0.8736
0.26	F	VVS2	INT	0.882
0.25	G	IF	INT	0.8981
0.3	I	IF	INT	0.9093
0.31	I	IF	INT	0.9359
0.26	F	VVS1	INT	0.9555
0.3	G	VVS1	INT	1.0213
0.3	F	VVS2	INT	1.0213
0.32	H	IF	INT	1.0234
0.29	G	IF	INT	1.0297
0.25	F	IF	INT	1.0395
0.33	H	IF	INT	1.0521
0.41	I	VVS2	INT	1.0542
0.26	F	IF	INT	1.0773
0.35	G	VVS2	INT	1.078
0.3	E	VVS2	INT	1.106
0.27	F	IF	INT	1.1165
0.41	I	VVS1	INT	1.1312
0.31	F	VVS1	INT	1.1396
0.31	E	VVS2	INT	1.1396
0.34	F	VVS2	INT	1.1452
0.34	F	VVS1	INT	1.2411
0.35	F	VVS1	INT	1.2747
0.4	G	IF	INT	1.5932
0.48	F	VS1	INT	1.6681
0.47	F	VVS2	INT	1.8557
0.52	I	IF	INT	2.1665
0.56	G	VVS2	INT	2.429
0.5	G	IF	INT	2.5564
0.55	F	VVS2	INT	2.5942
0.51	F	VVS1	INT	2.6054
0.51	E	VVS2	INT	2.6054
0.58	G	VVS1	INT	2.6747
0.56	E	VVS2	INT	2.849
0.58	F	VVS1	INT	2.9463
0.58	E	VVS1	INT	3.3817
0.7	G	VVS2	INT	3.7282
0.7	G	VVS1	INT	3.9249
0.78	G	VVS2	INT	4.1559
0.76	F	VVS2	INT	4.2665
0.71	D	VS1	INT	4.312
1.01	I	VS1	INT	5.7225
1.01	H	VS2	INT	6.2111
1	H	VVS2	INT	6.5394
1.01	G	VS1	INT	6.7991
Carat	Colour	Clarity	Lab	Price (US$1000)

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

1) Construct a pivot table with Clarity as the row variable and Colour as the column variable. This was my response below (I hope that this is correct):

Count of Colour	Column Labels
Row Labels	D	E	F	G	H	I	Grand Total
IF	1	5	8	11	11	8	44
VS1	7	18	25	12	11	8	81
VS2	2	7	14	9	14	7	53
VVS1	2	5	18	12	10	5	52
VVS2	4	9	17	21	15	12	78
Grand Total	16	44	82	65	61	40	308

<colgroup><col><col span="6"><col></colgroup><tbody>
</tbody>

2) Use your pivot table to construct a contingency table showing percentages in each clarity category by colour. (This means that each column of your table should add up to 100%.) Pay attention to rounding in your display..

 

[sheetspread]

In the PivotTable Tools Options ribbon under Show Values as, click % of column total

 

[AhoyNC]

You may find this link to be of help.
https://www.youtube.com/watch?v=-ERARVSfeuw

He also does a statistics course using Excel that maybe of help. YouTube channel is ExcelisFun.
https://www.youtube.com/playlist?list=PLFAD0C5745D4A1F60