Inthisassignmentyouwillbeworkingwithatime-seriesdatasetdepictingthechanging proportionofimmigrantpopulationfor26urbancentres in Canada.Theassignmentismeantto highlightthevalueofthe descriptivemeasuresina
geographic
context.Quiteoften,geographersareinterestedinlookingatchangingpatternsovertime,andthesemeasuresprovideawayofexploringthedatavaluesthatexhibitthesechanges.Thedatasetisprovided separately on OWL as an Excel spreadsheet. In this lab you
will
beworkingwith
ExcelandSPSS.In
this
assignment,
you
are
going
to
use
Excel
to
calculate:
Mean,
Standard
Deviation,
Coefficient
of
Variation,
Skewness,
and
Kurtosis
using
the
formulas
from
your
lecture
notes
and
textbook. Additionally, you will be using SPSS to verify your results.
Objectives 1. Learn to calculate descriptive statistics using Microsoft Excel and SPSS. 2. Analyze the descriptive statistics spatially and temporally. 3. Interpret statistical results in written responses. Background: All questions in this assignment are to be answered in your write-up. All material is to be word-processed and any required tables or figures must be appropriately labelled (title, axes, etc.) and referenced in the text of your write-up. Your mark on this assignment is based on your work, your answers, and your presentation of both. Reading Chapters 2 & 3 in your textbook, and reviewing the material covered in the Week 2 Lecture PowerPoint is recommended before attempting this assignment and questions. Descriptive Statistics In this assignment you will be working with a time-series data set depicting the changing proportion of immigrant population for 26 urban centres in Canada. The assignment is meant to highlight the value of the descriptive measures in a geographic context. Quite often, geographers are interested in looking at changing patterns over time, and these measures provide a way of exploring the data values that exhibit these changes. The data set is provided separately on OWL as an Excel spreadsheet. In this lab you will be working with Excel and SPSS. Assignment This assignment is going to introduce you to several valuable skills for working with data sets in Excel and SPSS. In particular, we will be making extensive use of Excel’s capability for inputting and calculating formulas. The cells in an Excel workbook can be filled with numbers, text, or formulas. Consider the following example: A B C D 1 Hours Worked Rate Pay 2 50 5.50 3 46 6.50 4 45 6.55 5 38 7.25 2 Here we are trying to calculate the Pay based on Hours Worked and the Rate. The power of the spreadsheet lies in the fact that we can input a formula to do this calculation once (cell C2) and then copy and paste (or drag) it down the column to calculate the values for each observation. Try this: · In Excel, copy and paste the content of the above table (from cells A1:C:5) into an empty Excel spreadsheet. · Type “=A2*B2” into cell C2 (without the quotation marks). When you hit enter/return (or click on the green check next to the formula bar) this cell should change to 275 – as the calculation replaces the formula. · Copy and paste the formula from C2 into cells C3:C5 (or you can click on Cell C2, and then grab the little square in the bottom right of the cell and drag the cell down to C5. This should apply that formula to the three lines below. Tips: · You don’t need to actually type A2 or B2, you can click on the cell(s) you want to include in the formula. So, you could just type “=” in cell C2, then click on cell A2, type “*” and then click on cell B2. · Each formula needs to begin with “=” and can use * for multiplication, / for division, + or – for arithmetic. · Use ^ (shift-6 on the keyboard) to raise something to a power (e.g. to square Pay in the above we would enter “=(A2*B2)^2”. · To take the square root of a formula, raise it to the power of 0.5 (^0.5) · We also must use parentheses () to ensure that a calculation takes place in the proper order. · To reference an absolute cell location (one that doesn’t change as you drag or copy/paste your formula), use the $ before the column and row (e.g., $A$2 locks that cell into the formula). This is useful for calculations where one item remains constant – otherwise when you copy and paste the formula it changes cell numbers automatically. · Go here for Microsoft’s help page for formulas, or use the Help menu. In this assignment, you are going to use Excel to calculate: Mean, Standard Deviation, Coefficient of Variation, Skewness, and Kurtosis using the formulas from your lecture notes and textbook. Additionally, you will be using SPSS to verify your results. You will note that there are some commonly used formulas and shortcuts – take advantage of this fact as you proceed through the assignment! Excel Instruction Steps: 1) Save the file Lab 2 Data 2210.xlsx to your computer. 2) Obtain the 2016 data for the 26 cities on the spreadsheet from the Statistics Canada website. Create a new data column (Column F, title it “2016”) and then enter the 2016 data from the column labeled [Proportion (%) – Immigrant population] that matches the cities in the spreadsheet provided. You will not enter data for all the cities on the website – just those cities listed on the Excel spreadsheet provided. 3) You are going to focus on the 2011 data values (for now). The first task is to calculate the Mean ( or x-bar) immigrant population proportion. To complete this you need to determine the number of observations and the sum of values. · In cell A29, input “Sum”. In cell E29, input the following: “=sum(E2:E27)”. This provides the sum of the data values for the 2011 proportion of foreign-born residents variable. · In cell E32, input the following: “=count(E2:E27). This value is “n”, the number of observations for the variable. · In cell E33, calculate the Mean () using a formula. The mean is the sum of the data values divided by the number of observations for the variable. · You can also calculate mean using an Excel formula by typing “=average” followed by the cell range in parentheses as a formula (e.g. “=average(E2:E27)”. 4) You now must prepare to calculate the Sample Standard Deviation (s). a. Label columns G, H, I, and J “(X – Xbar)”, “(X – Xbar)^2”, “(X – Xbar)^3”, and “(X – Xbar)^4” respectively. b. Insert a formula in cell G2, under “(X – Xbar)”, that will calculate that observation’s deviation from the mean. Use the $ symbol when entering the cell with the mean (cell F33) into your formula. This will look like this: “=E2-$E$33“. Ensure that your answer makes sense. The dollar signs keep the formula referencing cell E33 even as you move the formula to other cells in the E column in the next step. c. Click on G2 after you have entered the formula. You could copy and paste this formula into all the cells below it; however, also try hovering the cursor over the lower, right corner of G2. When a solid cross appears, click and drag down to the last cell for which you want to calculate this value (G27). You should be rewarded with a column full of deviations. d. Insert a formula in cell H2, under “(X – Xbar)^2”, that will calculate the square of the values in column E. Repeat the copy and paste procedure to calculate the squared deviations for every observation of the 2011 data. To have Excel square a value from a cell you’d use the formula “=L3^2”. That would yield a value of L3 squared in the cell where you’ve entered the formula. e. In order to help with later calculations, calculate the cubed deviations (for skewness) and deviations raised to the power of 4 (for kurtosis) in the remaining two columns (columns I and J). In step d. above you used ^2 to square a value, you can use ^3, ^4, etc. to raise a value to the third, fourth, etc. powers. In the skewness and kurtosis columns be sure to use the mean deviation value from column G for your formulas. Drag these down or copy/paste for each of the 26 cities. f. You are now going to sum up deviation calculations for the last three columns of data you just created, (X – Xbar)^2 to (X – Xbar)^4. Move to E29, where you originally calculated the sum for the 2011 data. Hit “ctrl-c” to copy the formula in that cell. Move to cell G29. Click and hold the left mouse button and drag it to the right, highlighting the cells in that row that correspond to columns G through J and release. Hit “ctrl-v” to paste the formula in those cells. You should be rewarded with the sum of each of those columns. g. In the cells below the one labelled “Mean”, you will notice the following labels: Standard Deviation, Coefficient of Variation, Skewness, and Kurtosis. Move to the empty cell beside the Standard Deviation under 2011 (E34). h. You have now created all the components you need to calculate the standard deviation for the 2011 Proportion of immigrants variable. Check the equation you will need (**Is this sample data or population data?**) from the lecture notes or textbook, and input the formula into this cell (E34). Don’t type in the terms of the equation, but refer instead to the cell addresses where the numbers have been calculated when you make your formula. Remember, you can take the square root of something by raising it to the power of 0.5 [or use the sqrt() function in Excel]. Also, if you widen the column, you will see more of the calculated value. 5) For this step, you will be calculating Coefficient of Variation (CV) for the 2011 data. Again, move to the blank cell adjacent to the appropriate label (E35). Refer to the proper formula from lecture and input it in this cell, referring to cell addresses rather than values when you create your formula. 6) Calculate Skewness and Kurtosis, again referring to the proper formulas and placing them in the appropriate cells (E36 and E37). Remember, no numbers – cell addresses only when creating your formula. The formulas you need for these were provided in lecture and are also in the textbook. 7) One of the advantages of working in the spreadsheet is that we can change data values, and all the formulas we have input will change accordingly. We are going to take advantage of this fact to quickly calculate our descriptive statistics for the three remaining years of data. a. Move to the cell A1. Click and hold the left mouse button and drag out to highlight everything in this worksheet. Copy this highlighted area. b. On the bottom of the Excel window you’ll see a + in a grey tab, click on this; a new blank worksheet should appear. Click on cell A1 in this new blank worksheet. When you “Paste” (or ctrl-v), the data and work from your original worksheet should be staring back out at you. c. Move to the column labelled “1991”. Click and hold the left mouse button and drag down to highlight the data in this column (B1:B27). Hit “ctrl-c” to make a copy. You are now going to replace the 2011 column of data with this one [make sure you’re in your newly created worksheet]. Move to the cell where the “2011” label is. Hit “ctrl-v” to paste the 1991 data over the 2011 data. With any luck, you should immediately notice that everything has been recalculated. Move to the bottom and check the values for each statistic which should hopefully be new. You can relabel this new worksheet “1991 results” by clicking on the worksheet label (Sheet1) and typing the new title. d. Move back to the worksheet labelled “base data” (little tab at the bottom) and begin the procedure again at a). This time, copy the original data to another new worksheet and transfer the values from the 1996 column to the 2011 column. Your result will then be the calculation of descriptive statistics for the 1996 data. Follow this same procedure for the 2001 and 2016 data. e. You should now have multiple worksheets: base data, 1991 results, 1996 results, 2001 results, and 2016 results. [Your 2011 results will be on your base data worksheet] 8) Your last task is to create a summary table which contains the five descriptive statistics as calculated for each different year. Pick an empty spot on the “base data” sheet and copy and paste your statistics into a properly labelled table like this: 1991 1996 2001 2011 2016 Mean Std. Dev. CV Skewness Kurtosis Caution: currently, all those calculated values you see on the screen are formulas. If you copy and paste them somewhere, they will lose their cell references and no longer work (you’ll see the message “#REF!”. Follow these instructions to copy the VALUES ONLY: · In “base data” sheet, copy the column of statistics you calculated, Mean through Kurtosis. NOW: move to the area where you are creating the summary table. Then right mouse and select “Paste Special”. A selection box should appear, and under the column of options click on the “Values” option. The values you calculated should be pasted and you now see that instead of formulas, the cells contain actual numbers. [you can also find “Paste Special” under the Edit menu]. · Repeat this procedure copying the statistics from the 1991 results, 1996 results, 2001 results, and 2016 results worksheets to the summary table you are creating. · You can increase or reduce the number of digits after the decimal by highlighting the cells and clicking on either the