Excel Lab #4 – Using Excel to Analyze Data for a Research Paper
Overview:
You have now practiced the basic skills for using Excel – entering and formatting data, creating graphs, and computing with formulas. The new goal is for you to use these skills in the process of analyzing and presenting information in a brief research paper.
You will be analyzing data obtained from the U.S. government’s Statistical Abstract of the United States. This contains a considerable amount of demographic information (age, ethnicity, religion, etc.), for which certain ideas from statistics are helpful. Consequently, this lab includes instructions for basic statistics operations like computing means, medians, modes, and histograms.
Part I - Analyzing Data from the Statistical Abstract
1. Open Internet Explorer and go to http://www.census.gov/prod/www/statistical-abstract-02.html
2. Look down the column for the 2002 abstract, and click on the link for Section 8: “State and Local Government Finances and Employment.” Since the file is a PDF file, your computer should launch Adobe Acrobat to show it. Scroll down to page 4, which starts with the heading “Government Current Receipts and Expenditures by Type…”
3. Look over the data in the two tables on this page – it contains the information you hear about “deficit spending” and the “federal budget.” For practice, I’m going to have you focus on the second row of data, labeled “Personal tax and nontax receipts.”
4. Since the file you are reading is a PDF file, you can’t copy and paste the information into Excel. When you work on your own, you will have to retype the information yourself. For this example, I have put it into an Excel table. Download it from the class webpage, or go to
http://flightline.highline.edu/escott/Courses/95webw04/95Excel/95Lab4.xls
Notice that I reorganized the rows and columns so the years go down the first column like in all of our previous examples – this will make it easier to graph.
5. I first want you to practice with the Excel formulas for computing statistics. Starting in cell A9, type “Number” (without the quotes), and then continue down column A by typing the words “Min,” “Mean,” “Median,” “Mode,” and “Max” in that order, one word per cell.
6. In cell B9, write the following formula exactly as written, then press enter.
=Count(B4:B7)
7. The following formulas need to be typed in cells B10 through B14:
=Min(B4:B7)
=Average(B4:B7)
=Median(B4:B7)
=Mode(B4:B7)
=Max(B4:B7)
Tip: Instead of typing B4:B7 each time, you can just select the cells with your mouse.
8. After you’ve entered all the formulas, the first part of your spreadsheet should look like:
A / B1 / (From the 2002 Statistica
2 / Year / Personal Tax
3
4 / 1998 / 1070.4
5 / 1999 / 1159.1
6 / 2000 / 1286.4
7 / 2001 / 1292.1
8
9 / Number / 4
10 / Min / 1070.4
11 / Mean / 1202
12 / Median / 1222.75
13 / Mode / #N/A
14 / Max / 1292.1
The “#N/A” symbol in the mode row indicates that there is no single value that is repeated in the list, so there is no mode.
9. Copy these formulas to the rest of columns C, D, E, and F. Check to make sure you get 681.3 in cell D10 and 3000.7 in cell F14.
10. Use words to explain the meaning of each number in cells C9 through C14. Write out the dollar amounts with all the necessary zeros.
· Number:
· Minimum:
· Mean:
· Median:
· Maximum:
11. Now create two graphs. The first one will be a line graph comparing revenue from personal taxes to revenue from corporate taxes over each of the four years listed. (So years should be along the horizontal axis.) The second graph will be a pie graph that shows the breakdown of the government’s revenue for a specific year. Make sure the labels on the pie graph show the percentages!!
12. Suppose the government’s 2002 revenue was $2,843,000,000,000. Use either proportions or the percentages from your pie chart to estimate the amount of revenue coming from each of the different sources. Then right-click on the gray “8” on the left of your screen and choose “Insert.” Type 2002 in cell A8, and then put your calculated numbers in the appropriate places across this row.
13. Type your name in cell A16, and print out your data table and graphs. On the front or back side of the page with the data table (wherever there is enough space), write down the answers you gave in step 10 above. Write your name on your graphs (if separate), and turn them in.
Part II – Analyzing a topic of interest to you
Your assignment for this lab is to write a small research paper in which you use the each of the skills/ideas we’ve covered this quarter to analyze data obtained from the Statistical Abstract. I’m assuming you have not been asked to do this before, so I will try to provide a fair amount of guidance and feedback as you work towards the final product. You are responsible for pushing yourself to find ways of using the various skills/ideas meaningfully in the process of telling me about what you discovered in your data. To be successful, it is CRITICAL that you ask questions AND complete the stated tasks by the dates I present in this and subsequent handouts!!
1. For Friday, January 30th:
a) Find a set of data from the Statistical Abstract that you would like to analyze. Your data needs to meet the following conditions.
· It should be of some interest to you (this will make it easier to explain the data and to focus on it intently)
· It must contain at least 15 pieces of data (NOT counting year or category labels).
· Your data can be repeated measurements of one or two things over many years OR measurements of several different things that fall in the same category (e.g. the numbers of people who participated in several different types of activities) OR a combination of both (like we did in the example)
· A pie, bar, or line graph of your data truly can help others understand your data.
· You can use percentages/proportions with your data to predict results in new situations OR you can use percentage change/average rates of change to analyze trends.
Tip: If you are having a hard time finding such data, and can’t get help from me, ASK A LIBRARIAN!! This is not “cheating” – they are there to help you do this kind of research. The most important thing for them is that you bring along these instructions and have some idea of what you’d like to study.
b) Print out two copies of the page or two of the Statistical Abstract containing your data. Keep one copy for yourself, and turn the other one in to me.
2. For Monday, February 2nd:
a) Create an Excel spreadsheet of your data table.
b) Write down responses to the following questions:
i) What are the most interesting things about your data? (What do you want other people to notice or understand?)
ii) What kind of graph(s) do you plan to create, what data will you graph, and what point do you want your graph to make?
iii) Which analysis will be most helpful – using percents and proportions to predict results in new situations or using measures of change to more accurately describe trends?
iv) Does it make sense to calculate things like the mean, median, or mode, and could a histogram help you in some way?
Note: This does not have to be written up nicely – you will be using it as the basis for discussion with Erik and your classmates on Monday.
3. For Wednesday, February 4th:
a) Create an outline of your paper by deciding what you will write for each part of the “Write-up format” given at the end of this handout. Determine what you will need to use Excel for.
b) Have your Excel data and/or start of your write-up on disk and bring it to class.
4. For Friday, February 6th:
Write a rough draft of your paper using the outline from Wednesday. Bring a copy of the paper, graphs, and data table so you can get feedback on adjustments that need to be made.
5. For Monday, February 9th:
Your final draft of your paper is due.
Write-up format:
1. Introduction (Limit this to a fairly brief paragraph – you will expand on the ideas later.)
a) Get the reader interested in what you researched.
b) Describe what you chose to investigate.
c) Summarize the main point(s) revealed by your analysis.
2. Background and Data Presentation
a) Explain why you decided to investigate the topic you chose.
b) Describe where you obtained your information.
c) Include a nicely formatted data table containing your information.
d) Explain how your data table relates to your topic.
3. Data Analysis
a) Tell your reader what he or she should notice about the data. (Assume he or she doesn’t know how to read your data table very well) Point out at least two important data values or trends, and make sure you tell the reader exactly what numbers you looked at to draw your conclusion(s)!!!
b) State what statistics calculations you did on your data, and explain what the results tell you (or why they are NOT helpful).
c) Include a graph of your data. (I will evaluate you on your ability to choose an appropriate graph style for your data.)
d) Describe what the reader should conclude from the graph, and explain to the reader which aspects of the graph support your conclusions. You may want to explain how the ideas you presented in (a) are also revealed in the graph. (NOTE: When you refer to your graph, remember to avoid general statements like, “the graph shows …” and instead use specific descriptions such as, “you can see from the height of the bar for Seattle that ….”)
e) If you used a pie graph, use the percentages to estimate results in a new situation. If you used a bar (not a histogram, though!) or line graph, describe how the percentage change or average rate of change helps you interpret the graph.
4. Conclusion (This also does not need to be long.)
a) Summarize how your data supports the main point you listed in the introduction.
b) Present any concerns or questions you have about the data you found.
c) Describe any additional questions you thought about while working with your data that you would like to investigate in the future.
Other things Erik will consider when grading your paper:
· Is your paper fairly readable? (Is the handwriting or typing neat, is your English good enough that it doesn’t slow my reading too much?)
· Is it coherent? (Does your paper start with a main idea, help me understand your idea, then present “new” information related to that idea?)
· Is it interesting to read? (Did you attempt to get me interested in the idea you decided to investigate, or are you simply “doing what’s required?”)
· Did you try to use several of the mathematical ideas from the first part of this quarter to investigate your topic from different angles?
Remember, I am using this to evaluate your understanding of topics we’ve covered – and correctly explaining why certain techniques are NOT appropriate for your data is also a good way to demonstrate understanding.
· Did you try to weave the data table and graph into the actual text itself, rather than just include them in an appendix? (If you use Microsoft Word, you can copy and paste your graphs and data tables right into the document!)
· Did you follow the instructions carefully?
This paper is a fairly significant part of your grade (it counts as part of both your Excel grade and your midterm portfolio grade) because I’m checking to see how much you’ve retained of the topics we’ve covered along with how well you can apply the ideas to a new situation. So it’s a bit like a midterm exam in that sense. That makes it all the more important to do well – and I REALLY want you to do well. Nothing would make me happier than being able to show your papers to others in the educational community as a testament to how well people can understand and use mathematics when we allow you the freedom to make the ideas meaningful for you. So please make use of the available resources – class time, your classmates, me, the librarians, and the Writing Center!!
Final Note: If you have a large data set and want to compute statistical information, or want to create a histogram, the second sheet of the spreadsheet for this lab (95Lab4.xls) contains a “Stat Calculator” I created. All you have to do is put your data in the first column (it can handle up to 253 data points), and Excel will do the rest. Then you can just put a title and axis labels on it and copy and paste the chart into your paper.