Question

 

Question	Answer
1. On the Data sheet, there should only be a maxiumum trans_cnt (transaction count) of 20 per order. Use conditional formating to visually identify if any orders exceed that amount.	Show conditional formatting
2. How else could you have quickly answered #1?
3. Use formulas on the Data sheet to run basic descriptive stats on emissions (min, max, mean, median & mode, etc.). (HINT: use the Excel readings to look up formulas if you're unfamiliar)	Show answers on Data below emissions column
4. Call out the top and bottom 10 percent of emissions	Show conditional formatting
5. What is the range of highest 10% of emissions?
6. What is the range of lowest 10% of emissions?
7. How else would you group these emissions to better understand them? (A short written answer is fine. You don't have to show any work for this one.)
8. What is the longest distance any single item travelled? (You must use a formula to get credit!)

DATA SHEET

Order_ID	trans_cnt	cat_cnt	category	cat1_wgt	weight	total_km	g CO2e emissions	copy of total_km	copy of g CO2e
00175250501	1	1	Art	54.14	54.14	4,148	3,545	4,148	3,545
00175250628	1	1	Accessories	16.45	16.45	57	7	57	7
00175250657	1	1	Accessories	16.45	16.45	2,443	922	2,443	922
00175251388	1	1	Supplies	25.12	25.12	3,803	2,872	3,803	2,872
00175251961	2	1	Jewelry	4.1	8.2	4,068	527	4,068	527
00175252080	1	1	Supplies	25.12	25.12	751	113	751	113
00175252144	1	1	Jewelry	4.1	4.1	21	1	21	1
00175252312	1	1	Vintage	31.37	31.37	18	4	18	4
00175252475	4	1	Glass	55.62	222.48	483	699	483	699
00175253357	1	1	Jewelry	4.1	4.1	4,849	576	4,849	576
00175253546	1	1	Clothing	12.17	12.17	596	43	596	43
00175254052	1	1	Supplies	25.12	25.12	291	52	291	52
00175254624	9	1	Supplies	25.12	226.08	4,075	14,462	4,075	14,462
00175255997	1	1	Bags_And_Purses	24.65	24.65	1,649	1,261	1,649	1,261
00175256031	1	1	Jewelry	4.1	4.1	126	4	126	4
00175256181	1	1	Art	54.14	54.14	12	5	12	5
00175256315	1	1	Art	54.14	54.14	334	111	334	111
00175256391	1	1	Jewelry	4.1	4.1	425	11	425	11
00175256612	1	1	Crochet	11.48	11.48	1,588	549	1,588	549
00175256613	1	1	Glass	55.62	55.62	775	264	775	264
00175256627	1	1	Art	54.14	54.14	3,675	6,199	3,675	6,199
00175256676	3	1	Jewelry	4.1	12.3	483	36	483	36
00175256835	1	1	Vintage	31.37	31.37	453	85	453	85
00175256861	1	1	Children	10.63	10.63	4,245	691	4,245	691
00175257109	1	1	Jewelry	4.1	4.1	14	0	14	0
00175257200	4	1	Paper_Goods	14.86	59.44	6	3	6	3
00175258396	5	1	Supplies	25.12	125.6	733	548	733	548
00175258519	1	1	Housewares	30.62	30.62	349	64	349	64
00175259229	1	1	Jewelry	4.1	4.1	219	6	219	6
00175259345	2	2	Clothing	12.17	21.14	2,397	1,148	2,397	1,148
00175259352	1	1	Supplies	25.12	25.12	3,713	2,863	3,713	2,863
00175259453	2	1	Jewelry	4.1	8.2	3,644	931	3,644	931
00175259772	2	1	Vintage	31.37	62.74	2,735	5,331	2,735	5,331
00175260244	11	1	Supplies	25.12	276.32	4,252	30,826	4,252	30,826
00175260362	1	1	Art	54.14	54.14	39	15	39	15
00175261489	2	1	Bath_And_Beauty	32.79	65.58	2,430	4,299	2,430	4,299
00175261595	1	1	Accessories	16.45	16.45	122	14	122	14
00175262126	1	1	Art	54.14	54.14	2,890	4,851	2,890	4,851
00175262206	1	1	Housewares	30.62	30.62	398	78	398	78
00175262480	20	1	Supplies	25.12	502.4	2,955	45,987	2,955	45,987
00175262564	1	1	Supplies	25.12	25.12	1,232	184	1,232	184
00175262778	1	1	Vintage	31.37	31.37	476	94	476	94
00175263360	4	1	Clothing	12.17	48.68	602	173	602	173
00175263614	1	1	Weddings	22.92	22.92	4,023	2,332	4,023	2,332
00175263691	1	1	Vintage	31.37	31.37	5,151	2,456	5,151	2,456
00175263754	1	1	Geekery	16.44	16.44	1,009	98	1,009	98
00175264025	6	1	Vintage	31.37	188.22	3,905	11,618	3,905	11,618
00175264184	5	1	Jewelry	4.1	20.5	3,698	2,351	3,698	2,351
00175264360	1	1	Children	10.63	10.63	560	37	560	37
00175264605	1	1	Accessories	16.45	16.45	3,585	1,832	3,585	1,832
00175264674	1	1	Everything_Else	17.56	17.56	1,250	130	1,250	130
00175265125	1	1	Dolls_And_Miniatures	3.84	3.84	5,484	332	5,484	332
00175265274	2	2	Art	54.14	70.58	813	339	813	339
00175266557	1	1	Knitting	8.29	8.29	864	42	864	42
00175266937	2	1	Supplies	25.12	50.24	3,602	5,593	3,602	5,593
00175267166	1	1	Accessories	16.45	16.45	1,101	109	1,101	109
00175267214	1	1	Supplies	25.12	25.12	3,661	2,854	3,661	2,854
00175267285	1	1	Jewelry	4.1	4.1	3,725	471	3,725	471
00175267347	1	1	Supplies	25.12	25.12	3,787	2,869	3,787	2,869
00175267769	1	1	Clothing	12.17	12.17	32	3	32	3
00175269363	1	1	Vintage	31.37	31.37	24	5	24	5
00175269371	1	1	Housewares	30.62	30.62	622	113	622	113
00175270293	1	1	Clothing	12.17	12.17	233	17	233	17
00175270469	1	1	Paper_Goods	14.86	14.86	35	4	35	4
00175271275	1	1	Supplies	25.12	25.12	269	48	269	48
00175271303	1	1	Supplies	25.12	25.12	4,210	1,656	4,210	1,656
00175272078	4	1	Jewelry	4.1	16.4	1,241	121	1,241	121
00175272488	1	1	Jewelry	4.1	4.1	132	4	132	4
00175272506	2	1	Supplies	25.12	50.24	2,869	2,945	2,869	2,945
00175272778	1	1	Vintage	31.37	31.37	3,144	2,905	3,144	2,905
00175272848	1	1	Clothing	12.17	12.17	4,135	763	4,135	763
00175273343	12	1	Supplies	25.12	301.44	17	38	17	38
00175274099	1	1	Art	54.14	54.14	4,090	3,394	4,090	3,394
00175274376	2	1	Glass	55.62	111.24	4,349	7,373	4,349	7,373
00175276799	1	1	Bath_And_Beauty	32.79	32.79	638	125	638	125
00175277020	4	1	Paper_Goods	14.86	59.44	354	126	354	126
00175277034	1	1	Paper_Goods	14.86	14.86	3,676	1,697	3,676	1,697
00175277038	5	1	Children	10.63	53.15	59	22	59	22
00175278093	2	1	Supplies	25.12	50.24	1,525	2,261	1,525	2,261
00175278199	1	1	Bags_And_Purses	24.65	24.65	4,807	1,878	4,807	1,878
00175278292	1	1	Jewelry	4.1	4.1	240	6	240	6
00175278448	1	1	Clothing	12.17	12.17	115	10	115	10
00175278601	3	1	Supplies	25.12	75.36	8	4	8	4
00175278669	1	1	Jewelry	4.1	4.1	4,838	313	4,838	313
00175278778	1	1	Housewares	30.62	30.62	703	128	703	128
00175278915	1	1	Bath_And_Beauty	32.79	32.79	349	74	349	74
00175279341	1	1	Housewares	30.62	30.62	363	80	363	80
00175279552	1	1	Children	10.63	10.63	842	53	842	53
00175279790	2	1	Supplies	25.12	50.24	1,148	341	1,148	341
00175280281	2	1	Jewelry	4.1	8.2	85	5	85	5
00175280606	1	1	Accessories	16.45	16.45	3,138	1,523	3,138	1,523
00175281058	5	2	Clothing	12.17	60.85	84	36	84	36
00175281509	31	1	Supplies	25.12	778.72	672	3,155	672	3,155
00175281592	5	1	Supplies	25.12	125.6	55	50	55	50
00175282014	14	1	Supplies	25.12	351.68	3,869	40,758	3,869	40,758
00175282109	1	1	Art	54.14	54.14	100	39	100	39
00175282544	6	2	Supplies	25.12	169.47	435	439	435	439
00175282593	2	1	Supplies	25.12	50.24	543	167	543	167
00175282731	1	1	Supplies	25.12	25.12	321	51	321	51
00175283135	1	1	Pets	8.97	8.97	3,828	541	3,828	541
00175283577	1	1	Jewelry	4.1	4.1	3,755	447	3,755	447
00175283851	1	1	Vintage	31.37	31.37	4,021	3,633	4,021	3,633
00175283928	1	1	Children	10.63	10.63	877	56	877	56
00175284148	2	1	Art	54.14	108.28	1,256	813	1,256	813
00175284227	1	1	Geekery	16.44	16.44	239	24	239	24
00175284389	2	2	Knitting	8.29	24.74	1,169	174	1,169	174
00175284391	1	1	Accessories	16.45	16.45	3,707	1,770	3,707	1,770
00175284440	1	1	Jewelry	4.1	4.1	4,811	313	4,811	313
00175284776	1	1	Supplies	25.12	25.12	2,417	1,888	2,417	1,888
00175284841	1	1	Jewelry	4.1	4.1	11	0	11	0
00175284852	1	1	Paper_Goods	14.86	14.86	1,116	99	1,116	99
00175284870	1	1	Supplies	25.12	25.12	823	122	823	122
00175284998	1	1	Children	10.63	10.63	1,439	91	1,439	91
00175285191	1	1	Clothing	12.17	12.17	3,903	1,411	3,903	1,411
00175285544	2	1	Accessories	16.45	32.9	4,850	2,531	4,850	2,531
00175285830	2	1	Supplies	25.12	50.24	849	253	849	253
00175286053	1	1	Accessories	16.45	16.45	591	57	591	57
00175286283	1	1	Supplies	25.12	25.12	2,230	1,731	2,230	1,731
00175286708	14	1	Supplies	25.12	351.68	368	802	368	802
00175286837	2	1	Pets	8.97	17.94	1,905	792	1,905	792
00175287882	1	1	Children	10.63	10.63	3,391	981	3,391	981
00175288175	1	1	Vintage	31.37	31.37	864	162	864	162
00175288332	10	1	Supplies	25.12	251.2	538	824	538	824
00175289086	1	1	Jewelry	4.1	4.1	2,214	214	2,214	214
00175289472	1	1	Everything_Else	17.56	17.56	626	65	626	65
00175289580	1	1	Jewelry	4.1	4.1	160	5	160	5
00175289758	1	1	Supplies	25.12	25.12	2,933	2,295	2,933	2,295
00175289898	1	1	Housewares	30.62	30.62	427	79	427	79
00175290274	1	1	Supplies	25.12	25.12	305	47	305	47
00175290463	1	1	Plants_And_Edibles	24.4	24.4	298	46	298	46
00175290779	1	1	Art	54.14	54.14	493	163	493	163
00175291106	2	1	Knitting	8.29	16.58	2,731	1,118	2,731	1,118
00175291121	2	2	Supplies	25.12	29.22	15	3	15	3
00175291246	1	1	Supplies	25.12	25.12	74	13	74	13
00175291883	1	1	Paper_Goods	14.86	14.86	1,344	119	1,344	119
00175291890	1	1	Paper_Goods	14.86	14.86	3,469	1,497	3,469	1,497
00175293044	1	1	Jewelry	4.1	4.1	299	7	299	7
00175293113	1	1	Housewares	30.62	30.62	87	19	87	19
00175293498	1	1	Bath_And_Beauty	32.79	32.79	2,960	3,007	2,960	3,007
00175293739	1	1	Children	10.63	10.63	428	28	428	28
00175293992	11	1	Supplies	25.12	276.32	358	611	358	611
00175294333	1	1	Vintage	31.37	31.37	3,009	2,888	3,009	2,888
00175294616	1	1	Accessories	16.45	16.45	4,275	1,856	4,275	1,856
00175294621	1	1	Toys	20.57	20.57	584	74	584	74
00175294870	1	1	Art	54.14	54.14	146	57	146	57
00175295089	1	1	Jewelry	4.1	4.1	95	3	95	3
00175295321	1	1	Woodworking	42.97	42.97	1,047	271	1,047	271
00175295801	1	1	Children	10.63	10.63	1,770	553	1,770	553
00175295823	2	1	Supplies	25.12	50.24	447	134	447	134
00175296412	2	2	Housewares	30.62	40.58	220	64	220	64
00175296430	1	1	Vintage	31.37	31.37	3,805	3,610	3,805	3,610
00175296926	2	1	Jewelry	4.1	8.2	745	36	745	36
00175298269	1	1	Supplies	25.12	25.12	1,657	1,286	1,657	1,286
00175298455	1	1	Children	10.63	10.63	3,160	1,037	3,160	1,037
00175298864	2	1	Supplies	25.12	50.24	2,953	4,598	2,953	4,598
00175298892	1	1	Bags_And_Purses	24.65	24.65	3,626	2,770	3,626	2,770
00175299107	1	1	Art	54.14	54.14	3,196	3,741	3,196	3,741
00175299121	1	1	Jewelry	4.1	4.1	32	1	32	1
00175299138	1	1	Vintage	31.37	31.37	4,050	2,013	4,050	2,013
00175300376	2	1	Jewelry	4.1	8.2	4,020	521	4,020	521
00175300818	1	1	Clothing	12.17	12.17	3,612	1,103	3,612	1,103
00175300821	1	1	Children	10.63	10.63	3,660	1,204	3,660	1,204
00175301010	15	1	Supplies	25.12	376.8	5,011	29,264	5,011	29,264
00175301204	1	1	Children	10.63	10.63	839	53	839	53
00175301299	5	1	Plants_And_Edibles	24.4	122	95	83	95	83
00175301309	1	1	Supplies	25.12	25.12	3,670	2,856	3,670	2,856
00175301678	3	1	Supplies	25.12	75.36	2,586	4,664	2,586	4,664
00175301735	5	1	Supplies	25.12	125.6	4,096	7,845	4,096	7,845
00175302080	1	1	Jewelry	4.1	4.1	2,963	376	2,963	376
00175302298	1	1	Children	10.63	10.63	3,040	979	3,040	979
00175302330	6	1	Supplies	25.12	150.72	439	410	439	410
00175302657	1	1	Everything_Else	17.56	17.56	30	4	30	4
00175302903	1	1	Jewelry	4.1	4.1	3,394	272	3,394	272
00175303058	1	1	Children	10.63	10.63	2,100	135	2,100	135
00175303383	1	1	Paper_Goods	14.86	14.86	183	20	183	20
00175303616	1	1	Everything_Else	17.56	17.56	271	34	271	34
00175303968	1	1	Supplies	25.12	25.12	1,111	166	1,111	166
00175304410	1	1	Supplies	25.12	25.12	86	15	86	15
00175305202	22	1	Supplies	25.12	552.64	53	210	53	210
00175305534	1	1	Jewelry	4.1	4.1	258	7	258	7
00175305542	8	1	Supplies	25.12	200.96	828	999	828	999
00175305610	1	1	Supplies	25.12	25.12	4,809	1,914	4,809	1,914
00175305701	7	1	Supplies	25.12	175.84	1,257	1,320	1,257	1,320
00175306928	1	1	Jewelry	4.1	4.1	2,859	364	2,859	364
00175307167	2	1	Art	54.14	108.28	3,301	10,157	3,301	10,157
00175307216	2	1	Jewelry	4.1	8.2	3,650	931	3,650	931
00175307475	1	1	Jewelry	4.1	4.1	1,414	176	1,414	176
00175307551	1	1	Everything_Else	17.56	17.56	868	90	868	90
00175307596	1	1	Supplies	25.12	25.12	1,642	1,114	1,642	1,114
00175307602	1	1	Supplies	25.12	25.12	2,224	1,586	2,224	1,586
00175307711	1	1	Vintage	31.37	31.37	2,923	2,154	2,923	2,154
00175308000	2	1	Jewelry	4.1	8.2	4,321	540	4,321	540
00175308721	1	1	Bags_And_Purses	24.65	24.65	115	20	115	20
00175309248	1	1	Crochet	11.48	11.48	451	32	451	32
00175309495	2	1	Vintage	31.37	62.74	4,012	3,985	4,012	3,985
00175309791	1	1	Art	54.14	54.14	626	202	626	202
00175309842	1	1	Accessories	16.45	16.45	4,043	1,055	4,043	1,055
00175309843	1	1	Jewelry	4.1	4.1	142	4	142	4
00175309892	2	2	Knitting	8.29	33.41	4,015	2,123	4,015	2,123
00175310092	1	1	Jewelry	4.1	4.1	222	5	222	5

Answer 1

1. The order Id’s (175281509) and (175305202) has the 31 and 22 transaction count, respectively. Which is higher than 20.
2. The answer to #1 took two seconds. The only thing you have to do is to sort the column from Larger to smaller.
3. The “=AVERAGE(A:A)”, “MEDIAN(A:A)” “=MODE.MULT(A:A)”, "=MAX(A:A)”, “=MIN(A:A)" are the formulae to calculate the mean, median, mode, Maximum, minimum values respectively. We have found the following statistics for the omission:

Mean = 1847, Median = 268, Mode = 4, Min = 0, Max= 45987

4. The simplest way would be to sort the variable emission from lowest to highest. Then, the 10% of the total values 200 would be 20. Thus, we will have the top 20 and bottom 20 values as the top and bottom ten percent of emission.

Bottom 10%	Top 10%
0	3,610
0	3,633
1	3,741
1	3,985
3	4,299
3	4,598
3	4,664
3	4,851
4	5,331
4	5,593
4	6,199
4	7,373
4	7,845
4	10,157
4	11,618
5	14,462
5	29,264
5	30,826
5	40,758
5	45,987

5. The range of bottom 10% of emissions is (0 to 5)

6. The range of highest 10% of emission is (3610 to 45987).

8. 175265125 travels the highest distance of 5484. The formula used was "=MAX(A:A)”