## Introduction to excel: basic operations
- Navigate this page:
- Table 1: Wage Expenditure and Performance of Premier League Football Clubs (Season 1997/98)
- Points
- Descriptive Measure Function
- B2:B21 and C2:C21
To enter the above data in an Excel worksheet, activate the cell (e.g. A1) using the mouse. Now type
Importing Data from Other File types
If, on the other hand, the data already existed in electronic form - either in another package such as Microsoft Word, database, spreadsheet in another statistical program or a document on an Internet site – it is possible to import the data directly into an Excel worksheet. For example, suppose the data in Table 1 was available in a text file (a text file is a file which contains only text and the columns of data are usually separated by either a space, comma or tab). The file name is Premier.txt.
To import the file into an Excel workbook double-click on Premier.txt. You will now be confronted with the Text Import Wizard. There are three steps. Step 1: Describe how the columns of data are separated. At this stage you can also decide whether to import all or just a sub-sample of the file. Click Next.
As a precursor to undertaking regression analysis an initial visual inspection of the data can be useful in determining the shape of the relationship between the variables (i.e. linear or non-linear) and in determining the presence of outliers and influential (excluded) variables. Charts can either be inserted as an embedded chart object (i.e. a chart in the current worksheet) or as a chart sheet (i.e. a separate sheet). In this section I will demonstrate how to create a scatterplot in Excel, again using the workbook Premier.xls.
Firstly open the file Premier.xls and highlight the columns Wages and Totpoints. Click Insert/Chart (alternatively, click on the chart wizard button on the toolbar). This opens up the chart wizard.
If the chart is embedded you can change its location by cutting and pasting, or by clicking a blank spot within the chart moving the chart to a different location. You can change its size by moving the mouse pointer to the bottom right hand side of the chart dragging the pointer down until the desired size is achieved. Finally, if you want to edit any individual component of the chart (e.g. plot symbols, axes scale etc) move the mouse pointer and right-click the component you want to change. This brings up a drop down menu from where the necessary alterations can be made.
5. Data Description Another useful preliminary tool is to calculate various summary measures or descriptive statistics. Typically these statistics can be classified into those that measure centre (mean, median and mode), those that measure spread (variance, standard deviation and range) and those that measure shape (skewness and kurtosis).
As an example, to calculate the mean number of points for the file Premier.xls go to cell B22 and click on the function button in the toolbar (f Descriptive Measure Function
Mean MEAN(data range) Median MEDIAN(data range) Mode MODE(data range)
Variance VAR(data range) Standard deviation STDEV(data range) Maximum value* MAX(data range) Minimum value* MIN(data range)
Skewness SKEW(data range) Kurtosis KURT(data range)
*The range is generated by subtracting the minimum value from the maximum value.
All of these summary measures can be calculated simultaneously (and inputted as a new worksheet). Click We can also look at the variables together. For example, we may want to determine how correlated the two variables are. The scatterplot we constructed in Section 5 suggests that a strong positive relationship exists but how strong is the relationship? To calculate the (Pearson) correlation locate an empty cell below the data and the various summary measures calculated above. Click on the function button on the toolbar and locate the function
6. Bivariate (Simple) Linear Regression
When we plotted the two variables in our scatterplot the values did not fall exactly in a straight line. In regression analysis we wish to find the line that best estimates the relationship between y (the dependent or response variable) and x (the explanatory or independent variable). The plotted line is called the fitted regression and the equation of the line is known as the regression equation. Plotting the Regression Line
Return to the scatterplot you constructed in Section 4. To add a regression line right-click any of the data points in the graph and click
A table of regression statistics is much more useful, particularly for hypothesis testing and other diagnostic purposes. To construct a table of regression statistics we must first of all make sure that the Analysis ToolPak is installed. If it is not, then: Click
Click
7. Data Transformations Sometimes you may want to transform one variable in another form. For example, if the relationship between two variables is exponential then undertaking a logarithmic transformation of the variables will make the relationship linear (see Lecture 6) or reduce mis-specification problems (Lecture 7) and heteroskedasticity (Lecture 8).
To transform Wages into (natural) logarithmic form:
In the first cell of a new column (D) write the variable name Other Useful Transformations
Exponential transformation (e Reciprocal (1/x) Variable raised to a particular power: Cross-Products of Variables Share with your friends: |