Sept 24 2003 LAB 1 ECON 240A 1 L Phillips Orientation to Excel Exploratory Data Analysis I Orientation to Excel Help 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 display Sum 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 arrow to continue with the dialog box II Descriptive Statistics Functions Open XM02 01 in Excel from the Econ240A folder in classes Ch 2 The data should be in column 1 starting in row 2 Descriptive statistics Select 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 key Select 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 key Select 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 key Sept 24 2003 LAB 1 ECON 240A 2 L Phillips Orientation 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 key III Histogram of the monthly telephone bills of new subscribers Go 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 choices The histogram window dialog box should be open If you need help click on help and info about this dialog box comes up Select rows a2 a201 for data input For the output options Click New Worksheet Ply and give it a name like Bills Histogram Click chart output box and hit OK You 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 K W 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 K W also discuss this choice using Table 2 2 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 95 so 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 24 2003 LAB 1 ECON 240A 3 L Phillips Orientation to Excel Exploratory Data Analysis Select cell i3 go to the formula bar and type an go to insert menu and insert function select all for function category select min Select rows 2 201 of column1 hit enter key Use this same process steps for the max function B Letting K W or you make the choices First 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 limits Go 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 options Click 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 Again Sept 24 2003 LAB 1 ECON 240A 4 L Phillips Orientation 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 OK In 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 Plot This requires a Macro Data Analysis Plus from K W Go to Econ 240A folder in classes and open Excel file Stats Open XM02 01 in Excel from the Econ240A folder in classes Ch 2 This is the telephone bill data file used above Go to the Tools Menu and select data analysis plus Box Plot OK This output corresponds to the box plot in K W and The Excel Output on p 113 V Exercise Due in one week Develop the box plots for the seconds to wait for drive through window service in five competing fast food restaurants The file is Xm04 15 You can compare your results to Keller and Warrack on p 112 a Setting your tastes for food aside which restaurant has the fastest service b Which restaurant has the slowest service c Which of these five seems to have the most advertising on TV
View Full Document
Unlocking...