2024 485 Lab 2 - Questions
pdf
keyboard_arrow_up
School
University of Calgary *
*We aren’t endorsed by this school
Course
485
Subject
Geography
Date
Apr 3, 2024
Type
Pages
10
Uploaded by KidWhale4225
GEOG 485 – Winter 2024: Lab Assignment #2
Descriptive Statistics
Total: 85 Marks (5% of Final Grade)
Due: Start of Lab 3 (week of 13 Feb, 2024)
Objectives
1. Learn to calculate descriptive statistics with Microsoft Excel using arithmetic formulas
and built-in functions.
2. Analyze and evaluate descriptive statistics.
Assignment
This assignment is going to introduce you to valuable skills for working with datasets in Excel.
You will be making use of Excel’s capability for inputting and calculating formulas. We will
explore three methods of calculating descriptive statistics:
1. Pen, paper, and calculator
2. Typing in arithmetic operations in Excel
3. Using built-in Excel methods
Deliverables
All questions in this assignment are to be answered in a write-up you’ll be handing in to your TA.
You will also be submitting two Excel spreadsheets. Any tables or figures you include must be
appropriately labelled and/or referenced in the text of your write-up. If you use any references,
include a reference list at the end of your document. Your grade in this assignment is based on
your process, your answers, and your
presentation/organization
of both.
Revisiting Chapters 2
and 3 in the textbook is recommended
before attempting this assignment and the questions in it.
Deliverables are as follows:
-
word-processed document (PDF or word doc) including question answers, tables, and scanned
written work
-
Excel documents including all work performed
Part 1: Pen and Paper Warm-Up (15 Marks)
(scan and add it to your written document)
In this part, we are going to be working with fictional data from the following course: ‘GEOG
999: The Geography of Boredom’. There are 10 students registered in the course. For their
first midterm, the students are required to sit and observe paint drying on a wall for as long as
possible. The professor recorded the data in the following table:
Table 1: Number of hours each GEOG 999 student spent watching paint dry.
Student Name
Number of Hours Spent Watching Paint Dry
Michael
8
Sean
2
Ashley
5
Juan
7
Jennifer
23
Hannah
12
Roger
17
David
4
Charlene
11
Tyler
0
Question 1:
Using
pen/pencil and paper
calculate the following statistics for this
population
data from GEOG 999: The Geography of Boredom (follow the formulas as presented in lecture):
∙
Mean
(4 marks)
∙
Median
(3 marks)
∙
Standard Deviation
(5 marks)
∙
Coefficient of Variation
(3 marks)
Show the formulas you use, your calculations, and your steps in full
.
Be organized
. Submit
this either in hard copy or digital photo/scan, as applicable.
Part 2: Arithmetic Formulas in Excel (20 Marks)
Start a new Excel document and name it “LastName_Lab2.xlsx”. For this part,
you are not
allowed
to use the built-in Excel functions
(with two exceptions described below). You must
input arithmetic formulas to get your results.
To begin, let’s cover some Excel basics:
∙
Each formula needs to begin with ‘=’
∙
Addition and subtraction can be done using ‘+’ and ‘-‘
∙
Multiplication and division can be done using ‘*’ and ‘/’
∙
To raise a number to a power, use ‘^’
∙
A square root can be taken using ‘^0.5’
∙
You can use parentheses ‘()’ to ensure the order of operations happens in the way
you would like it to
Some examples of basic arithmetic work in Excel are shown here:
A
B
C
1
1
1
=A1+B1
2
5
2
=A2-B2
3
4
4
=A3*B3
4
100
10
=A4/B4
5
3
=A5^2
6
25
=A6^0.5
7
2
4
=(A7+B7)^2
8
8
4
=((A8/B8)-(B8/A8))/(B8^0.5)
Excel will calculate the formula
exactly as you input it.
The formulas above will produce the following
results:
C1
2
C2
3
C3
16
C4
10
C5
9
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
C6
5
C7
36 (this would be 18 if there were no parentheses)
C8
0.75
Spend a little time practicing with arithmetic in Excel until you are comfortable with the basic operations.
Here are some more
helpful tips:
∙
To reference an
absolute
cell location, use ‘$’ 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.
∙
To calculate the sum of multiple cells, you may use =SUM(FirstCell:LastCell).
∙
To
count how many values there are in a series of cells (i.e., population/sample size), you
may use =COUNT(FirstCell:LastCell).
∙
The two tips above are the exceptions to the ‘no built-in function’ rule in this part.
∙
Save your work regularly
∙
Keep your spreadsheet organized
∙
If you accidentally start changing the formula or values of a cell, hit escape and the cell will
return to its last version
∙
If you make a mistake, Ctrl+Z is a shortcut to undo the command/operation
Now that we’ve covered the basics, let’s try a simple exercise. Follow the instructions below
and answer the question at the end.
Step 1: Copy the following table into a new Excel spreadsheet (except for the ABC, 1-8):
A
B
C
1
7
2
=$B$1*A1
2
4
3
3
4
9
5
20
6
1
7
3
8
=SUM(A1:A7)
Step 2: If you copied and pasted successfully, your spreadsheet should now look like this:
A
B
C
1
7
2
14
2
4
3
3
4
9
5
20
6
1
7
3
8
47
Step 3: If you click on cells C1 and A8, you will see that the formulas are still displayed in the
text prompt at the top of the Excel page. Now, click on cell C1. At the bottom right of the cell,
you will notice a very small square. Hover your cursor over the square until your mouse pointer
turns into a black ‘+’. When you have the black ‘+’, click and hold your left mouse button and
drag the cursor
down to cell C8. Let go of the left mouse button. If you were successful, your
spreadsheet should now look like this:
A
B
C
1
7
2
14
2
4
8
3
3
6
4
9
18
5
20
40
6
1
2
7
3
6
8
47
94
Step 4: This drag-and-drop method is the best way to rapidly apply a formula to multiple cells in
Excel and can save a lot of time. However, Excel makes certain assumptions when you use this
drag-and-drop method. We will now explore one of those assumptions.
Delete the contents of cells C2 to C8 so that your spreadsheet looks like the spreadsheet in Step 2.
Next, select cell C1 and delete the two ‘$’ so that the formula is =B1*A1. Perform the same drag
and drop method described in Step 3 to apply the formula to cells C1 to C8. Your spreadsheet will
now look like this:
A
B
C
1
7
2
14
2
4
0
3
3
0
4
9
0
5
20
0
6
1
0
7
3
0
8
47
0
Question 1:
Explain what has changed between the spreadsheets at the end of Step 3 and at the
end of Step 4. Why do cells C2 to C8 all have non-zero values in Step 3 but all have a value of
zero in Step 4? What could you add to cells B2 to B8 to make the formula in Step 4 (=B1*A1)
produce the same results as the formula applied in Step 3 (=$B$1*A1)?
(
5 marks
-
you do not
need to submit your Excel work for this question
)
Question 2: We’re now moving on to calculating descriptive statistics. Follow the
instructions below.
Copy the ‘GEOG 999: The Geography of Boredom’ (Table 1) into your Excel document. Using
arithmetic formulas (remember,
no built-in functions except COUNT and SUM)
, calculate the
following descriptive statistics for this dataset:
∙
Sum (total number of hours spent watching paint dry) (Hint: see tips above, you are
not allowed to just type in 89)
∙
Population Size (Hint: see tips above, you are not allowed to just type in 10)
∙
Mean
∙
Variance
∙
Standard Deviation
∙
Coefficient of Variation
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
∙
Skewness
∙
Kurtosis
Your Excel spreadsheet should look like this:
A
B
1
Student Name
Number of Hours Spent Watching Paint Dry
2
Michael
8
3
Sean
2
4
Ashley
5
5
Juan
7
6
Jennifer
23
7
Hannah
12
8
Roger
17
9
David
4
10
Charlene
11
11
Tyler
0
12
13
Descriptive Statistic
Value
14
Sum
?
15
Population Size
?
16
Mean
?
17
Variance
?
18
Standard Deviation
?
19
Coefficient of Variation
?
20
Skewness
?
21
Kurtosis
?
Again, your task here is to create arithmetic formulas for cells B14 to B21 that calculate the
corresponding descriptive statistics. In your formulas, you
can
reference values you have already
calculated (e.g., you can reference cell B16 for the mean instead of recalculating it for every
formula). Use the formulas as presented in your textbook/lecture slides.
This worksheet must be included in your deliverables for this lab
(15 marks)
.
Part 3: Canadian Population Change – Built-in Excel Functions (35 Marks)
In this part, you
will be working with a time-series dataset of the changing proportion of foreign-born populations
in 28 Canadian cities from 1991, 1996, and 2001. Your goal is to use these data as a sample to
estimate how foreign-born populations across Canada changed from 1991 to 2001. For this part,
you will be exploring Excel’s built-in functions for calculating descriptive statistics. All of
Excel’s built-in functions are cataloged here:
https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-
63f26a86c0eb?ui=en-US&rs=en-US&ad=US
Open the Geog485-Lab2-Data.xlsx spreadsheet from D2L. Copy and paste this dataset into a
new worksheet in your “LastName_Lab2.xlsx” document. Name the new sheet “Part 3”. Your
first task in this part is to find the appropriate built-in Excel function to calculate the values for
the 4 descriptive statistics requested in the spreadsheet. Here are a few things to keep in mind:
∙
All functions, like formulas, must begin with ‘=’
∙
Are your descriptive statistics describing a sample or a population? (this will affect which
function you select)
When you are done, your spreadsheet should look like this:
A
B
C
D
…
…
…
…
…
33
1991
1996
2001
34
Number of Observations
28
28
28
35
Mean
14.26
14.53
14.73
36
Standard Deviation
9.180028
9.937809
10.40797
37
Skewness
0.413051
0.764788
0.914337
38
Kurtosis
0.10799
0.864757
1.13353
39
Coefficient of Variation (%)
64.38897
68.38502
70.66516
The values presented in the spreadsheet above are the
correct answers
.
If you do not have
these values in your spreadsheet, then you have used the wrong functions (there may be small
rounding differences in your values, but they should be
very
similar). You must use functions for
this part and not simply copy and paste the answers.
This worksheet must be included in your deliverables for this lab
(8 marks)
.
Questions:
1. The mean and standard deviation are
absolute descriptive measures
. This means that
their values are a direct function of the magnitude of the data. For the 1991-2001 time
series data, briefly explain how these two measures have changed over time for the
proportion of foreign-born residents and speculate on the reasons for these changes.
(4
marks)
2. How does the increasing skewness over time impact the data? What does this increasing
skewness tell us about where on the distribution the percentage of foreign-born residents
is increasing the most?
(3 marks)
3. Which two cities had the highest and lowest percentage of foreign-born residents in all
three years observed? What are the individual deviations of these two cities from the
mean? Provide a hypothesis for why these two particular cities might be at the extreme
ends of the distribution. Write your hypothesis in such a way that it could form the basis
of a more detailed research project.
(6 marks)
4. Discuss the relationship between data visualizations (visual tools), which we worked with
in Lab 1, and the descriptive measures (numbers) explored in this lab. Which do you
think is the best way to present data, or does it depend on the situation? How can visual
tools and descriptive statistics complement one another?
(6 marks)
5. The goal of this study was to get an estimate of the total percentage of the Canadian
population that is foreign-born. We’ve used a sample of cities to accomplish this goal and
have come up with a number of 14.73% (for 2001). Based on this sampling method, do
you think it’s correct to make this statement: “
14.73% of the 2001 Canadian population
was foreign-born
.”? In your answer, address issues of spatial aggregation, sampling bias,
sample size, and false precision.
(8 marks)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Part 4: Describing Demographic data from External Data Set – (15 Marks)
Re-create
the table in Part 3 and populate it using external demographic data of your choice
Find number of observations, mean, standard deviation, skewness, kurtosis and coefficient of
variation (CV) for at least two time periods (do not need three like in part 3) of a variable.
Can be any quantitative demographic data from any region (doesn't have to be from a gov). Try
to use your own unique data, different from other students. You can use any appropriate
demographic variable, it does not need to be population change, as long as it is quantitative and
appropriate for this type of descriptive summary.
Add table to your word/pdf document, be sure to cite your data source, include this work in your
Excel document, and clearly label data
---------------------------------------------End of Lab 2----------------------------------------------
Good luck! :)