PSYC 2317 Basic Statistics
Summarizing Data Assignment
Using a spreadsheet program such as Excel, create a histogram, bar chart, pie chart, scatterplot and timeline, using the instructions below. You may use any data that you find interesting, as long as the data is suited to the type of graph you are creating. Consider using health data, sports statistics, weather data, demographic data, etc.
If you don’t already have Excel, note that Lone Star College provides students with provides students with free access to Microsoft Office 365, which includes Excel, Word, PowerPoint, One Note, etc., and 1 TB of OneDrive storage. CLICK HERE to access.
Be sure to label each graph so that a reader would be able to understand what is being presented, even if they don’t look at your raw data. Include a title and all appropriate axes labels or other information, and include the units of measurement (for example, inches, centimeters, US Dollars, percent…) The title should not be “Histogram” or “Bar Chart,” but should explain the data you are displaying. Use a variety of styles, colors, etc., to illustrate that you know how to use the format and design tools. When you have completed all the graphs, save them all in one Excel file. You may use multiple tabs, if you choose. Submit the assignment via the dropbox provided by midnight on the date due.
1. Histogram – Using continuous data along the x-axis (for example, age at first marriage) and frequency (number reported that they were first married at that age) on the y-axis. There are a couple of ways of doing this. Although the second way listed below is a more complicated, this is a good opportunity to see what the Data Analysis tab has to offer. You only have to create one histogram, so you can choose either option 1 or 2 below (not both).
Histogram option 1:
Enter the data in a column.
Highlight that column.
Click on the Insert tab
Click the down arrow beside Charts to see a list of charts available
Click on Histogram.
Right click the numbers on the horizontal axis and select Format axis. Then choose Bin width and enter a nice round number that fits your data.
Checking the overflow or underflow boxes will allow you to determine the starting/ending points for your bins (intervals along the X axis).
Click Design under the Chart Tools tab and select Add Chart Elements to make sure you have a title and axes labels. (You may see a box with a + sign beside your chart that will allow you to do this also.)
Play with Design and Format tabs and other options to make the histogram look the way you want. (You may see a box with a paintbrush beside your chart that will allow you to do this also.)
Histogram option 2:
Open Excel and click on the Data tab. Data Analysis should appear as an option on the far right. If not, follow the steps below:
Click on the File tab
Select Options
Click on Add-ins, and then on Analysis ToolPakClick on Go
Make sure Analysis ToolPak is checked and click on OK
Now when you click on the Data tab, you should see the Data Analysis box.
Once the Data Analysis box is visible, then enter your data in a column, with the heading in the top cell.
In the second column, create a bins column by choosing the limits to the intervals you want to use. These set the “bins,” or the intervals that you want your data to be separated into. In the example below, I chose the following bins: 6, 6.5, 7, 7.5, 8, 8.5 The first number is lower than all my data points, and the last number is higher. They fall into equal intervals, using a nice round number.
Now click on Data Analysis, then on histogram.
Click on the Input box, and then highlight your entire first column, including the heading. (Highlight by left clicking in the first cell. Hold the mouse button down as you move the mouse to the last cell before releasing.)
Click on the Bins box, and highlight your Bins column.
Check the box to indicate you included labels.
Choose one of the radio buttons to tell Excel where you want your output to be displayed: In a new workbook, on a new worksheet page in the same workbook, or on the same page you are on. If you choose the same page, you must specify the cell where you want you want the output to begin by clicking on the output range box first and then on a cell on your page where you want your output to be.
Most importantly, check the box that says Chart Output.
Change the width of the columns so that there are no gaps, as is typical with histograms: Right-click on the columns of the histogram, choose Format Data Series, and slide slider to No gap.
In my example that follows, students in my class measured their hand widths, as indicated below. Each number in the first column represents the hand width for a different student. In the second column, I entered numbers to represent the “bins,” or groups, I’ve chosen. Excel will create a histogram with bars representing the number of students whose hand width falls into these groups. The number of bars created depends on how many bins you select.
Put your data in this format:
Hand Width Bins Based on the bins I indicated, Excel will count the number of hand widths in each of the following intervals….
7.75 6 15557589535006 or below
7 6.5 1651009906000greater than 6 but no higher than 6.5
7.25 7 1555758953500greater than 6.5 but no higher than 76.75 7.5 1555759906000greater than 7 but no higher than 7.5
7.75 8 1555759906000greater than 8
7.25 7.5 7.5 6.25 6.75 7.25 7.0 7.8 6.5 7.25 7 7.75 7.25 7 6 6.5 8.3 2. Bar chart – This should include a nominal independent variable (categorical data) on x-axis (horizontal axis). For example, you might ask several of your friends to measure their height (in inches). Then calculate the mean height for men and the mean height for women and create a bar chart in excel to show the two means. (You may calculate the mean in Excel , with your calculator or by hand.)
How: In Excel, type “Men” in cell A1 and “Women” in cell B1. Then enter the mean height for each group in the cells just below. Highlight both columns by clicking and dragging your mouse over your data. Click on Insert , then Column (chart). Play around with the chart tools (design, layout, format) to add a title, change colors, labels, etc. to your liking.
Here is what your data may look like:
Mean Height (in inches)
Men 69.3
Women 65.6
3. Pie Chart – Find data that can be presented as % of total. One source of data of interest to psychologists and sociologists is the General Social Survey, which can be found by Internet search. If you use this database, you might list how many people answered a question in a specific way. For example, if 1000 people responded to a particular question, and 500 answered “yes,” 300 answered “no” and 200 answered “undecided,” you would enter this data like this:
Yes No Undecided
500 300 200
Highlight the cells that contain these data by clicking and dragging the mouse; click on Insert, then Pie Chart. After the chart is created, you can change the data to % by clicking on Design and selecting one of the designs with % on it. (Skip this step if you entered the data as percentages.) Click on the title and type into the data entry box to change the title. Play around with the design, layout and format tools to get to chart to look the way you want it to look.
4. Scatterplot –showing relationship between two variables, for example, hand width and height. See the hand width and height data below, or collect your own data to present. Make sure you keep the data paired correctly, so that each row represents two measures for the same person. For example, the person below who was 68 inches tall had a hand width of 7.75 inches. Enter the data into an excel spreadsheet. Highlight the data, then click Insert and then Scatter. Again, play around with the design, layout and format tools to get to chart to look the way you want it to look. The data will appear clearer if you change the “height” axis so that it doesn’t begin at 0. (Right click on the axis and select format axis, then set minimum.)
Height Hand Width
68 7.75
62 7
69 7.25
64 6.75
65 7.75
65 7.2
64 7.5
63 7.5
68 7.8
63 6.5
61 7.25
65 7
70 8.75
58 6.25
67 8.5
65 7.5
66 7
63 7
67 9
62 6
5. Line chart – Create a line chart (time plot) to show change over time. For example, the data below from the General Social Survey indicate the proportion of respondents (of those with a valid answer) who said they were either “very happy” or “fairly happy” when asked “If you were to consider your life in general these days, how happy or unhappy would you say you are on the whole?” Alternatively, you could look at a particular player’s batting average over the last five years, or the amount of rainfall in Houston each month.
How: Enter your data in Excel in columns, with the title in the first row of the second column, and the dates in the first column. The data should be in the second column, adjacent to the appropriate date, as in the example below. Do not put a title over the first column, especially if you are using years.
Put Title Here
2002 0.67
2004 0.62
2006 0.42
2008 0.32
2010 0.49
2012 0.56
Then highlight the entire area, including the area with the title. Click on Insert, then click on line graph.
