QuestionDescribingDataNumerically&Graphically ( PartA :Perform…DescribingDataNumerically&Graphically(PartA:Perform SummaryStatistics usingExcel1.Downloadthe”HealthyLifestyles”DatasetOpen the Excel dataset and save it as a new file2. Refer to page 2 of this handout for meaning of each label (lifestyle dimension) in the “HealthyLifestyle”dataset.3. DataArrangementandAnalysis:Delete the entirerows corresponding tothe 4 stateswith datamissing.Use Excel’s Data Analysis feature (or individual statistics)to calculate the mean, median, sample variance and sample standard deviation for the1st,3rdand 5thlifestyle dimensions (SMK, SED, ALC)separately.**Pleaserefer tosecond halfofpage2 ofthis handoutfor EXCELDataAnalysisinstruction**Report Pearson’s Coefficientof Skewnessfor the 5thlifestyle dimension (ALC)based onyouranswerin theabove 2) and formulaCase:HealthyLifestylesThe Centers for Disease Control and Prevention (CDC) in Atlanta, Georgia, is the governmentagencyresponsiblefor disease-relatedissuesin theUnitedStates. TheCDCcoordinates effortstocounteract outbreaks of diseases and funds a variety of medical and health research studies. TheCDCalso serves as acentral clearinghouse for health-relateddata.The CDC conducts the annual Behavioral Risk Factor Surveillance Survey. The survey measures awhole series of lifestyle characteristics that relate to health and longevity, such as smoking and useofseat belts. Thesurveycompiles data ona state-by-state basis. Notall states are surveyed.The data set from the 1990 Behavioral Risk Factor Surveillance Survey is on the dataset named”Healthy Lifestyles. All numbers are percentages, and asterisks indicate the missing data for thatstate.Variable LabelSMK Currentcigarettesmokers.WEI Overweight(top15 percentofpopulation, accordingtotheCDCheight-weightformula).SED Sedentary lifestyle (less than three 20-minute exercise sessions a week).ACT Noleisuretime activity (off thejob).ALC Binge drinking (five or more drinks on occasion, previous month).DWI Drinking and driving (after “too much” to drink, previous month).SEA Seat-beltuse (occasionally ornever).STATE U.S.state(alphanumeric).Your task is to prepare a summary of these data and generate descriptive measures to be put in areportissuedto majornewsorganizations, suchas theAssociatedPress,andwillappear inmajornewspapersaround theUnited States.Source: Used with permission of Peter G. Bryant and Marlene A. Smith, Practical Data Analysis:CaseStudiesin BusinessStatistics,Irwin,1995.Descriptive Statistics using ExcelWithExceldata fileopen, lookfor the”Data” tabfromthemenu baron top:SelectData-> Data Analysis-> Descriptive Statistics.Specify Input range- the column(s) that contain data ofinterest including thelabels.CheckLabels infirst rowandSummary Statisticsand clickOK.Theresultsaredisplayedinanew worksheetwithinthesame file.Rename outputworksheet asPartA_Result.Format all numerical results to 2 decimal places.[Format decimal pointsof data in reportinganswers]Highlight the relevant cell in the Excel sheet that contains number to be formatted, right-click andselectFormatCellsNumbers2decimalplaces.Continue to pp.3-4 of this handout for detailed instruction of Part B and Part C of ExcelAssignment1.PartB: Compute Meanfor Grouped DataDownload the”KW Home Sales” Dataset. Copy the dataset to a separate worksheetwithinyourExcelAssignment1 file created at the beginning ofPartA.Calculatingthe meansalespriceCalculate the range of each price class and enter the result in a separate column (next toexisting data columns) with variable name Rangein the 3rd row (i.e. same row as “pricerange”,”frequency” labels).Calculate the midpoint of each class (halfway between Min and Max of a specific class)andentertheresultinaseparatecolumn(nexttotheRangecolumncreatedfromlaststep)with variable name Midpointin the 3rd row (i.e. same row as “price range”, “frequency”labels).Multiply the Midpointof each class by its Frequency, record the value in a separatecolumn (next to the Midpointcolumn created from last step) with variable name Productin the 3rd row (i.e. same row as “price range”, “frequency” labels).Compute the Sum of all values under the Productcolumn, divide the result by thetotal number of houses. Write down your answer in an empty cell of the worksheet.Renamethisworksheet as “PartB_Result”.Part C: DrawHistogram using ExcelDownload the “HouseList” Dataset.CopythedatasettoaseparateworksheetwithinyourExcelAssignment1 file created at the beginning of Part A. You will draw a histogram to illustrate data for the columnof”Size” in thisdataset.Calculateandreporttheoptimalnumberofclasses(k)basedonthetotalnumberofhouses(n)in thisdataset. K is the smallest number such that 2k> n.Calculateandreportthe rangeof”Size” column(= Max-Min)Calculate and report class width– Divide the range of “Size” by k (found in step 1), ifnecessary,round up to a convenientinteger number.Determine class boundaries: The first (lowest) class should start at or below the minimumvalue of “Size” column; the last (highest) class should end at or beyond the maximum value of”Size” column. Enter upper boundaryof each of the k classes to a new column with thename”SizeBins”entered on top.Follow the following steps 1)-10) inExcel to createand formathistogramSelectData->DataAnalysis->Histogram ->OK.Specify theinput range,including the label(Size).Specify Bin Range, including thelabel (SizeBins).Checktheboxfor”Labels”.Check the box for “Chart Output”. The histogram will be displayed in a new worksheet-renamethe worksheet as “PartC_Result”.Rightclickon anyof the barsin thehistogram.SelectFormat Data Series -> Series Options->enter”0″ inthebox of Gapwidth.SelectFillvarycolorsbypoint->close.DeletetheMorerow(lastrow)fromtheBinsFrequencytablegeneratedbyExcel(inthesameworksheetwhere the histogramisdisplayed).Renamethetitle and sizeofthe histogramforbetter visualclarity.DescribingDataNumerically&Graphically(PartA:Perform SummaryStatistics usingExcel1.Downloadthe”HealthyLifestyles”DatasetOpen the Excel dataset and save it as a new file2. Refer to page 2 of this handout for meaning of each label (lifestyle dimension) in the “HealthyLifestyle”dataset.3. DataArrangementandAnalysis:Delete the entirerows corresponding tothe 4 stateswith datamissing.Use Excel’s Data Analysis feature (or individual statistics)to calculate the mean, median, sample variance and sample standard deviation for the1st,3rdand 5thlifestyle dimensions (SMK, SED, ALC)separately.**Pleaserefer tosecond halfofpage2 ofthis handoutfor EXCELDataAnalysisinstruction**Report Pearson’s Coefficientof Skewnessfor the 5thlifestyle dimension (ALC)based onyouranswerin theabove 2) and formulaCase:HealthyLifestylesThe Centers for Disease Control and Prevention (CDC) in Atlanta, Georgia, is the governmentagencyresponsiblefor disease-relatedissuesin theUnitedStates. TheCDCcoordinates effortstocounteract outbreaks of diseases and funds a variety of medical and health research studies. TheCDCalso serves as acentral clearinghouse for health-relateddata.The CDC conducts the annual Behavioral Risk Factor Surveillance Survey. The survey measures awhole series of lifestyle characteristics that relate to health and longevity, such as smoking and useofseat belts. Thesurveycompiles data ona state-by-state basis. Notall states are surveyed.The data set from the 1990 Behavioral Risk Factor Surveillance Survey is on the dataset named”Healthy Lifestyles. All numbers are percentages, and asterisks indicate the missing data for thatstate.Variable LabelSMK Currentcigarettesmokers.WEI Overweight(top15 percentofpopulation, accordingtotheCDCheight-weightformula).SED Sedentary lifestyle (less than three 20-minute exercise sessions a week).ACT Noleisuretime activity (off thejob).ALC Binge drinking (five or more drinks on occasion, previous month).DWI Drinking and driving (after “too much” to drink, previous month).SEA Seat-beltuse (occasionally ornever).STATE U.S.state(alphanumeric).Your task is to prepare a summary of these data and generate descriptive measures to be put in areportissuedto majornewsorganizations, suchas theAssociatedPress,andwillappear inmajornewspapersaround theUnited States.Source: Used with permission of Peter G. Bryant and Marlene A. Smith, Practical Data Analysis:CaseStudiesin BusinessStatistics,Irwin,1995.Descriptive Statistics using ExcelWithExceldata fileopen, lookfor the”Data” tabfromthemenu baron top:SelectData-> Data Analysis-> Descriptive Statistics.Specify Input range- the column(s) that contain data ofinterest including thelabels.CheckLabels infirst rowandSummary Statisticsand clickOK.Theresultsaredisplayedinanew worksheetwithinthesame file.Rename outputworksheet asPartA_Result.Format all numerical results to 2 decimal places.[Format decimal pointsof data in reportinganswers]Highlight the relevant cell in the Excel sheet that contains number to be formatted, right-click andselectFormatCellsNumbers2decimalplaces.Continue to pp.3-4 of this handout for detailed instruction of Part B and Part C of ExcelAssignment1.PartB: Compute Meanfor Grouped DataDownload the”KW Home Sales” Dataset. Copy the dataset to a separate worksheetwithinyourExcelAssignment1 file created at the beginning ofPartA.Calculatingthe meansalespriceCalculate the range of each price class and enter the result in a separate column (next toexisting data columns) with variable name Rangein the 3rd row (i.e. same row as “pricerange”,”frequency” labels).Calculate the midpoint of each class (halfway between Min and Max of a specific class)andentertheresultinaseparatecolumn(nexttotheRangecolumncreatedfromlaststep)with variable name Midpointin the 3rd row (i.e. same row as “price range”, “frequency”labels).Multiply the Midpointof each class by its Frequency, record the value in a separatecolumn (next to the Midpointcolumn created from last step) with variable name Productin the 3rd row (i.e. same row as “price range”, “frequency” labels).Compute the Sum of all values under the Productcolumn, divide the result by thetotal number of houses. Write down your answer in an empty cell of the worksheet.Renamethisworksheet as “PartB_Result”.Part C: DrawHistogram using ExcelDownload the “HouseList” Dataset.CopythedatasettoaseparateworksheetwithinyourExcelAssignment1 file created at the beginning of Part A. You will draw a histogram to illustrate data for the columnof”Size” in thisdataset.Calculateandreporttheoptimalnumberofclasses(k)basedonthetotalnumberofhouses(n)in thisdataset. K is the smallest number such that 2k> n.Calculateandreportthe rangeof”Size” column(= Max-Min)Calculate and report class width– Divide the range of “Size” by k (found in step 1), ifnecessary,round up to a convenientinteger number.Determine class boundaries: The first (lowest) class should start at or below the minimumvalue of “Size” column; the last (highest) class should end at or beyond the maximum value of”Size” column. Enter upper boundaryof each of the k classes to a new column with thename”SizeBins”entered on top.Follow the following steps 1)-10) inExcel to createand formathistogramSelectData->DataAnalysis->Histogram ->OK.Specify theinput range,including the label(Size).Specify Bin Range, including thelabel (SizeBins).Checktheboxfor”Labels”.Check the box for “Chart Output”. The histogram will be displayed in a new worksheet-renamethe worksheet as “PartC_Result”.Rightclickon anyof the barsin thehistogram.SelectFormat Data Series -> Series Options->enter”0″ inthebox of Gapwidth.SelectFillvarycolorsbypoint->close.DeletetheMorerow(lastrow)fromtheBinsFrequencytablegeneratedbyExcel(inthesameworksheetwhere the histogramisdisplayed).Renamethetitle and sizeofthe histogramforbetter visualclarity.HEALTHY LIFESTYLE DATASETHealthyLifestyles-datasetSTATE SMK WEI SED ACT ALC DWI SEAAlabama 22 24 60 34 10 2 39Alaska * * * * * * *Arizona 21 21 51 23 14 3 30Arkansas * * * * * * *California 20 21 54 24 17 3 13Colorado 21 16 45 19 17 3 17Connecticut 22 23 52 26 17 3 23Delaware 23 26 54 27 15 2 31D.C. 19 27 73 52 6 2 26Florida 24 24 55 32 13 2 20Georgia 24 20 62 37 9 2 23Hawaii 21 18 62 32 19 4 5Idaho 20 22 59 28 10 1 29Illinois 24 21 60 32 16 4 29Indiana 27 26 61 27 13 3 28Iowa 22 25 61 34 13 3 24Kansas * * * * * * *Kentucky 29 23 69 42 10 1 46Louisiana 25 24 59 29 16 4 24Maine 27 24 60 36 10 1 41Maryland 22 23 62 30 8 1 13Massachusetts 24 19 50 23 18 3 46Michigan 29 26 59 32 18 3 21Minnesota 21 21 55 25 21 3 24Mississippi 24 26 66 39 11 2 48Missouri 26 23 61 33 16 3 27Montana 19 20 52 18 19 4 28Nebraska 23 24 55 25 17 5 51NewHampshire 22 21 47 20 16 2 40NewMexico 22 22 51 28 15 3 12NewYork 23 20 63 33 12 1 20NorthCarolina 28 24 61 40 9 2 16NorthDakota 20 23 56 27 17 4 60Ohio 26 23 69 33 9 3 24Oklahoma 27 23 66 41 11 2 26Oregon 22 22 49 22 12 2 33Pennsylvania 24 25 55 27 18 3 26RhodeIsland 26 22 55 26 18 2 49SouthCarolina 25 25 70 34 10 2 14SouthDakota 21 23 57 29 16 4 57Tennessee 27 24 61 39 6 1 26Texas 23 22 54 29 19 5 15Utah 17 19 49 23 11 1 31Vermont 22 20 51 25 21 4 34Virginia 23 20 59 26 16 4 16Washington 22 19 52 20 18 3 16WestVirginia 27 25 68 40 9 1 46Wisconsin 25 23 54 25 27 6 29Wyoming * * * * * * *1 of 1KW HOME SALESKWHomesalesin4thquarterof2006Pricerange(class) Numberofhouses(frequency)$200,000–$225,000 195$225,000–$250,000 165$250,000–$275,000 95$275,000–$300,000 77$300,000–$350,000 86$350,000–$400,000 45$400,000–$500,000 44$500,000–$750,000 29$750,000–$1,000,000 3$1,000,000 -$1, 500,000 2Total 741HOUSE TYPE LISTHouseType SizeTownhouse 515Apartment 518Apartment 720Apartment 720Apartment 500Apartment 840Apartment 612Apartment 1050Apartment 1130Apartment 1010IndividualHouse 1428Apartment 1014Apartment 1050Apartment 742Apartment 609Apartment 749Apartment 713Apartment 1293Townhouse 1150Apartment 831IndividualHouse 1104Townhouse 1105Apartment 550Apartment 1057Townhouse 1200Apartment 880IndividualHouse 1560Townhouse 1379Townhouse 1600Apartment 750MathStatistics and ProbabilitySTAT 72105Share Question
