Please see numerical data to answer the questions in an excel sheet
F G H M N 2. Quantitative Data - 15 pts O One way to organize data numerically is to create a frequency distribution. This is important for both quantitative data (numbers that behave as numbers) and qualitative data, where we are interested in some non-numerical attribute. Using the data set in the second tab, create the distributions described below. Instructions: Part A - 5 pts Beginning in cell A1 on this page, create a frequency distribution for the quantitative "Potassium" column from your Data Set. You should have classes 0-49, 50-99, 100-149, 150-199, 200-249, 250-299 and 300-349 in column A and counts for each range in column B. If you get a (blank) row, be sure to remove it. Use the "Location" option to choose the appropriate location for your display using the "Pivot Table" feature. Part B - 5 pts Using the pivot table from your output in Part A, create a histogram. Entitle your histogram "Potassium in Breakfast Cereal". Ensure you have a gap-width of zero and that your frequency scale has increments of 5. Add an axis title of "Frequency" to the vertical axis and "Sodium" to the horizontal axis. Position the histogram below your pivot table from Part A beginning in cell A10. Feel free to move this text box out of the way to make room. Part C - 5 pts Based on your histogram from Part B, about how much potassium would you expect to consume eating cereal for breakfast? Answer in a complete sentence(s), making sure to cite your STATISTICAL reason(s) for your solution and citing the histogram from part B. Type your solution in the textbox provided, then position the text box below your histogram. Resize the text box as needed for your solution to fit within the box. 5. Box Plot (Ch.3) Qual Data (Ch.2) 4. Sum Stats (Ch.3) 1:00 PM11 X V Fiber B C D E F G H K M N O 58 Top Quaker_Oat_SquareQ C 100 135 14 6 110 25 1 0.5 59 Bottom Quaker Oatmeal Q H 100 IN 0 2. 110 0 1 0.67 60 Middle Raisin Bran 120 210 14 12 240 25 1.33 0.75 61 Top Raisin_Nut_Bran G C IN 140 2.5 10.5 8 140 25 1 0.5 62 Top Raisin_Squares K Ic 0 0 15 6 110 25 1 0.5 ON 63 Bottom Rice_Chex R Ic 0 240 23 2 30 25 1 1.13 K 0 290 22 35 25 1 64 Bottom Rice_Krispies C 1 w O 95 0 0.83 1 65 Bottom Shredded Wheat N C 0 0 16 C 0 19 140 0 1 0.67 66 Bottom Shredded_Wheat_'IN 20 0 1 0.67 -HWA 67 Bottom Shredded_Wheat_SN C 120 40 25 1 0.75 WNNONWWNN 68 Middle Smacks K 1 70 0 16 55 25 1 Special_K K 10 230 69 Bottom 15 w N w w A w u woo Ow 0 90 25 Strawberry_Fruit_WN 18 70 Middle 35 LOO g S OwPOWH 71 Top Total_Corn_Flakes G .10 15 230 LOO 1.5 Total Raisin_Bran G 140 72 Top 100 100 1 200 16 Total_Whole_Grain G 0.75 73 Top 1 250 21 74 Top Triples 865 1 140 13 25 75 Middle Trix 17 25 0.67 1 230 HWWO 25 1 W W 76 Bottom Wheat_Chex R w 110 G 100 1 200 17 60 25 1 0.75 77 Bottom Wheaties 110 2 1 200 16 78 Bottom Wheaties_Honey_GG 79 80 81 82 4. Sum Stats (Ch.3) 5. Box Plot (Ch.3) 83 Data Sets 1. Rand# (Ch.1) 2. Quant Data (Ch.2) 3. Qual Data (Ch.2) Average: 1.903463203 Count 234 Sum: 4 Instructions O 2 L W Ready Ei Type here to search3. Qualitative Data - 10 pts We are interested in where the grocery store tends to position cereals. For our market research, we are interested in finding out the distribution of cereal shelf location of our competition. Using the "Shelf" column in your data set page, complete the following. Instructions: Part A - 5 pts Beginning in cell A1, create a frequency distribution for the qualitative "Shelf" column from your Data Set using the "Pivot Table" feature. If you get a (blank) row, be sure to remove it. You should have the shelf location in column A and the count for each cereal in that location displayed as a percent of the grand total in column B. Part B - 5 pts Using the pivot table from Part A, create a pie chart that graphically displays your data. Give it a title of "Cereal Location" and a legend. Have the percentage for each shelf displayed on your pie chart. Position the pie chart beneath your pivot table.Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 11 X V fx Fiber A C D E F G H K M N O P 25 Top Double_Chex R C 100 190 18 5 80 25 1 0.75 ININ 26 Middle Froot Loops TO 110 125 11 13 30 25 1 1 27 Bottom Frosted Flakes K C 110 1 200 14 11 25 25 1 0.75 28 Middle Frosted Mini-Wheek C 100 3 0 14 7 100 25 1 0.8 29 Top Fruit_&_Fibre_Date P C 120 2 160 12 10 200 25 1.25 0.67 W W 1.33 0.67 30 Top Fruitful_Bran 0 240 190 25 K 120 14 1 135 13 12 25 25 0.75 31 Middle Fruity_Pebbles 11 15 40 25 0.88 32 Bottom Golden_Crisp 0 45 1 280 15 45 25 0.75 33 Middle Golden_Grahams G 85 25 0.88 HWWWNA 140 15 34 Top Grape_Nuts_Flakes P 25 0.25 0 170 90 35 Top Grape-Nuts P 100 0.33 75 1 36 Top Great_Grains_Pecar P NW 220 37 Middle Honey_Graham_Oh Q. 1 0.75 WOO NON HOUPWWW OOOU 250 1.33 38 Bottom Honey_Nut_Cheeri G 180 39 Bottom Honey-comb 170 1.3 0.75 40 Top Just_Right_Crunchy K 170 1.5 41 Top Just_Right_Fruit & K 260 25 0.67 42 Middle Kix 2 150 12 12 55 25 1 1 43 Middle Life NWASANDNUNE 1 180 95 25 1 1 w Lucky_Charms 100 1 0 16 44 Middle 3 95 16 11 170 25 1 1 Maypo 1 45 Middle 150 Muesli_Raisins,_Da R 150 16 11 170 25 0.67 w 1.5 46 Top 150 IN W W 17 13 160 25 Muesli_Raisins,_Pe R 160 2 150 90 25 1 47 Top 15 Mueslix_Crispy_Blek 220 0.67 100 40 25 48 Top 1 15 Multi-Grain_Cheeri G 190 4. Sum Stats (Ch.3) 5. Box Plot (Ch.3) 49 Bottom 120 3. Qual Data (Ch.2) 50 |Middle Nut&Honey_Crunch k 1. Rand# (Ch.1) 2. Quant Data (Ch.2) Average 1.903463203 Count 234 Sum 439.7 Instructions Data Sets 2 O L Ready O Type here to search1. Random Number Creation - 5 pts Random numbers are used in sampling, simulations and even hypothesis testing, which we will learn more about later in this course. Often random number tables are used, though technology makes it easy to come up with our own random numbers. Beware, however, as many generators follow a prescribed algorithm, so what appears to be random is not! For this question, all you need to do is use the RANDBETWEEN function to make Excel find some random numbers for you. Instructions: Part A - 1 pt In column A (cells 1-10): Generate 10 random numbers from 0 to 100 Part B - 2 pts In column B (cells 1-15): Generate 15 random numbers from 10 to 20 Part C - 2 pts In column C (cells 1-20): Generate 20 random numbers from 75 to 1001 PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X V fx Fiber A C D E F G H K M N O P Bottom Multi-Grain_Cheeri G C 100 1 220 15 6 90 25 1 ON Middle Nut&Honey_Crunch K C 120 190 15 9 40 25 1 0.67 WN Top Nutri-Grain_Almon K C 140 220 W IN 21 7 130 25 1.33 0.67 Top Nutri-grain_Wheat K DE 0 170 18 2 90 25 Top Oatmeal Raisin_CriG C 130 2 170 1.5 13.5 10 120 25 1.25 0.5 Top Post Nat. Raisin B P C 120 200 11 14 260 25 1.33 0.6 100 Top Product 19 LOO 20 3 X C 0 320 45 13 0 15 0 0.5 1 Top Puffed_Rice Q C 0 0 0 1 Puffed_Wheat Q 50 0 0 10 50 0.5 a o Top Quaker_Oat_Square Q 100 1 135 14 110 25 1 0.5 Top 0 110 0 0.67 Quaker_Oatmeal Q H WWUPN Bottom 14 25 1.33 210 12 240 0.75 50 Middle Raisin_Bran X C 140 140 25 1 0.5 61 Top Raisin_Nut_Bran G C ON 110 25 0.5 N 62 Top Raisin_Squares C 30 25 1.13 240 OWN CO Bottom Rice_Chex oo 53 290 35 25 HWAWOONinU 64 Bottom Rice_Krispies K 16 95 0 0.83 0.67 65 Bottom Shredded_Wheat N 19 140 0 Shredded_Wheat_'IN oo oo 0.6 66 Bottom 20 120 0 70 15 40 25 1 0.75 67 Bottom Shredded_Wheat_SN 1 K 25 1 1 68 Middle Smacks NWWNNONWWNN 0 230 16 55 90 25 1 1 69 Bottom Special_K K 15 15 90 0 35 100 1 70 Middle Strawberry_Fruit_WN 21 110 1 200 OwDOWE 15 14 230 100 1.5 71 Top Total_Corn_Flakes G 140 1 190 100 1 1 Total_Raisin_Bran G 200 16 110 72 Top 100 0.75 21 60 25 73 Top Total_Whole_Grain G 110 250 5. Box Plot (Ch.3) Triples G 74 Top 3. Qual Data (Ch.2) 4. Sum Stats (Ch.3) 1. Rand# (Ch.1). 2. Quant Data (Ch.2) Average: 1.903463203 Count: 234 Sum: 439.7 Instructions Data Sets 2 L Ready O EI Type here to searchSearch File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 11 X V fx Fiber A B C D E G H K M N O P Shelf Name Manufacturepe Calories Protein Fat Sodium Fiber Carboh Sugars Potassium Vitamins Weight ( Cups in Serving 2 Top 100%_Bran N C 70 P 130 10 6 280 25 0.33 3 Top 100% Natural_Bran Q C 120 15 2 135 0 1 1 4 Top All-Bran K C 70 260 5 320 25 1 0.33 5 Top All-Bran_with_Extrak 50 4 0 140 14 0 330 25 1 0.5 Almond_Delight R 110 2 2 200 14 25 1 D.75 6 Top 1 7 Bottom Apple_Cinnamon_CG 110 2 180 1.5 10.5 10 70 25 0.75 25 1 1 8 Middle Apple_Jacks 10 2 0 125 11 14 30 Basic 4 3 Top 2 210 18 100 25 1.33 0.75 1 200 125 1 0.67 10 Bottom Bran_Chex 2 3 0 210 13 0.6 11 Top Bran_Flakes 2 220 12 0.75 12 Middle Cap'n'Crunch 2 290 17 1.25 13 Bottom Cheerios 3 210 13 HHPPP 0.75 WENWWJOHNMOO 14 Middle Cinnamon_Toast_CIG 0.5 140 15 Top Clusters OHN ENNEWPO 180 HNEPAOPPOONONOUANA 16 Middle Cocoa_Puffs 280 17 Bottom Corn_Chex 0 290 18 Bottom Corn_Flakes 110 0 90 Middle Corn_Pops 25 1 1 110 180 Count_Chocula 140 10 160 25 1 0.5 20 Middle 110 3 1 1 wOv 21 Top Cracklin'_Oat_Bran K 100 0 80 21 0 1 22 Middle Cream_of_Wheat_( N 21 30 25 NNNNWWE 110 0 220 25 1 0.75 23 Top Crispix K 100 1 140 11 10 120 25 1 0.75 un O 18 80 24 Top Crispy_Wheat & R. G 100 190 25 1 R 1 11 13 30 25 Top Double_Chex 110 1 125 4. Sum Stats (Ch.3) 5. Box Plot (Ch.3) Froot_Loops K 26 | Middle 3. Qual Data (Ch.2) Data Sets 1. Rand# (Ch.1) 2. Quant Data (Ch.2) Average 1.903463203 Count: 234 Sum: 439.7 Instructions L Ready O 2 Type here to searchG H 4. Summary Statistics - 5 pts We will use summary statistics throughout the course. For now, it is just a great numerical summary of our data and a way to compare distributions. Later on, we will use these summary stats in more advanced calculations. Instructions: Use Excel to calculate all summary statistics for the potassium content of our cereal data from the Data Set page. Do NOT find each summary statistic separately. You are to use the Data Analysis Tookpak to do this. Display your summary statistics beginning in cell A1. Keep in mind that you will have to have the Data Analysis ToolPak option enabled.5. Box Plot - 5 pts We are always interested in being able to compare "apples to apples". In statistics, this means that we want to create displays that have the same scale on our axes. For this question, we are going to compare the protein, fat, and fiber in our cereals. Instructions: Create one display that contains a box plot for each of the columns of interest including protein, fat and fiber on the same scale. You can select columns using the "control" button on your keyboard. Entitle the box plot "Cereal Nutrition" and add a legend and label your vertical axis "Grams". Begin your display in cell A1. Hint: If your box plot shows up on the "Data Sets" page, simply select it, then cut and paste to this page and move to the desired location
