Remove all records with missing numerical measurements from the dataset by creat
ID: 3354765 • Letter: R
Question
Remove all records with missing numerical measurements from the dataset by creating a new worksheet. You can use the “Missing Data Handling utility in XLMiner”
name mfr type calories protein fat sodium fiber carbo sugars potass vitamins shelf weight cups rating 100%_Bran N C 70 4 1 130 10 5 6 280 25 3 1 0.33 68.40297 100%_Natural_Bran Q C 120 3 5 15 2 8 8 135 0 3 1 1 33.98368 All-Bran K C 70 4 1 260 9 7 5 320 25 3 1 0.33 59.42551 All-Bran_with_Extra_Fiber K C 50 4 0 140 14 8 0 330 25 3 1 0.5 93.70491 Almond_Delight R C 110 2 2 200 1 14 8 25 3 1 0.75 34.38484 Apple_Cinnamon_Cheerios G C 110 2 2 180 1.5 10.5 10 70 25 1 1 0.75 29.50954 Apple_Jacks K C 110 2 0 125 1 11 14 30 25 2 1 1 33.17409 Basic_4 G C 130 3 2 210 2 18 8 100 25 3 1.33 0.75 37.03856 Bran_Chex R C 90 2 1 200 4 15 6 125 25 1 1 0.67 49.12025 Bran_Flakes P C 90 3 0 210 5 13 5 190 25 3 1 0.67 53.31381 Cap'n'Crunch Q C 120 1 2 220 0 12 12 35 25 2 1 0.75 18.04285 Cheerios G C 110 6 2 290 2 17 1 105 25 1 1 1.25 50.765 Cinnamon_Toast_Crunch G C 120 1 3 210 0 13 9 45 25 2 1 0.75 19.82357 Clusters G C 110 3 2 140 2 13 7 105 25 3 1 0.5 40.40021 Cocoa_Puffs G C 110 1 1 180 0 12 13 55 25 2 1 1 22.73645 Corn_Chex R C 110 2 0 280 0 22 3 25 25 1 1 1 41.44502 Corn_Flakes K C 100 2 0 290 1 21 2 35 25 1 1 1 45.86332 Corn_Pops K C 110 1 0 90 1 13 12 20 25 2 1 1 35.78279 Count_Chocula G C 110 1 1 180 0 12 13 65 25 2 1 1 22.39651 Cracklin'_Oat_Bran K C 110 3 3 140 4 10 7 160 25 3 1 0.5 40.44877 Cream_of_Wheat_(Quick) N H 100 3 0 80 1 21 0 0 2 1 1 64.53382 Crispix K C 110 2 0 220 1 21 3 30 25 3 1 1 46.89564 Crispy_Wheat_&_Raisins G C 100 2 1 140 2 11 10 120 25 3 1 0.75 36.1762 Double_Chex R C 100 2 0 190 1 18 5 80 25 3 1 0.75 44.33086 Froot_Loops K C 110 2 1 125 1 11 13 30 25 2 1 1 32.20758 Frosted_Flakes K C 110 1 0 200 1 14 11 25 25 1 1 0.75 31.43597 Frosted_Mini-Wheats K C 100 3 0 0 3 14 7 100 25 2 1 0.8 58.34514 Fruit_&_Fibre_Dates,_Walnuts,_and_Oats P C 120 3 2 160 5 12 10 200 25 3 1.25 0.67 40.91705 Fruitful_Bran K C 120 3 0 240 5 14 12 190 25 3 1.33 0.67 41.01549 Fruity_Pebbles P C 110 1 1 135 0 13 12 25 25 2 1 0.75 28.02577 Golden_Crisp P C 100 2 0 45 0 11 15 40 25 1 1 0.88 35.25244 Golden_Grahams G C 110 1 1 280 0 15 9 45 25 2 1 0.75 23.80404 Grape_Nuts_Flakes P C 100 3 1 140 3 15 5 85 25 3 1 0.88 52.0769 Grape-Nuts P C 110 3 0 170 3 17 3 90 25 3 1 0.25 53.37101 Great_Grains_Pecan P C 120 3 3 75 3 13 4 100 25 3 1 0.33 45.81172 Honey_Graham_Ohs Q C 120 1 2 220 1 12 11 45 25 2 1 1 21.87129 Honey_Nut_Cheerios G C 110 3 1 250 1.5 11.5 10 90 25 1 1 0.75 31.07222 Honey-comb P C 110 1 0 180 0 14 11 35 25 1 1 1.33 28.74241 Just_Right_Crunchy__Nuggets K C 110 2 1 170 1 17 6 60 100 3 1 1 36.52368 Just_Right_Fruit_&_Nut K C 140 3 1 170 2 20 9 95 100 3 1.3 0.75 36.47151 Kix G C 110 2 1 260 0 21 3 40 25 2 1 1.5 39.24111 Life Q C 100 4 2 150 2 12 6 95 25 2 1 0.67 45.32807 Lucky_Charms G C 110 2 1 180 0 12 12 55 25 2 1 1 26.73452 Maypo A H 100 4 1 0 0 16 3 95 25 2 1 1 54.85092 Muesli_Raisins,_Dates,_&_Almonds R C 150 4 3 95 3 16 11 170 25 3 1 1 37.13686 Muesli_Raisins,_Peaches,_&_Pecans R C 150 4 3 150 3 16 11 170 25 3 1 1 34.13977 Mueslix_Crispy_Blend K C 160 3 2 150 3 17 13 160 25 3 1.5 0.67 30.31335 Multi-Grain_Cheerios G C 100 2 1 220 2 15 6 90 25 1 1 1 40.10597 Nut&Honey_Crunch K C 120 2 1 190 0 15 9 40 25 2 1 0.67 29.92429 Nutri-Grain_Almond-Raisin K C 140 3 2 220 3 21 7 130 25 3 1.33 0.67 40.69232 Nutri-grain_Wheat K C 90 3 0 170 3 18 2 90 25 3 1 1 59.64284 Oatmeal_Raisin_Crisp G C 130 3 2 170 1.5 13.5 10 120 25 3 1.25 0.5 30.45084 Post_Nat._Raisin_Bran P C 120 3 1 200 6 11 14 260 25 3 1.33 0.67 37.84059 Product_19 K C 100 3 0 320 1 20 3 45 100 3 1 1 41.50354 Puffed_Rice Q C 50 1 0 0 0 13 0 15 0 3 0.5 1 60.75611 Puffed_Wheat Q C 50 2 0 0 1 10 0 50 0 3 0.5 1 63.00565 Quaker_Oat_Squares Q C 100 4 1 135 2 14 6 110 25 3 1 0.5 49.51187 Quaker_Oatmeal Q H 100 5 2 0 2.7 110 0 1 1 0.67 50.82839 Raisin_Bran K C 120 3 1 210 5 14 12 240 25 2 1.33 0.75 39.2592 Raisin_Nut_Bran G C 100 3 2 140 2.5 10.5 8 140 25 3 1 0.5 39.7034 Raisin_Squares K C 90 2 0 0 2 15 6 110 25 3 1 0.5 55.33314 Rice_Chex R C 110 1 0 240 0 23 2 30 25 1 1 1.13 41.99893 Rice_Krispies K C 110 2 0 290 0 22 3 35 25 1 1 1 40.56016 Shredded_Wheat N C 80 2 0 0 3 16 0 95 0 1 0.83 1 68.23589 Shredded_Wheat_'n'Bran N C 90 3 0 0 4 19 0 140 0 1 1 0.67 74.47295 Shredded_Wheat_spoon_size N C 90 3 0 0 3 20 0 120 0 1 1 0.67 72.80179 Smacks K C 110 2 1 70 1 9 15 40 25 2 1 0.75 31.23005 Special_K K C 110 6 0 230 1 16 3 55 25 1 1 1 53.13132 Strawberry_Fruit_Wheats N C 90 2 0 15 3 15 5 90 25 2 1 1 59.36399 Total_Corn_Flakes G C 110 2 1 200 0 21 3 35 100 3 1 1 38.83975 Total_Raisin_Bran G C 140 3 1 190 4 15 14 230 100 3 1.5 1 28.59279 Total_Whole_Grain G C 100 3 1 200 3 16 3 110 100 3 1 1 46.65884 Triples G C 110 2 1 250 0 21 3 60 25 3 1 0.75 39.10617 Trix G C 110 1 1 140 0 13 12 25 25 2 1 1 27.7533 Wheat_Chex R C 100 3 1 230 3 17 3 115 25 1 1 0.67 49.78745 Wheaties G C 100 3 1 200 3 17 3 110 25 1 1 1 51.59219 Wheaties_Honey_Gold G C 110 2 1 200 1 16 8 60 25 1 1 0.75 36.18756Explanation / Answer
Steps ->
1) Copy the file in Excel
2) Make a table by selecting the entire table and then pressing Alt+D+F+F
3)Select all the columns from calories to rating and convert "General" to "Numerical"
4)Filter each column from drop down option in each categoy and check for the category "Blank" or "NA"
5)Just select that column by ticking it and Delete that entire row .
6)Repeat the steps for each and every column and eliminate the blank rows.
name mfr type calories protein fat sodium fiber carbo sugars potass vitamins shelf weight cups rating 100%_Bran N C 70.00 4.00 1.00 130.00 10.00 5.00 6.00 280.00 25.00 3.00 1.00 0.33 68.40 100%_Natural_Bran Q C 120.00 3.00 5.00 15.00 2.00 8.00 8.00 135.00 0.00 3.00 1.00 1.00 33.98 All-Bran K C 70.00 4.00 1.00 260.00 9.00 7.00 5.00 320.00 25.00 3.00 1.00 0.33 59.43 All-Bran_with_Extra_Fiber K C 50.00 4.00 0.00 140.00 14.00 8.00 0.00 330.00 25.00 3.00 1.00 0.50 93.70 Apple_Cinnamon_Cheerios G C 110.00 2.00 2.00 180.00 1.50 10.50 10.00 70.00 25.00 1.00 1.00 0.75 29.51 Apple_Jacks K C 110.00 2.00 0.00 125.00 1.00 11.00 14.00 30.00 25.00 2.00 1.00 1.00 33.17 Basic_4 G C 130.00 3.00 2.00 210.00 2.00 18.00 8.00 100.00 25.00 3.00 1.33 0.75 37.04 Bran_Chex R C 90.00 2.00 1.00 200.00 4.00 15.00 6.00 125.00 25.00 1.00 1.00 0.67 49.12 Bran_Flakes P C 90.00 3.00 0.00 210.00 5.00 13.00 5.00 190.00 25.00 3.00 1.00 0.67 53.31 Cap'n'Crunch Q C 120.00 1.00 2.00 220.00 0.00 12.00 12.00 35.00 25.00 2.00 1.00 0.75 18.04 Cheerios G C 110.00 6.00 2.00 290.00 2.00 17.00 1.00 105.00 25.00 1.00 1.00 1.25 50.77 Cinnamon_Toast_Crunch G C 120.00 1.00 3.00 210.00 0.00 13.00 9.00 45.00 25.00 2.00 1.00 0.75 19.82 Clusters G C 110.00 3.00 2.00 140.00 2.00 13.00 7.00 105.00 25.00 3.00 1.00 0.50 40.40 Cocoa_Puffs G C 110.00 1.00 1.00 180.00 0.00 12.00 13.00 55.00 25.00 2.00 1.00 1.00 22.74 Corn_Chex R C 110.00 2.00 0.00 280.00 0.00 22.00 3.00 25.00 25.00 1.00 1.00 1.00 41.45 Corn_Flakes K C 100.00 2.00 0.00 290.00 1.00 21.00 2.00 35.00 25.00 1.00 1.00 1.00 45.86 Corn_Pops K C 110.00 1.00 0.00 90.00 1.00 13.00 12.00 20.00 25.00 2.00 1.00 1.00 35.78 Count_Chocula G C 110.00 1.00 1.00 180.00 0.00 12.00 13.00 65.00 25.00 2.00 1.00 1.00 22.40 Cracklin'_Oat_Bran K C 110.00 3.00 3.00 140.00 4.00 10.00 7.00 160.00 25.00 3.00 1.00 0.50 40.45 Crispix K C 110.00 2.00 0.00 220.00 1.00 21.00 3.00 30.00 25.00 3.00 1.00 1.00 46.90 Crispy_Wheat_&_Raisins G C 100.00 2.00 1.00 140.00 2.00 11.00 10.00 120.00 25.00 3.00 1.00 0.75 36.18 Double_Chex R C 100.00 2.00 0.00 190.00 1.00 18.00 5.00 80.00 25.00 3.00 1.00 0.75 44.33 Froot_Loops K C 110.00 2.00 1.00 125.00 1.00 11.00 13.00 30.00 25.00 2.00 1.00 1.00 32.21 Frosted_Flakes K C 110.00 1.00 0.00 200.00 1.00 14.00 11.00 25.00 25.00 1.00 1.00 0.75 31.44 Frosted_Mini-Wheats K C 100.00 3.00 0.00 0.00 3.00 14.00 7.00 100.00 25.00 2.00 1.00 0.80 58.35 Fruit_&_Fibre_Dates,_Walnuts,_and_Oats P C 120.00 3.00 2.00 160.00 5.00 12.00 10.00 200.00 25.00 3.00 1.25 0.67 40.92 Fruitful_Bran K C 120.00 3.00 0.00 240.00 5.00 14.00 12.00 190.00 25.00 3.00 1.33 0.67 41.02 Fruity_Pebbles P C 110.00 1.00 1.00 135.00 0.00 13.00 12.00 25.00 25.00 2.00 1.00 0.75 28.03 Golden_Crisp P C 100.00 2.00 0.00 45.00 0.00 11.00 15.00 40.00 25.00 1.00 1.00 0.88 35.25 Golden_Grahams G C 110.00 1.00 1.00 280.00 0.00 15.00 9.00 45.00 25.00 2.00 1.00 0.75 23.80 Grape_Nuts_Flakes P C 100.00 3.00 1.00 140.00 3.00 15.00 5.00 85.00 25.00 3.00 1.00 0.88 52.08 Grape-Nuts P C 110.00 3.00 0.00 170.00 3.00 17.00 3.00 90.00 25.00 3.00 1.00 0.25 53.37 Great_Grains_Pecan P C 120.00 3.00 3.00 75.00 3.00 13.00 4.00 100.00 25.00 3.00 1.00 0.33 45.81 Honey_Graham_Ohs Q C 120.00 1.00 2.00 220.00 1.00 12.00 11.00 45.00 25.00 2.00 1.00 1.00 21.87 Honey_Nut_Cheerios G C 110.00 3.00 1.00 250.00 1.50 11.50 10.00 90.00 25.00 1.00 1.00 0.75 31.07 Honey-comb P C 110.00 1.00 0.00 180.00 0.00 14.00 11.00 35.00 25.00 1.00 1.00 1.33 28.74 Just_Right_Crunchy__Nuggets K C 110.00 2.00 1.00 170.00 1.00 17.00 6.00 60.00 100.00 3.00 1.00 1.00 36.52 Just_Right_Fruit_&_Nut K C 140.00 3.00 1.00 170.00 2.00 20.00 9.00 95.00 100.00 3.00 1.30 0.75 36.47 Kix G C 110.00 2.00 1.00 260.00 0.00 21.00 3.00 40.00 25.00 2.00 1.00 1.50 39.24 Life Q C 100.00 4.00 2.00 150.00 2.00 12.00 6.00 95.00 25.00 2.00 1.00 0.67 45.33 Lucky_Charms G C 110.00 2.00 1.00 180.00 0.00 12.00 12.00 55.00 25.00 2.00 1.00 1.00 26.73 Maypo A H 100.00 4.00 1.00 0.00 0.00 16.00 3.00 95.00 25.00 2.00 1.00 1.00 54.85 Muesli_Raisins,_Dates,_&_Almonds R C 150.00 4.00 3.00 95.00 3.00 16.00 11.00 170.00 25.00 3.00 1.00 1.00 37.14 Muesli_Raisins,_Peaches,_&_Pecans R C 150.00 4.00 3.00 150.00 3.00 16.00 11.00 170.00 25.00 3.00 1.00 1.00 34.14 Mueslix_Crispy_Blend K C 160.00 3.00 2.00 150.00 3.00 17.00 13.00 160.00 25.00 3.00 1.50 0.67 30.31 Multi-Grain_Cheerios G C 100.00 2.00 1.00 220.00 2.00 15.00 6.00 90.00 25.00 1.00 1.00 1.00 40.11 Nut&Honey_Crunch K C 120.00 2.00 1.00 190.00 0.00 15.00 9.00 40.00 25.00 2.00 1.00 0.67 29.92 Nutri-Grain_Almond-Raisin K C 140.00 3.00 2.00 220.00 3.00 21.00 7.00 130.00 25.00 3.00 1.33 0.67 40.69 Nutri-grain_Wheat K C 90.00 3.00 0.00 170.00 3.00 18.00 2.00 90.00 25.00 3.00 1.00 1.00 59.64 Oatmeal_Raisin_Crisp G C 130.00 3.00 2.00 170.00 1.50 13.50 10.00 120.00 25.00 3.00 1.25 0.50 30.45 Post_Nat._Raisin_Bran P C 120.00 3.00 1.00 200.00 6.00 11.00 14.00 260.00 25.00 3.00 1.33 0.67 37.84 Product_19 K C 100.00 3.00 0.00 320.00 1.00 20.00 3.00 45.00 100.00 3.00 1.00 1.00 41.50 Puffed_Rice Q C 50.00 1.00 0.00 0.00 0.00 13.00 0.00 15.00 0.00 3.00 0.50 1.00 60.76 Puffed_Wheat Q C 50.00 2.00 0.00 0.00 1.00 10.00 0.00 50.00 0.00 3.00 0.50 1.00 63.01 Quaker_Oat_Squares Q C 100.00 4.00 1.00 135.00 2.00 14.00 6.00 110.00 25.00 3.00 1.00 0.50 49.51 Raisin_Bran K C 120.00 3.00 1.00 210.00 5.00 14.00 12.00 240.00 25.00 2.00 1.33 0.75 39.26 Raisin_Nut_Bran G C 100.00 3.00 2.00 140.00 2.50 10.50 8.00 140.00 25.00 3.00 1.00 0.50 39.70 Raisin_Squares K C 90.00 2.00 0.00 0.00 2.00 15.00 6.00 110.00 25.00 3.00 1.00 0.50 55.33 Rice_Chex R C 110.00 1.00 0.00 240.00 0.00 23.00 2.00 30.00 25.00 1.00 1.00 1.13 42.00 Rice_Krispies K C 110.00 2.00 0.00 290.00 0.00 22.00 3.00 35.00 25.00 1.00 1.00 1.00 40.56 Shredded_Wheat N C 80.00 2.00 0.00 0.00 3.00 16.00 0.00 95.00 0.00 1.00 0.83 1.00 68.24 Shredded_Wheat_'n'Bran N C 90.00 3.00 0.00 0.00 4.00 19.00 0.00 140.00 0.00 1.00 1.00 0.67 74.47 Shredded_Wheat_spoon_size N C 90.00 3.00 0.00 0.00 3.00 20.00 0.00 120.00 0.00 1.00 1.00 0.67 72.80 Smacks K C 110.00 2.00 1.00 70.00 1.00 9.00 15.00 40.00 25.00 2.00 1.00 0.75 31.23 Special_K K C 110.00 6.00 0.00 230.00 1.00 16.00 3.00 55.00 25.00 1.00 1.00 1.00 53.13 Strawberry_Fruit_Wheats N C 90.00 2.00 0.00 15.00 3.00 15.00 5.00 90.00 25.00 2.00 1.00 1.00 59.36 Total_Corn_Flakes G C 110.00 2.00 1.00 200.00 0.00 21.00 3.00 35.00 100.00 3.00 1.00 1.00 38.84 Total_Raisin_Bran G C 140.00 3.00 1.00 190.00 4.00 15.00 14.00 230.00 100.00 3.00 1.50 1.00 28.59 Total_Whole_Grain G C 100.00 3.00 1.00 200.00 3.00 16.00 3.00 110.00 100.00 3.00 1.00 1.00 46.66 Triples G C 110.00 2.00 1.00 250.00 0.00 21.00 3.00 60.00 25.00 3.00 1.00 0.75 39.11 Trix G C 110.00 1.00 1.00 140.00 0.00 13.00 12.00 25.00 25.00 2.00 1.00 1.00 27.75 Wheat_Chex R C 100.00 3.00 1.00 230.00 3.00 17.00 3.00 115.00 25.00 1.00 1.00 0.67 49.79 Wheaties G C 100.00 3.00 1.00 200.00 3.00 17.00 3.00 110.00 25.00 1.00 1.00 1.00 51.59 Wheaties_Honey_Gold G C 110.00 2.00 1.00 200.00 1.00 16.00 8.00 60.00 25.00 1.00 1.00 0.75 36.19