Homework 6

Note: Do NOT change columns names
Data: Over 14 Averages
10
16
16
18
16
16
18
12
16
14
13
13
18
14
14
16
12
12
13
12
12
18
18
12
16
18
16
12
13
13
16
16
5
10
12
16
13
13
16
18
16
12
16
18
14
16
16
16
12
14
16
21
13
13
16
11
18
12
14
12
13
13
12
16
14
13
6
13
21
12
16
12
9
9
16
9
12
13
18
12
13
9
12
14
13
18
13
18
13
13
13
16
12
14
16
18
18
12
12
16
13
12
12
12
16
16
10
20
18
16
0
16
21
18
12
12
16
16
10
13
6
14
16
16
11
16
16
16
0
12
11
13
13
13
14
12
10
1
13
21
13
16
12
12
6
13
16
16
18
16
12
12
18
16
16
13
12
12
13
16
16
13
11
16
12
13
5
13
12
12
13
16
16
14
13
16
18
12
16
12
14
12
12
20
12
16
16
16
16
18
12
13
13
18
12
12
13
13
12
14
13
18
20
21
10
14
12
16
16
16
18
12
21
14
16
16
18
18
18
13
12
16
12
13
14
12
12
14
16
13
20
14
16
16
12
14
18
18
14
18
12
18
12
14
14
12
16
14
18
12
8
14
14
11
14
16
16
16
13
16
9
18
14
12
12
16
16
12
13
12
16
14
13
14
12
20
13
12
12
18
12
13
12
14
12
18
16
12
11
13
12
14
18
18
20
16
18
16
12
12
18
11
16
14
16
12
12
20
16
16
16
16
16
16
13
9
5
12
14
12
16
12
12
12
16
12
12
16
16
13
18
16
16
10
14
13
13
16
14
12
16
18
16
16
21
12
16
14
12
21
8
12
12
16
16
18
13
16
12
18
12
14
16
14
14
16
12
13
16
16
12
6
18
14
16
14
16
14
21
1
12
20
12
12
16
18
12
14
11
12
13
12
12
16
14
12
12
18
5
12
16
12
12
21
16
12
18
20
13
16
12
13
14
16
5
12
16
16
16
16
12
9
12
16
20
13
16
16
16
16
14
12
16
12
13
14
13
16
9
12
18
18
16
16
18
12
12
18
16
13
13
12
12
14
13
18
21
18
16
16
18
16
14
18
16
16
18
16
16
13
12
18
18
16
12
12
16
12
13
12
18
13
16
13
16
12
16
4
12
13
13
8
18
16
16
12
13
13
14
18
Averages
Count: At least 14 years
Count: Less than 14 years
Econ 5, Project 6
Due Date: Feb 24th, by 7:00pm Pacific Time
You can submit your Excel spreadsheet AS MANY TIMES AS YOU WANT. You will see your score
each time. Your LAST SUBMISSION’s score will be your score for the project. Autograder will only
score the numeric parts of the assignment, the graphs will be graded by your TA after the due date so they
cannot be revised.
Overview
As an aspiring economist, it is important to know how to deal with large data sets. To start, we
will deal with a smaller one.
Improving educational attainment is a task of many researchers in economics and other fields. In
order to understand the distribution of education in the United States, you have been given a
sample of number of years of education completed for 35- to 39-year old people in the United
States in 2020. Your sample contains 500 data points in cells C3-C502.
To Receive Credit
General Instructions:
• Make sure your capitalization is correct for any non-numeric entries as the autograder is
case sensitive.
• Dataset sent to each student is different. Do not share your dataset with others.
Excel Instructions:
• The Excel spreadsheet you submit must have a title of the following form: Project6.xlsx.
• Make sure Sheet 1 in your Excel file includes all calculations, answers, and graphs. Only
Sheet 1 will be graded.
• Make sure Sheet 1 is formatted as specified in the instructions; autograder will only grade
entries that are in the correct cells.
• You MUST use Excel, NOT Google Sheets. Make sure you download the file as an Excel
spreadsheet. If you accidently open it in Google Sheets download it to your computer as
an Excel file, do NOT cut and paste to an Excel spreadsheet as you might mess up the
format and create errors that make it impossible for Autograder to read your file.
Graph Instructions:
• All graphs must also be submitted as a single PDF with titled Project6.pdf as instructed
below.
• All graphs MUST include the following:
o A title that describes the main point.
o A graph number as described in the instructions. The graph number must be at the
beginning of the graph title.
o Meaningful, readable labels for axes and categories.
o A legend when it necessary.
o The graph must be self-explanatory, i.e. someone who is not familiar with the
context and data could understand its main message.
Required Elements (all elements must be in Sheet 1 in your Excel file)
(1) In cells D3-D502, enter a 1 if at least 14 years of education are encoded in the cell to the
immediate left, and 0 otherwise. For example, if the value in C3 is 12, then the value in D3 needs
to be 0. For another example, if the value in C4 is 14, then the value in D4 needs to be 1. (Hint:
The IF function can help save you a lot of time if you learn how to use it properly.)
(2) In cell F4, calculate the number of people with at least 14 years of education. In cell G4,
calculate the number of people with less than 14 years of education.
(3) Graph 1: Make a pie graph that shows the information in cells F3-G4. Remember to have a
title. The legend needs to have two categories listed, “At least 14 years” and “Less then 14
years”.
(4) In cells E22, E42, E62, …, E482, and E502, calculate the average of 20 data points in column
C. (There are 25 averages you are calculating.) For example, E22 calculates the average of cells
C3-C22, and E42 calculates the average of cells C23-42.
(5) The numbers in cells C509-AA509 need to be the same as the 25 averages you calculated in
element 4. The order must be the same. For example, C509 must have the same number as E22,
and D509 must have the same number as E42.
(6) Graph 2: Using the averages in cells C509-AA509, produce a histogram with 5 bins on the
horizontal axis. Make sure to include a title and labels for each axis.
Leaderboard
•
•
•
•
•
•
•
•
When uploading the Excel spreadsheet to GradeScope, you will be prompted to provide a
name for the leaderboard. A leaderboard is a tool that allows you to monitor accuracy and
timing of your submission relative to others in the class.
You can pick your own leaderboard name, please make sure it is appropriate and
respectful, given that the leaderboard is visible to all students in the class. Use the same
leaderboard name for all submissions this quarter.
DO NOT use your real name as your leaderboard name. If you accidentally use your real
name as your leaderboard name, submit again, the original leaderboard entry will be
deleted.
The leaderboard will NOT affect your course grade at all. If you don’t want your
leaderboard name to be uniquely identified, you can type Optout Otter as your
leaderboard name.
DO NOT leave the leaderboard name blank, your submission will not be processed.
DO NOT share your leaderboard name with other students. A student’s leaderboard name
is known only to that particular student and course instructors.
Leaderboard ranks all submissions by the SUBMISSION TIME among the class
members. The earlier the submission, the higher the rank. Note though, the leaderboard
ONLY ranks those who got FULL SCORE on the autograder part of the assignment. For
example, if you and another student both got a full score, and you submitted earlier, your
leaderboard name will appear higher on the leaderboard than the other student’s. Please
notice that your leaderboard name would appear at the bottom of the leaderboard if your
submission scored less than FULL SCORE on the autograder part of the assignment.
After your submission, click on “Leaderboard” on the “Autograder Results page” to
check the ranking.
Submitting your project
•
•
•
•
•
You must include the two files described in the “To Receive Credit” section above. One
file needs to be submitted as an Excel spreadsheet, while the other a PDF. Sheet 1 must
be formatted as described above.
You must also submit all graphs in PDF form. The easiest way to do this is to copy the
Excel graphs into Word using COPY followed by PASTE IMAGE. (It may also be listed
as Paste Picture.) Once all graphs are in the word doc and nicely formatted and sized, you
can save the document as a PDF. We require that you submit as a PDF rather than a word
document because formatting is sometimes compromised in the transfer for word
documents, but PDFs always transfer exactly as you formatted it. This file must have a
title of the following format Project6.pdf.
All graphs need to be in BOTH on Sheet 1 of the excel file AND separately, in the PDF
file.
These two files must be uploaded to GradeScope before the project deadline.
Your submission must have NO hidden rows or columns.
Grading Rubric for Project 6 (each element must be completely correct to receive credit;
no partial credit given)
Required Element 1 is correct
Required Element 2 is correct
Required Element 3 is correct
Required Elements 4 & 5 are correct
Required Element 6 is correct
1 point
1 point
1 point
1 point
1 point
The following information is provided if you are interested in the data set, but this information is not
needed to complete this assignment:
1. If you are interested in the data set used for this assignment, the U.S. Census Bureau Current
Population Survey was used. The data set can be found at
https://www.census.gov/data/tables/2020/demo/educational-attainment/cps-detailed-tables.html.
2. We used Doctoral degree = 21 years; professional degree = 20 years; master’s degree = 18 years;
bachelor’s degree = 16 years; associate’s degree = 14 years; some college, no degree = 13 years
3. When there are multiple grades listed (e.g. 1st – 4th grade), we assume an approximately equal
number of people complete each grade for sampling purposes.