I. Orientation to Excel“System Info”II. Descriptive Statistics FunctionsIII. Histogram of the monthly telephone bills of new subscribersIV. Box and Whisker PlotGo to the “Tools” Menu and select “data analysis plus”: “Box Plot”, OKV. Stem and Leaf DiagramGo to the “Tools” Menu and select: “stem and leaf display”: OKSept. 28, 2005 LAB #1 ECON 240A-1 L. PhillipsOrientation to Excel; Exploratory Data Analysis I. Orientation to ExcelHelp Menu: “About Microsoft Excel”“System Info”Help Menu: “Contents and Index”“Key Information”: “If you are new to spreadsheets”“How to get started with MS Excel”“Information about what you can do with MS Excel”Help menu: “Index” Count function: type in “count” in Index window and read displaySum function: type in “sum” and then select “sum worksheet function”Mean function: type in “mean”, display goes to “average”Histogram analysis tool: type in “histogram”, click the arrowto continue with the dialog boxII. Descriptive Statistics FunctionsOpen XM02-04 in Excel from the Econ240A folder in classes, Ch. 2The data should be in column 1, starting in row 2Descriptive statisticsSelect cell e2, and type in “count”Select cell e3, go to the formula bar and type an “=”, go to insert menu and insert “function”, select “count”Select rows 2-201 of column1, hit enter keySelect cell f2 and type in “sum”Select cell f3, go to the formula bar and type an “=”, go to insert menu and insert “function”, select “sum”Select rows 2-201 of column1, hit “enter” keySelect cell g2, and type in “mean”Select cell g3, go to the formula bar and type an “=”, select cell f3, type a division “/”, select cell e3, hit “enter” keySept. 28, 2005 LAB #1 ECON 240A-2 L. PhillipsOrientation to Excel; Exploratory Data Analysis Select cell h2, and type in “average”Select cell h3, go to the formula bar and type an “=”, go to insert menu and insert “function”, select “average”Select rows 2-201 of column1, hit enter keyIII. Histogram of the monthly telephone bills of new subscribersGo to the “Tools” Menu and select “data analysis”: “histogram”, click OK(if data analysis is not in the tools menu, choose “add-ins” in the tools menu and check the box for “Analysis ToolPak” . If “Analysis ToolPak” is not on this list it needs to be installed.A. Letting Excel make the choicesThe histogram window (dialog box) should be open. If you need help, click on“help” and info about this dialog box comes upSelect rows a2-a201 for data inputFor the output optionsClick “New Worksheet Ply” and give it a name like “Bills Histogram” Click “chart output” box and hit OKYou should get a sheet with 15 bins beginning at zero and the frequency, i.e. number of observations in each bin, adding to 200, and the histogram chart. This may be satisfactory for a quick and dirty examination. If you want to add a little value you can tidy up the bins. As Keller explain in Ch. 2, you can choose an approximate bin width by taking the difference between the largest bill and the smallest bill and dividing by the number of classes. Keller also discusses this choice, using Table 2.6. For a number of observations of 50-200, they recommend 7-9 classes and use 8. So,Approx. class width = (maximum bill – minimum bill)/ # classes= ($119.63 – 0)/8 = $14.95so choose 15 for the class width You can find the minimum and maximum by eyeball, or use the min and max functions, for example:Select cell i2, and type in “minimum”Sept. 28, 2005 LAB #1 ECON 240A-3 L. PhillipsOrientation to Excel; Exploratory Data Analysis Select cell i3, go to the formula bar and type an “=”, go to insert menu andinsert “function”, select “all” for function category, select “min”Select rows 2-201 of column1, hit enter keyUse this same process (steps) for the max functionB. Letting Keller, or you, make the choicesFirst, on the data sheet next to column A, type in “Bin” in cell b1. Then in cell b2 type in 15, the upper limit of the first bin. Then in cell b3 type in 30, the upper limit for bin two. Then drag cells b2 and b3 down through b9 to get all 8 bin or class limitsGo to the “Tools” Menu and select “data analysis”: “histogram”, click OK With the histogram window open, select the input data range, a2-a201, as before. For the bin range, select b2-b9.For the output optionsClick “New Worksheet Ply” and give it a name like “Bills Histogram #2” Click “chart output” box and hit OK On the resulting output sheet you can click on bin “more” and its frequency and backspace to remove. Also selecting the blue box and moving it up a row will remove this bin from the plot. Click on the legend “frequency” and from the “edit” menu, select “clear” “all” to remove. Select plot area, go to “format” menu and choose selected plot area and in the box under area select “none” to remove gray color.Click on Histogram and type in “of 200 Telephone Bills” to change the title.In general, you can experiment by selecting things to change them. For example, select “bin” and backspace to remove and type in “ Monthly Bill in $”. So you can tailor your graphic displays to suit yourself but remember the guidelines for taste from Ch. 3 of K&W, with an emphasis on the “Art” of graphical presentations.IV. Descriptive Statistics AgainSept. 28, 2005 LAB #1 ECON 240A-4 L. PhillipsOrientation to Excel; Exploratory Data Analysis In the discussion above, to illustrate the use of “insert function”, we produced count, sum, average, minimum, maximum, etc. step by step. There is a quicker way, which provides some additional info we will discuss in the future, and the standard deviation, discussed in Lecture One as a measure of dispersion.Go to the “Tools” Menu and select “data analysis”: “Descriptive Statistics”, OKIn the dialog box, provide the input range a2-a201, and under output options, select “new worksheet ply” with a name like “Bills Descriptive”, and select descriptive statistics, and hit OK. A time saver.IV. Box and Whisker PlotThis requires a Macro, Data Analysis Plus, from KellerGo to Econ 240A folder in classes and open Excel file “Stats”Open XM02-04 in Excel from the Econ240A folder in classes, Ch. 2This is the telephone bill data file, used above.Go to the “Tools” Menu and select “data analysis
View Full Document
Unlocking...