excel

profileBYSTANDER
MGMT650HW2Summer22_Questions.xlsx
Home>Science homework help>excel

Week 2 HW

MGMT 650 Summer 2022 Week 2 Homework Questions (Last updated 5/15/2022)

1 - 8 - Descriptive Statistics

You are a statistician contracted to sample and analyze weights of the 5 lb bags of coffee. You collect the following data:
5.30 6.14 4.47 5.38 6.00 5.56 5.34 4.75 4.69 5.13
4.67 4.28 5.08 5.53 5.32 4.85 4.25 5.28 4.32 4.49
4.93 4.84 4.51 4.14 5.59 4.46 4.77 4.99 4.82 4.86
5.51 5.61 4.60 4.54 4.26 5.74 5.40 4.96 5.49 5.03
5.99 5.08 5.15 4.57 5.18 4.90 5.03 4.98 4.18 4.53
5.34 5.15 5.45 5.82 5.30 5.13 5.73 5.04 5.16 4.29
5.04 5.28 4.39 3.83 5.40 5.60 5.08 5.19 4.34 5.29
4.42 5.78 4.86 4.90 4.67 4.77 6.18 5.52 4.67 5.92
For all questions, write your answers in the yellow cells.
Compute the following statistics using Excel functions so that Excel calculates for you:
1) Mean
Median
2) First quartile using QUARTILE.EXC
Third quartile using QUARTILE.EXC
Interquartile range (IQR)
3) Maximum
Minimum
Range
4) Variance
Standard deviation
Coeficient of Variation (CV)
5) For the standard deviation, when do you use STDEV.S and when do you use STDEV.P?
6) Explain why the standard deviation is a better measure to use than the variance.
The coefficient of variation (CV) is a measure of relative variability equal to the ratio between the standard deviation divided by the mean, and formatted to %. It is regularly used to compare risk (volatility) in investing, and is especially useful in to compare data on different scales or with different units of measure. Consider weights of 10 oz, 13.4 oz, 15.1 oz with SD of 2.597 oz. The corresponding weights in pounds 0.625 lb, 0.838 lb, 0.944 lb have SD 0.162 lb. The SDs are not equal, yet the sample and its variability are the same. How can we compare variabilities? The CV, unlike other measures of variability, does not depend on the units of measure. The units are divided out in dividing the standard deviation by the mean. For a "rule of thumb", a CV of greater than 5% is considered significant. So, the CV is also used to assess data with no prior history to compare to evaluate any trends.
When is the Coefficient of Variation (CV) especially useful?
7) As a contracted statistician, you have no prior experience with the company's product.
The company has no prior process history and data with which to compare the sample to evaluate any trends.
The mode is not especially useful for this data, but explain why there is or is not
a concern with bag weight in terms of the other measures of central tendency.
8) What statistic should you use to assess variability of the product?
Explain why there is or is not a concern with the variability of the product.

&D

9-12 Pivot Table

See the tab "Used Car Data". This list represents a consolidated list from 2021 of used cars for sale on Craigs List in the Athens, GA area. The data set contains 421 used cars from the year 1995 or newer that sold for $1000 or more. The list also includes columns like price, condition, manufacturer and other relevant categories a buyer might find of interest. Note: For the tables in question 9 and 11, you can either create them in a new tab, or place it at the bottom of this tab See the tab "Sample Data for Pivot Table" for an example of creating one-way and two-way tables
9. Create a pivot table showing the frequency and percent frequency of the manufacturer from the Used Car Data.
10. What is the most common used car manufacturer? What is the least common used car manufacturer?
11. Create a two way pivot table showing Year vs. Condition using the Used Car Data.
12. In one paragraph, describe any relationship the data shows between year and condition, and explain why.

Used Car Data

price year manufacturer model condition cylinders fuel odometer title_status transmission drive size type paint_color
32590 2015 mercedes-benz gla-class gla 45 good gas 34811 clean other other black
7500 2006 mazda miata excellent 4 cylinders gas 100700 clean manual rwd compact convertible blue
32990 2019 volkswagen atlas se 4motion good 6 cylinders gas 3896 clean other other silver
16990 2016 kia forte koup ex coupe 2d good gas 17631 clean other fwd coupe
12995 2011 ford explorer excellent gas 166267 clean automatic fwd SUV purple
10995 2011 rover lr2 excellent gas 114575 clean automatic 4wd SUV grey
3995 1997 chevrolet lumina excellent gas 0 clean automatic fwd sedan
28990 2017 bmw 4 series 440i xdrive gran good gas 49355 clean automatic coupe
4900 2016 ford focus se good 4 cylinders gas 125100 clean automatic fwd
7995 2007 infiniti m45 excellent gas 214740 clean automatic rwd sedan black
13995 2013 bmw 3 series excellent gas 135848 clean automatic rwd sedan white
8995 2008 mercedes-benz e-class excellent gas 112214 clean automatic rwd sedan silver
20649 2012 chevrolet silverado 1500 excellent gas 118600 clean automatic 4wd full-size pickup silver
25990 2013 toyota tundra crewmax pickup 4d good 8 cylinders gas 98879 clean other pickup white
31990 2017 jeep wrangler sport s utility 2d good 6 cylinders gas 891 clean other 4wd other white
31990 2017 bmw 3 series 340i sedan 4d good gas 19366 clean automatic rwd sedan red
25990 2014 chevrolet silverado 1500 regular good 8 cylinders other 82140 clean automatic 4wd pickup black
2200 1997 ford ranger super cab fair 6 cylinders gas 165000 salvage manual rwd mid-size truck red
24990 2016 mazda mx-5 miata grand touring good gas 6218 clean other rwd other
6600 2016 nissan versa like new 4 cylinders gas 110500 clean automatic fwd sedan blue
34590 2015 ford mustang gt premium good 8 cylinders gas 30970 clean other rwd other white
25990 2014 gmc sierra 1500 regular cab good 6 cylinders gas 15575 clean other pickup brown
15590 2016 chevrolet cruze limited 1lt good gas 7076 clean automatic fwd sedan blue
5000 1997 gmc savana 3500 fair 8 cylinders gas 177000 clean automatic rwd van white
32590 2016 subaru wrx sti sedan 4d good other 21195 clean other sedan white
33990 2018 ford f150 super cab xl pickup 4d good 6 cylinders other 13670 clean other pickup black
29990 2016 chevrolet colorado extended cab good 6 cylinders gas 17302 clean other 4wd pickup red
34590 2017 toyota tacoma access cab trd good 6 cylinders gas 20097 clean other 4wd pickup
2600 2000 gmc jimmy good 6 cylinders gas 138000 clean automatic 4wd mid-size silver
21000 2016 subaru outback 3.6r limited excellent 6 cylinders gas 87400 clean automatic 4wd full-size SUV green
5000 2001 lexus ls430 good 8 cylinders gas 267000 clean automatic rwd full-size sedan silver
35590 2010 chevrolet corvette convertible good 8 cylinders gas 31879 clean other rwd other red
32990 2013 tesla model s signature good electric 62588 clean other rwd sedan white
3000 1995 ford f150 xl good 6 cylinders gas 211000 clean manual rwd full-size truck white
32990 2017 ram 1500 crew cab big horn good 6 cylinders other 10193 clean other 4wd pickup
29990 2019 chevrolet camaro lt coupe 2d good 6 cylinders gas 6727 clean other rwd coupe red
4500 2011 ford focus se excellent 6 cylinders gas 130000 clean automatic rwd mid-size sedan silver
21500 2012 gmc sierra 1500 excellent 8 cylinders gas 116500 clean automatic 4wd truck white
8999 2011 ford ranger xlt excellent 4 cylinders gas 120000 clean manual rwd mid-size truck red
30990 2019 ford ranger supercrew xl pickup good other 1834 clean other pickup black
36990 2015 chevrolet tahoe lt sport utility good 8 cylinders other 51677 clean other other black
27990 2020 ford ranger supercab xl pickup good gas 10688 clean other pickup white
16590 2015 volkswagen beetle 1.8t fleet good gas 39649 clean other fwd hatchback black
12800 1995 toyota pickup excellent 6 cylinders gas 212000 clean manual 4wd compact pickup white
6995 2001 chevrolet silverado 1500 good 6 cylinders gas 200920 clean automatic rwd full-size pickup white
9495 2000 chevrolet silverado 1500 ls good 8 cylinders gas 167393 clean automatic rwd full-size pickup white
8995 2010 mazda cx-9 excellent gas 183761 clean automatic fwd SUV white
11500 2009 bmw 3 series excellent gas 43280 clean automatic rwd sedan red
5495 2003 infiniti g35 excellent gas 203067 clean automatic rwd sedan silver
4400 2005 ford focus good 4 cylinders gas 140000 rebuilt automatic fwd sub-compact sedan white
38990 2019 tesla model 3 standard range good electric 7609 clean other rwd sedan
35990 2020 dodge challenger r/t coupe 2d good 8 cylinders gas 1548 clean other rwd coupe black
6800 2007 toyota rav4 good 4 cylinders gas 186000 clean automatic fwd compact SUV blue
17990 2015 volkswagen golf tdi sel good diesel 51482 clean other fwd sedan white
39990 2019 nissan 370z nismo coupe 2d good 6 cylinders gas 8373 clean other rwd coupe white
14700 2015 honda cr-v ex-l excellent 4 cylinders gas 70672 salvage automatic 4wd compact SUV brown
42995 2017 ford f150 xlt excellent 6 cylinders gas 41500 clean automatic 4wd full-size truck black
24590 2013 gmc sierra 1500 extended cab slt good 8 cylinders other 80318 clean other pickup
6300 2009 chevrolet express 3500 good 8 cylinders gas 194000 clean automatic
27990 2012 toyota tacoma double cab pickup good 6 cylinders gas 67245 clean other 4wd pickup silver
29590 2015 chevrolet silverado 1500 double good other 43874 clean other pickup
31590 2013 ford f150 supercrew cab fx4 good other 68089 clean other 4wd pickup red
7500 2007 toyota tacoma good 4 cylinders gas 270080 clean manual rwd truck silver
1500 2009 mercury grand marquis fair 8 cylinders gas 160000 clean automatic fwd full-size other
9495 2012 mazda cx-9 excellent gas 183954 clean automatic fwd SUV blue
10995 2011 nissan armada excellent gas 172210 clean automatic SUV blue
6995 2005 toyota sienna excellent gas 180821 clean automatic fwd mini-van blue
5995 2003 toyota corolla excellent gas 188596 clean automatic fwd sedan grey
6000 2007 bmw z4 convertible excellent 6 cylinders gas 183000 clean automatic rwd compact convertible black
39590 2018 toyota 4runner limited sport good 6 cylinders gas 34199 clean other 4wd other red
37990 2016 toyota tundra double cab sr good 8 cylinders gas 22870 clean other 4wd pickup black
4495 1999 ford expedition excellent gas 272412 clean automatic rwd SUV
11495 2016 kia soul excellent gas 114843 clean automatic fwd wagon white
34990 2020 jeep wrangler sport s utility 2d good other 131 clean other 4wd other black
38990 2017 jeep wrangler unlimited rubicon good 6 cylinders gas 20676 clean other 4wd other red
2850 1996 jeep cherokee good 6 cylinders gas 209356 clean automatic 4wd full-size SUV red
6500 2008 ford f-150 good gas 161000 clean automatic 4wd full-size pickup grey
7300 2000 ford ranger xlt good 6 cylinders gas 120000 clean automatic 4wd mid-size truck brown
28590 2017 chevrolet silverado 1500 regular good 6 cylinders gas 102 clean other pickup red
26000 2016 ram 1500 4x4 excellent 8 cylinders gas 67000 clean automatic 4wd full-size truck silver
29990 2019 mazda mx-5 miata grand touring good gas 5247 clean other rwd other
32990 2018 ford mustang gt coupe 2d good 8 cylinders gas 27726 clean other rwd coupe red
17990 2012 volkswagen touareg tdi sport suv good 6 cylinders gas 93185 clean other other white
2000 2002 ford ranger good 4 cylinders gas 188000 clean manual rwd mid-size pickup green
8500 1995 lexus sc400 excellent 8 cylinders gas 98015 clean automatic rwd coupe custom
6500 2004 mercedes-benz benz-e500 5.0l v8 excellent 8 cylinders gas 116000 clean automatic rwd mid-size sedan blue
4200 2002 dodge 1500 good 8 cylinders gas 226000 clean automatic rwd full-size pickup grey
8495 2005 infiniti g35 excellent gas 146561 clean automatic rwd coupe red
10995 2011 mercedes-benz r-class excellent gas 171865 clean automatic 4wd wagon black
7995 2011 kia sorento excellent gas 151544 clean automatic fwd SUV grey
4495 2006 honda odyssey excellent gas 238229 clean automatic fwd mini-van grey
10995 2008 mercedes-benz c-class excellent gas 99883 clean automatic 4wd sedan silver
13995 2010 chevrolet camaro excellent gas 91596 clean manual rwd coupe
22590 2013 ford f150 super cab xl pickup 4d good 6 cylinders other 14169 clean other pickup silver
15990 2010 ford ranger super cab xlt pickup good 6 cylinders gas 48055 clean other pickup silver
29590 2017 ford expedition xlt sport good 6 cylinders gas 70760 clean other 4wd SUV blue
25990 2019 ram 1500 classic regular cab good 8 cylinders gas 12302 clean other pickup white
21500 2012 rover like new 8 cylinders gas 79500 clean automatic 4wd full-size SUV silver
13995 2006 nissan titan excellent gas 172118 clean automatic 4wd pickup grey
8995 2011 bmw 3 series excellent gas 172222 clean automatic rwd sedan grey
11995 2008 ford expedition excellent gas 144597 clean automatic SUV orange
8995 2006 infiniti g35 excellent gas 153107 clean automatic rwd coupe white
8995 2005 lexus ls 430 excellent gas 213104 clean automatic rwd sedan silver
27990 2014 toyota tacoma access cab pickup good other 17805 clean other pickup red
39590 2017 chevrolet camaro ss coupe 2d good 8 cylinders gas 10496 clean other rwd coupe white
32990 2011 bmw z4 sdrive35is roadster 2d good other 45858 clean other rwd other red
38590 2011 chevrolet corvette grand sport good 8 cylinders gas 30237 clean other rwd other red
4900 2007 honda cr-v good 4 cylinders gas 236000 clean automatic
6000 2007 mazda cx-9 excellent 6 cylinders gas 194000 clean automatic mid-size black
25990 2015 buick enclave premium sport good 6 cylinders gas 33154 clean other SUV
11995 2007 chevrolet avalanche excellent gas 195976 clean automatic 4wd pickup black
8495 2008 honda accord excellent gas 177122 clean automatic fwd sedan black
12995 2012 ford explorer excellent gas 148840 clean automatic fwd SUV white
9495 2008 mazda cx-9 excellent gas 161541 clean automatic fwd SUV grey
10995 2008 ford expedition excellent gas 182003 clean automatic SUV silver
9495 2012 mazda cx-9 excellent gas 175137 clean automatic fwd SUV blue
7995 2006 mitsubishi eclipse excellent gas 116091 clean automatic fwd hatchback grey
7995 2007 infiniti fx35 excellent gas 213539 clean automatic 4wd SUV black
10495 2010 cadillac srx excellent gas 184225 clean automatic fwd SUV black
6495 2006 toyota sienna excellent gas 200745 clean automatic fwd mini-van grey
28590 2018 jaguar xe 25t premium sedan 4d good gas 4007 clean other rwd sedan black
15990 2014 acura ilx 2.0l sedan 4d good gas 73001 clean other fwd sedan
30590 2017 jaguar xe 35t r-sport sedan 4d good other 17022 clean other rwd sedan silver
13500 2008 toyota fj cruiser good 6 cylinders gas 173000 clean automatic rwd SUV blue
9495 2010 bmw 3 series excellent gas 121229 clean automatic rwd sedan blue
10995 2011 bmw 3 series excellent gas 105943 clean automatic rwd sedan white
3995 1996 ford explorer excellent gas 185485 clean automatic 4wd SUV red
7495 2009 jeep liberty excellent gas 191610 clean automatic SUV blue
5995 2004 honda odyssey excellent gas 179893 clean automatic fwd mini-van silver
7995 2009 mercedes-benz e-class excellent diesel 168860 clean automatic rwd sedan white
14995 2010 ford f-150 excellent gas 179429 clean automatic pickup red
39990 2019 audi a6 45 tfsi premium sedan 4d good gas 8573 clean other sedan white
16590 2016 hyundai sonata eco sedan 4d good gas 39763 clean other fwd sedan white
1200 2006 cadillac cts sedan fair 6 cylinders gas 150000 clean automatic rwd mid-size sedan black
28590 2018 lexus nx 300 sport utility 4d good gas 30240 clean other fwd hatchback black
37990 2020 cadillac xt4 sport suv 4d good gas 1366 clean other hatchback white
25995 2011 ford f250 diesel excellent 8 cylinders diesel 261000 clean automatic 4wd grey
14995 2011 jeep wrangler unlimited sport excellent 6 cylinders gas 218000 clean automatic 4wd orange
24995 2013 jeep wrangler unlimited sahara excellent 6 cylinders gas 115000 clean automatic 4wd red
13995 2013 ford expedition el limited excellent 8 cylinders gas 154000 clean automatic rwd red
14995 2015 nissan rogue excellent 4 cylinders gas 59000 clean automatic fwd blue
11995 2017 jeep patriot excellent 6 cylinders gas 99000 clean automatic fwd grey
6995 2008 mercedes-benz clk350 excellent 6 cylinders gas 171000 clean automatic blue
12995 2012 ford expedition excellent 8 cylinders gas 126000 clean automatic rwd white
3500 2011 nissan altima good gas 228000 clean automatic white
33990 2019 acura rdx sh-awd sport utility good gas 11902 clean other SUV red
12995 2010 mercedes-benz e-class excellent gas 103275 clean automatic rwd sedan grey
7995 2007 infiniti m45 excellent gas 214740 clean automatic rwd sedan black
13995 2013 bmw 3 series excellent gas 135848 clean automatic rwd sedan white
3995 2001 lincoln ls excellent gas 204407 clean automatic rwd sedan black
8995 2008 mercedes-benz e-class excellent gas 112214 clean automatic rwd sedan silver
25000 2016 mercedes-benz gle 350 like new 6 cylinders gas 85065 clean automatic rwd full-size SUV silver
21990 2020 mitsubishi outlander se sport good other 12069 clean other other
29590 2019 alfa-romeo romeo stelvio ti sport good other 60984 clean other hatchback black
24990 2015 acura mdx sh-awd sport utility good other 61261 clean other SUV
13499 2011 lexus rx350 good 6 cylinders gas 121000 clean automatic rwd mid-size SUV black
12499 2007 lexus ls 460 good 8 cylinders gas 112000 clean automatic rwd full-size sedan white
3200 2000 honda odyssey excellent 4 cylinders gas 136000 clean automatic mid-size van green
5995 2004 ford explorer excellent gas 180767 clean automatic rwd SUV green
5495 2003 infiniti g35 excellent gas 203067 clean automatic rwd sedan silver
25990 2018 lincoln mkz premiere hybrid good hybrid 17500 clean other fwd sedan silver
30990 2019 lincoln mkz hybrid reserve ii good gas 7256 clean other fwd sedan
14500 2015 honda crosstour exl excellent gas 60201 rebuilt automatic hatchback silver
22950 2019 honda passport like new 4 cylinders gas 7012 rebuilt automatic SUV blue
25590 2017 ford mustang v6 convertible 2d good 6 cylinders gas 11325 clean other rwd convertible red
16590 2013 honda civic si coupe 2d good other 81552 clean other fwd coupe silver
31990 2020 mazda mx-5 miata grand touring good gas 2408 clean other rwd other white
35590 2011 dodge challenger srt8 coupe 2d good 8 cylinders gas 16796 clean other rwd coupe green
1500 2006 honda odyssey good 6 cylinders gas 187000 clean automatic fwd mid-size mini-van silver
18500 2015 toyota tacoma excellent 4 cylinders gas 108000 clean automatic rwd mid-size truck white
26590 2017 bmw 3 series 330i xdrive sedan good other 14358 clean other sedan black
27990 2016 bmw 3 series 340i sedan 4d good gas 46057 clean other rwd sedan blue
26990 2018 bmw 3 series 330i xdrive sedan good gas 13088 clean automatic sedan
47990 2020 bmw 3 series m340i sedan 4d good other 9064 clean other rwd sedan black
4000 2002 volkswagen jetta tdi good diesel 185500 clean automatic
1000 2003 chrysler town & country excellent 6 cylinders gas 170000 clean automatic rwd full-size mini-van grey
89000 2021 chevrolet corvette new 8 cylinders gas 175 clean automatic rwd coupe
40590 2019 ford f250 super duty regular cab good 8 cylinders other 9313 clean other 4wd pickup white
46990 2014 chevrolet corvette stingray good 8 cylinders gas 16966 clean other rwd other white
33990 2016 ford f250 super duty cab xl good 8 cylinders gas 18380 clean other pickup white
25990 2017 ford expedition xlt sport good other 74795 clean other 4wd SUV red
10995 2011 nissan armada excellent gas 172210 clean automatic SUV blue
6495 2006 toyota sienna excellent gas 213998 clean automatic fwd mini-van
4495 2002 mazda protege excellent gas 189035 clean automatic fwd sedan
11995 2007 chevrolet silverado 1500 excellent gas 165315 clean automatic rwd pickup blue
5495 2004 nissan xterra excellent gas 205269 clean automatic 4wd SUV grey
8495 2008 gmc yukon xl excellent gas 226071 clean automatic 4wd SUV
9495 2012 mazda cx-9 excellent gas 183954 clean automatic fwd SUV blue
6995 2005 toyota sienna excellent gas 180821 clean automatic fwd mini-van blue
5995 2003 toyota corolla excellent gas 188596 clean automatic fwd sedan grey
2500 2008 kia sportage good 4 cylinders gas 140000 clean automatic fwd sub-compact SUV black
9200 2005 toyota tundra sr5 excellent 8 cylinders gas 182000 clean automatic rwd full-size truck red
1000 2003 chrysler town & country excellent 6 cylinders gas 170000 clean automatic rwd full-size mini-van grey
37990 2015 ford f150 supercrew cab king good other 54520 clean other 4wd pickup brown
41990 2012 bmw m3 coupe 2d good 8 cylinders gas 31572 clean other rwd coupe blue
52990 2018 bmw m3 sedan 4d good other 44724 clean other sedan white
28990 2016 ford f150 supercrew cab xl good 8 cylinders gas 53251 clean other pickup brown
2000 1998 mercedes-benz c-class good 4 cylinders gas 232000 clean automatic rwd full-size sedan black
4995 1999 ford expedition excellent gas 272412 clean automatic rwd SUV
12495 2009 chevrolet tahoe excellent gas 200039 clean automatic 4wd SUV black
11495 2016 kia soul excellent gas 114843 clean automatic fwd wagon white
6495 2005 mercury mariner excellent gas 155394 clean automatic fwd SUV silver
7995 2009 bmw 3 series excellent gas 191536 clean automatic rwd sedan grey
24950 1996 ford f350 good 8 cylinders diesel 89887 clean automatic 4wd full-size truck white
6500 2000 chevrolet flatbed good 8 cylinders gas 141750 clean automatic rwd full-size pickup white
28990 2019 acura ilx technology and a-spec good gas 5736 clean other fwd sedan black
28590 2017 buick enclave leather sport good other 29324 clean other other
25990 2017 jaguar xe 20d sedan 4d good diesel 15718 clean other sedan black
19590 2017 acura ilx sedan 4d good diesel 29014 clean other fwd sedan black
25999 2014 ford f-150 excellent 6 cylinders gas 59000 rebuilt automatic 4wd full-size truck grey
10995 2011 mercedes-benz r-class excellent gas 171865 clean automatic 4wd wagon black
7995 2011 kia sorento excellent gas 151544 clean automatic fwd SUV grey
14995 2010 chevrolet camaro excellent gas 91596 clean manual rwd coupe
9495 2009 bmw 3 series excellent gas 159656 clean automatic 4wd sedan white
9495 2005 infiniti g35 excellent gas 146561 clean automatic rwd coupe red
9995 2006 toyota tundra excellent gas 201461 clean automatic rwd pickup
4495 2006 honda odyssey excellent gas 238229 clean automatic fwd mini-van grey
10995 2008 mercedes-benz c-class excellent gas 99883 clean automatic 4wd sedan silver
8995 2005 lexus ls 430 excellent gas 213104 clean automatic rwd sedan silver
32590 2017 audi a6 3.0t prestige sedan 4d good other 34303 clean other sedan blue
17990 2018 hyundai sonata se sedan 4d good gas 17897 clean other fwd sedan silver
24990 2016 lexus nx 200t sport utility 4d good gas 34818 clean automatic fwd hatchback white
31590 2019 cadillac xt4 sport suv 4d good other 12102 clean other fwd hatchback black
3500 2004 mazda rx8 good gas 4200 clean manual sub-compact red
8995 2011 bmw 3 series excellent gas 172222 clean automatic rwd sedan grey
11995 2008 ford expedition excellent gas 144597 clean automatic SUV orange
4495 2003 chevrolet malibu excellent gas 113262 clean automatic fwd sedan grey
9995 2006 infiniti g35 excellent gas 153107 clean automatic rwd coupe white
24590 2017 acura rdx sport utility 4d good 6 cylinders gas 36731 clean automatic SUV
11995 2007 chevrolet avalanche excellent gas 195976 clean automatic 4wd pickup black
8495 2008 honda accord excellent gas 177122 clean automatic fwd sedan black
12995 2012 ford explorer excellent gas 148840 clean automatic fwd SUV white
9495 2008 mazda cx-9 excellent gas 161541 clean automatic fwd SUV grey
9995 2012 honda odyssey excellent gas 165812 clean automatic fwd mini-van grey
19990 2018 mitsubishi outlander es sport good other 15207 clean automatic 4wd SUV
27990 2018 alfa-romeo romeo stelvio ti sport good gas 16493 clean other hatchback blue
36590 2018 acura mdx advance and good other 19139 clean automatic fwd SUV
1200 2006 honda odyssey good gas 201000 clean automatic full-size mini-van black
22800 2008 toyota fj cruiser excellent 6 cylinders gas 117000 clean automatic rwd SUV
22590 2017 lincoln mkz premiere sedan 4d good other 20624 clean other sedan blue
17990 2013 lincoln mkz sedan 4d good 6 cylinders gas 41750 clean automatic fwd sedan white
13995 2002 mercedes-benz benz cl 500 amg excellent 8 cylinders gas 53000 clean automatic rwd coupe black
34990 2018 ford mustang gt premium good 8 cylinders gas 18650 clean other rwd convertible black
40990 2014 chevrolet silverado 2500 hd crew good 8 cylinders gas 53263 clean other pickup white
4700 2011 nissan altima excellent 4 cylinders gas 136040 clean automatic rwd full-size sedan black
20590 2020 toyota corolla le sedan 4d good gas 1688 clean other fwd sedan blue
33590 2017 ford mustang gt coupe 2d good 8 cylinders gas 10744 clean other rwd coupe
26000 2013 ford f-150 excellent 6 cylinders gas 99500 clean automatic 4wd full-size truck blue
3995 1996 ford explorer excellent gas 185485 clean automatic 4wd SUV red
14995 2010 ford f-150 excellent gas 179429 clean automatic pickup red
7495 2009 jeep liberty excellent gas 191610 clean automatic SUV blue
9995 2011 cadillac cts excellent gas 108164 clean automatic 4wd sedan silver
7900 2002 toyota 4runner good 6 cylinders gas 269700 clean automatic
9495 2010 bmw 3 series excellent gas 121229 clean automatic rwd sedan blue
10995 2011 bmw 3 series excellent gas 105943 clean automatic rwd sedan white
5995 2004 honda odyssey excellent gas 179893 clean automatic fwd mini-van silver
7995 2009 mercedes-benz e-class excellent diesel 168860 clean automatic rwd sedan white
11995 2008 chevrolet colorado excellent gas 137042 clean automatic pickup silver
11995 2007 gmc yukon excellent gas 203986 clean automatic 4wd SUV blue
12995 2011 ford explorer excellent gas 166267 clean automatic fwd SUV purple
10995 2011 rover lr2 excellent gas 114575 clean automatic 4wd SUV grey
3995 1997 chevrolet lumina excellent gas 0 clean automatic fwd sedan
3995 2001 lincoln ls excellent gas 204407 clean automatic rwd sedan black
6495 2010 mazda tribute i good 4 cylinders gas 113464 clean automatic fwd mid-size SUV black
27590 2018 bmw 3 series 330i xdrive sedan good other 20845 clean other sedan blue
27990 2018 bmw 3 series 330i xdrive sedan good gas 24379 clean other sedan black
26590 2018 bmw 3 series 330i xdrive sedan good gas 19980 clean automatic sedan black
24590 2018 bmw 3 series 330i xdrive sedan good other 38872 clean other sedan black
23500 2013 chevrolet avalanche ltz excellent 8 cylinders gas 148000 clean automatic fwd full-size truck white
8995 2008 mercedes-benz e-class excellent gas 112214 clean automatic rwd sedan silver
5995 2004 ford explorer excellent gas 180767 clean automatic rwd SUV green
5495 2003 infiniti g35 excellent gas 203067 clean automatic rwd sedan silver
12995 2010 mercedes-benz e-class excellent gas 103275 clean automatic rwd sedan grey
7995 2007 infiniti m45 excellent gas 214740 clean automatic rwd sedan black
13995 2013 bmw 3 series excellent gas 135848 clean automatic rwd sedan white
30990 2018 infiniti qx60 3.5 sport utility good 6 cylinders gas 22821 clean automatic fwd SUV blue
30990 2019 lincoln continental select sedan good 6 cylinders gas 25786 clean other fwd sedan silver
52990 2020 jaguar e-pace p300 r-dynamic se good gas 79 clean other other white
5999 2008 toyota sienna good 6 cylinders gas 112000 clean automatic fwd full-size mini-van silver
7500 2013 mazda 6 isport like new 4 cylinders gas 121399 clean manual fwd sedan black
39990 2018 ford f150 supercrew cab xl good 8 cylinders other 41886 clean other 4wd pickup blue
8495 2010 mazda cx-9 excellent gas 183761 clean automatic fwd SUV white
10995 2003 ford excursion excellent gas 197926 clean automatic rwd SUV silver
11500 2009 bmw 3 series excellent gas 43280 clean automatic rwd sedan red
7995 2004 infiniti g35 excellent gas 156220 clean automatic 4wd sedan white
9995 2011 honda odyssey excellent gas 164677 clean automatic fwd mini-van white
43990 2015 bmw m3 sedan 4d good other 33780 clean other rwd sedan black
38590 2019 ford f150 supercrew cab xlt good other 77492 clean other pickup
33990 2011 bmw m3 coupe 2d good 8 cylinders gas 52294 clean other rwd coupe black
5500 2006 toyota sienna le good gas 142000 clean automatic mini-van custom
16995 2012 jeep wrangler sport excellent 6 cylinders gas 148000 clean automatic 4wd black
23995 2017 jeep cherokee trailhawk excellent 6 cylinders gas 58000 clean automatic 4wd red
13995 2015 chevrolet traverse excellent 6 cylinders gas 158000 clean automatic 4wd white
12995 2016 jeep compass excellent 4 cylinders gas 84000 clean automatic fwd white
15995 2013 chevrolet tahoe lt excellent 8 cylinders gas 171000 clean automatic rwd brown
5900 2005 chevrolet colorado excellent 5 cylinders gas 290000 clean automatic truck green
43590 2016 audi s7 sedan 4d good 8 cylinders gas 52366 clean other sedan
23590 2020 acura ilx sedan 4d good other 2630 clean other sedan black
43990 2020 buick enclave premium sport good other 2212 clean other other black
8105 2012 volvo s60 t5 good gas 114850 clean automatic sedan grey
14990 2017 bmw i3 good electric 21000 clean automatic rwd hatchback black
31800 2016 lexus rx 450h awd like new 6 cylinders hybrid 64559 clean automatic 4wd mid-size SUV white
6500 2011 toyota camry excellent 6 cylinders gas 138709 clean automatic rwd mid-size sedan grey
22990 2020 hyundai sonata se sedan 4d good gas 3066 clean other fwd sedan blue
10495 2007 dodge dakota excellent gas 137876 clean automatic pickup
12995 2004 chevrolet silverado 1500 excellent gas 189745 clean automatic 4wd pickup
5495 2004 nissan xterra excellent gas 205269 clean automatic 4wd SUV grey
35990 2020 cadillac xt4 sport suv 4d good gas 10647 clean other hatchback black
30590 2019 lexus nx 300 sport utility 4d good other 40251 clean other hatchback white
41590 2020 audi a6 45 tfsi premium plus good gas 27160 clean other sedan black
38990 2020 acura rdx advance pkg sport good gas 14028 clean other fwd SUV white
37590 2019 acura mdx advance pkg sport good other 30356 clean other other silver
8495 2008 gmc yukon xl excellent gas 226071 clean automatic 4wd SUV
10995 2011 nissan armada excellent gas 172210 clean automatic SUV blue
9995 2005 chevrolet silverado 1500 excellent gas 207449 clean automatic rwd pickup
11995 2009 chevrolet silverado 1500 excellent gas 189611 clean automatic pickup black
6995 2005 toyota sienna excellent gas 180821 clean automatic fwd mini-van blue
19590 2017 mitsubishi outlander se sport good other 50473 clean other 4wd SUV brown
35990 2018 alfa-romeo romeo stelvio ti sport good gas 19988 clean other hatchback white
4000 2011 kia sorento ex good 4 cylinders gas 118000 clean automatic 4wd mid-size SUV brown
28990 2017 bmw 4 series 440i xdrive gran good gas 49355 clean automatic coupe
37990 2016 chevrolet camaro ss coupe 2d good 8 cylinders gas 9704 clean other rwd coupe red
27990 2014 toyota tacoma access cab pickup good other 17805 clean other pickup red
30990 2019 mazda mx-5 miata grand touring good gas 4936 clean other rwd other black
9000 2019 nissan versa like new 4 cylinders gas 8000 rebuilt automatic fwd compact sedan grey
35990 2018 ford f150 super cab xlt pickup good 6 cylinders gas 22812 clean other pickup black
34590 2015 ford mustang gt premium good 8 cylinders gas 30970 clean other rwd other white
25990 2014 gmc sierra 1500 regular cab good 6 cylinders gas 15575 clean other pickup brown
34990 2010 chevrolet corvette coupe 2d good 8 cylinders gas 19502 clean other rwd coupe blue
33590 2019 ford ranger supercrew xl pickup good gas 33424 clean other 4wd pickup white
29990 2016 chevrolet colorado extended cab good 6 cylinders gas 17302 clean other 4wd pickup red
37990 2016 toyota tundra double cab sr good 8 cylinders gas 22870 clean other 4wd pickup black
36590 2019 tesla model 3 standard range good electric 23057 clean other rwd sedan white
6200 2003 toyota tacoma good 4 cylinders gas 123165 clean automatic rwd pickup silver
32990 2013 tesla model s signature good electric 62588 clean other rwd sedan white
20990 2011 jeep wrangler sport suv 2d good 6 cylinders gas 78712 clean other 4wd SUV red
35990 2020 dodge challenger r/t coupe 2d good 8 cylinders gas 1548 clean other rwd coupe black
16590 2015 volkswagen beetle 1.8t fleet good gas 39649 clean other fwd hatchback black
3500 2002 gmc yukon xl good gas 200000 clean automatic
31590 2015 toyota tacoma double cab good 6 cylinders gas 9567 clean other pickup silver
39990 2019 nissan 370z nismo coupe 2d good 6 cylinders gas 8373 clean other rwd coupe white
17990 2015 volkswagen golf tdi sel good diesel 51482 clean other fwd sedan white
23590 2012 ford f150 supercrew cab xlt good 8 cylinders gas 53752 clean other pickup white
25990 2019 ram 1500 classic regular cab good 8 cylinders gas 12302 clean other pickup white
36590 2018 jeep wrangler unlimited all new good 6 cylinders diesel 15813 clean other 4wd other white
28590 2010 chevrolet camaro ss coupe 2d good 8 cylinders gas 16903 clean other rwd coupe yellow
28590 2017 chevrolet silverado 1500 regular good 6 cylinders gas 102 clean other pickup red
4200 2003 gmc envoy 4x4 good 6 cylinders gas 149000 clean automatic 4wd full-size SUV red
30990 2020 mazda mx-5 miata club good gas 5999 clean other rwd other
22990 2012 toyota tacoma access cab pickup good gas 37725 clean other pickup white
35990 2015 ford mustang gt premium coupe 2d good 8 cylinders gas 4899 clean other rwd coupe red
17990 2012 volkswagen touareg tdi sport suv good 6 cylinders gas 93185 clean other other white
15990 2010 ford ranger super cab xlt pickup good 6 cylinders gas 48055 clean other pickup silver
27590 2019 jeep cherokee latitude plus good 6 cylinders gas 6955 clean other 4wd SUV silver
29590 2017 ford expedition xlt sport good 6 cylinders gas 70760 clean other 4wd SUV blue
19590 2013 bmw 1 series 135i coupe 2d good gas 83600 clean other rwd coupe black
32990 2011 bmw z4 sdrive35is roadster 2d good other 45858 clean other rwd other red
38590 2011 chevrolet corvette grand sport good 8 cylinders gas 30237 clean other rwd other red
34990 2018 bmw 4 series 430i convertible 2d good gas 39650 clean other rwd other black
31590 2018 acura mdx sport utility 4d good other 19370 clean other fwd SUV
29590 2018 acura mdx sport utility 4d good 6 cylinders gas 28921 clean other fwd other
36590 2020 buick enclave essence sport good 6 cylinders gas 3993 clean other fwd other white
37590 2020 buick enclave essence sport good 6 cylinders gas 14745 clean other other red
33990 2020 buick enclave essence sport good 6 cylinders gas 18748 clean other SUV black
30990 2019 lincoln mkz hybrid reserve ii good gas 7256 clean other fwd sedan
25990 2018 lincoln mkz premiere hybrid good hybrid 17500 clean other fwd sedan silver
14000 2013 ram 1500 4x4 good 8 cylinders gas 125000 clean automatic 4wd full-size pickup white
30590 2019 alfa-romeo romeo stelvio sport utility good gas 17304 clean other hatchback white
7000 2010 chrysler town & country excellent 6 cylinders gas 190000 clean automatic fwd mid-size mini-van custom
23590 2017 volvo s60 t5 inscription good other 25484 clean other sedan
21590 2017 volvo s60 t5 dynamic sedan 4d good gas 37202 clean other sedan red
4250 2007 honda civic good 4 cylinders gas 191400 salvage manual fwd compact sedan brown
25590 2017 ford mustang v6 convertible 2d good 6 cylinders gas 11325 clean other rwd convertible red
40590 2019 ford f250 super duty regular cab good 8 cylinders other 9313 clean other 4wd pickup white
21590 2011 mazda mx-5 miata grand touring good gas 23483 clean other rwd convertible red
7000 2015 hyundai veloster excellent 4 cylinders gas 72000 rebuilt automatic fwd compact coupe red
11000 2015 honda accord like new 4 cylinders gas 60004 rebuilt automatic fwd mid-size coupe blue
5500 2019 nissan versa excellent 4 cylinders gas 25000 rebuilt manual fwd compact sedan black
47990 2020 bmw 3 series m340i sedan 4d good other 9064 clean other rwd sedan black
27990 2016 bmw 3 series 340i sedan 4d good gas 46057 clean other rwd sedan blue
25990 2016 bmw 3 series 340i sedan 4d good gas 45611 clean automatic rwd sedan black
52590 2015 chevrolet corvette stingray good 8 cylinders gas 9740 clean other rwd convertible
25990 2017 ford expedition xlt sport good other 74795 clean other 4wd SUV red
33990 2016 ford f250 super duty cab xl good 8 cylinders gas 18380 clean other pickup white
42990 2020 chevrolet camaro ss coupe 2d good other 9424 clean other coupe orange
38990 2018 chevrolet camaro ss coupe 2d good 8 cylinders gas 5157 clean other rwd coupe
32590 2018 jeep wrangler unlimited willys good other 17300 clean other 4wd SUV black
36590 2018 bmw 4 series 430i convertible 2d good gas 29158 clean other rwd convertible white
1500 2008 nissan rogue fair 4 cylinders gas 165000 clean automatic black
42990 2020 acura mdx technology pkg sport good other 12391 clean other fwd SUV brown
45590 2020 acura mdx sh-awd w/advance pkg good 6 cylinders gas 21711 clean other other
21000 2016 ford f150 xlt like new 6 cylinders gas 45000 rebuilt automatic 4wd full-size pickup grey
43990 2020 buick enclave avenir sport good 6 cylinders gas 21086 clean other fwd other white
42590 2020 buick enclave avenir sport good other 24301 clean other fwd SUV white
34990 2020 buick enclave essence sport good 6 cylinders gas 18318 clean other SUV red
16590 2013 lincoln mkz sedan 4d good gas 61087 clean other fwd sedan red
29990 2018 alfa-romeo romeo stelvio ti sport good other 26978 clean other hatchback
37590 2018 acura mdx sport hybrid sh-awd good 6 cylinders hybrid 38840 clean other SUV brown
18990 2015 volvo s60 t5 premier sedan 4d good other 37238 clean other sedan silver
35990 2020 volvo s60 t6 inscription sedan good other 11878 clean other sedan blue
40990 2014 chevrolet silverado 2500 hd crew good 8 cylinders gas 53263 clean other pickup white
35990 2018 ford mustang gt coupe 2d good 8 cylinders gas 5160 clean other rwd coupe orange
33590 2017 ford mustang gt coupe 2d good 8 cylinders gas 10744 clean other rwd coupe
27590 2018 bmw 3 series 330i xdrive sedan good other 20845 clean other sedan blue
24990 2018 bmw 3 series 330i xdrive sedan good gas 28946 clean other sedan white
21590 2013 bmw 3 series 335i xdrive sedan good gas 62993 clean other sedan silver
28990 2018 infiniti qx60 3.5 sport utility good 6 cylinders gas 32458 clean other fwd SUV black
52990 2020 jaguar e-pace p300 r-dynamic se good gas 79 clean other other white
41590 2020 audi a6 45 tfsi premium plus good gas 27160 clean other sedan black
23590 2011 chevrolet camaro ss coupe 2d good 8 cylinders gas 38905 clean other rwd coupe red
33990 2018 jeep wrangler unlimited sport s good other 42966 clean other 4wd SUV black
26990 2020 chevrolet camaro lt coupe 2d good 6 cylinders gas 18634 clean automatic rwd coupe red
37590 2019 acura mdx advance pkg sport good other 30356 clean other other silver
20590 2015 bmw 4 series 428i gran coupe good gas 65290 clean automatic coupe black
5000 2010 honda cr-v good gas 137000 clean automatic 4wd SUV grey
36990 2020 buick enclave essence sport good 6 cylinders gas 11777 clean other other black
31990 2019 buick enclave essence sport good 6 cylinders gas 23997 clean other SUV brown
42590 2020 buick enclave premium sport good 6 cylinders gas 1134 clean other SUV black
31590 2018 alfa-romeo romeo stelvio ti sport good other 21415 clean other hatchback blue
26590 2018 lincoln mkz reserve sedan 4d good gas 19506 clean automatic sedan red
38990 2017 acura mdx sport hybrid sh-awd good other 9326 clean other SUV white

Sample Data for Pivot Table

ID Num Gender Employment Status Expected Salary Anticipated Salary in 5 Years Spending
ID01 M PT 45 75 200 This is a sample raw data file resulting from a survey. We can create some tables using Excel/Pivot Table. Let’s construct a one way table for gender and a two way table for gender and employment status. For the one way table, click on Insert/Pivot Table. Select range and highlight all the columns. Select new worksheet. Drag gender which is to the far right in the pivot table field list down to rows. Next drag gender again down to values. It should show “count”. If not, use the drop down arrow to get value field settings and select “count”. If you want to show a bar chart, click on Insert and look for the picture of a bar chart and select it. Also for a pie chart. For a two way table, same initial steps as before. Drag gender down to rows, drag employment status down to columns, and drag either variable down to values. Again make sure count shows. A two way table can be used to examine whether two categorical variables may be related. Next let’s construct a one way table that shows the total of all the expected salary of males and females. Same initial steps, drag gender down to rows, drag expected salary down to values and make sure it shows sum. If not change it in the value field setting. One way table
ID02 M FT 120 250 150
ID03 M FT 85 120 65 Count of Gender
ID04 M FT 100 150 150 Gender Total
ID05 M FT 80 90 300 F 16
ID06 F FT 100 150 250 M 24
ID07 M FT 100 125 400 Grand Total 40
ID08 M FT 60 100 60
ID09 F FT 50 60 180 Two way table
ID10 M PT 50 80 700
ID11 M FT 60 100 100 Count of Gender Employment Status
ID12 M FT 65 100 200 Gender FT PT UN Grand Total
ID13 M FT 80 110 300 F 13 1 2 16
ID14 M FT 100 150 100 M 20 4 24
ID15 F UN 50 100 1000 Grand Total 33 5 2 40
ID16 M FT 40 65 300
ID17 F FT 70 100 200 Summary table
ID18 F FT 60 75 150
ID19 M FT 105 120 150 sum
ID20 F FT 60 100 130 Gender Total
ID21 F UN 45 65 300 F 1015
ID22 F FT 60 80 200 M 1785
ID23 M FT 80 140 90 Grand Total 2800
ID24 F FT 65 85 170
ID25 M PT 60 150 320
ID26 M FT 65 135 200
ID27 F FT 55 90 600
ID28 M FT 90 130 200
ID29 M FT 70 100 100
ID30 F FT 45 80 160
ID31 F PT 80 110 230
ID32 M FT 75 150 500
ID33 M FT 60 85 250
ID34 F FT 75 95 150
ID35 F FT 65 90 120
ID36 M FT 50 85 160
ID37 F FT 75 100 100
ID38 F FT 60 90 160
ID39 M FT 90 160 130
ID40 M PT 55 85 110

13 - 16 - Frequency

13 Frequency Distribution:
Here is a hypothetical list of the number of caramel popcorn
cans sold in 70 scout troops in Maryland. Use Excel's
capability (=FREQUENCY()) to create a frequency distribution
Number of Caramel popcorn cans sold in each troop
174 105 103 105 148 158 121 130 118 157
153 147 132 110 115 192 158 196 149 140
183 199 174 107 179 183 129 194 119 150
198 171 120 163 163 108 184 134 186 175
180 114 107 107 152 137 184 200 189 103
190 175 156 143 142 152 182 126 142 160
183 119 165 134 172 145 184 168 170 113
MIN Use the =MIN() function to find the lowest number of popcorn cans sold.
MAX Use the =MAX() function to find the highest number of popcorn cans sold.
Subtract the lowest number of cans sold from the highest number of cans sold:
The range of number of cans sold is:
14 Decide how many bins to use. Study the data and pick a number between 5 and 10
See https://www.statisticshowto.com/choose-bin-sizes-statistics/
In one paragraph below explain why you selected the numbers of bins (between 5 and 10)
15 Now divide the range of the cans sold by the number of bins to find the size that each bin should be
Size of each bin is
Create your bins:
Start with the minimum number of cans sold and add the size of the bins.
Therefore, plus is the highest number that the first bin should be.
The =FREQUENCY() function uses the highest number of each bin when calculating the number in each bin.
The next bin highest number starts with the first bin's highest number and adds the size of the bins.
Therefore, the second bin begins with and adds the bin size to get
16 Continue adding to get the Bins array for the =FREQUENCY() function.
Follow the instructions in the youtube videos to use the =FREQUENCY() array function.
You know that you have correctly used the =FREQUENCY() function if Excel automatically puts {} around the function.
Min Bins: Frequency:
Max
Note: Some versions of Excel treat the last bin a bit differently, so the FREQUENCY function may show the last bin as one higher than it should be.
If you get that, don't worry about it, we will accept either answer.

17-20 - Charts

17 As part of the marketing group of a film company, you are asked to find out the age distribution of the audience of the latest film.
You ask questions of customers who exit the theatre. From 500 responses, you find that 49 are younger than 6 years old, 87 are 6 to 9 years,
165 are 10 to 14, 25 are 15 to 21, and 174 are older than 21.
a)  Make a frequency table of these categorical data.
b) Make a relative frequency table.
c)  Make a bar chart using counts in the frequency table.
d) Would a bar chart of relative frequencies look any different?
e)  Make a pie chart.
18 Write a few sentences summarizing the distribution demonstrated by your charts and tables.
19 In addition to age grouping information, the audiences interviewed were also asked if they had seen the movie before (Never, Once, More than Once).
under 6 6 to 9 10 to 14 15 to 21 over 21
never 42 62 90 20 152
once 4 21 41 5 17
more than once 3 4 34 0 5
a)  Find the marginal distributions of their previous viewing of the movie. Margin means total. Create a new column labeled "total".
b) Verify that the marginal distribution of the ages is the same as that given previously. Create a bottom row labeled "total".
20 Continue with the data from question 17
c)  Find column percentages.
d) Looking at these percentages, does the distribution of how many times someone has seen the movie look the same for each age group? What can you assume from these percentages?
e)  Make a stacked bar chart showing the distribution of viewings for each age level
f) What is the percentage of all audience members who are over 21? And what percentage of audience members who saw the movie more than once are over 21?
g) If these proportions are not the same, what does that mean with respect to the variables "age" and "frequency of viewing movies"?