Please see the attached Word doc for directions and the Excel file attached to answer the questions.
For the first half of this learning lab, the goal is to demonstrate
how we can use Excel to construct frequency distribution tables
and graphs of our data. Specifically, we will generate a histogram,
a polygon, and a pie chart of the baby looking time data.
For the second half, the goal is to show you how to extract the
measures of central tendency (mean, median, and mode) from a
dataset with Excel and with your calculator.
Introduction: When researchers conduct experiments, they often plot the obtained data in different ways to get a feel for the
results. Different kinds of graphs can be used for different settings. Graphing your data in clear ways is also very important
when researchers publish their findings in scientific journals. The
phrase a picture is worth a thousand words strongly applies in
that situation. Thus, being able to graph your data is an important
The data in Excel: Open the file
You will see data from a study of how long babies looked at a
stimulus. Researchers collected data on 20 babies. Thus, you can
now see data for a total of 20 babies. In column A it lists the babies in the experiment. In column B their looking times in seconds
out of a 60 second period.
Part 1. Creating a frequency distribution table. In column D,
you see the word Range in cell D1, and below it are 6 categories,
or ranges of numbers. In column E, you see the word Frequencies
in cell E1. Create a frequency distribution table by filling out cells
E2 to E7 to indicate the number of babies who had scores in each
of the ranges listed in Column D.
Part 2. Graphing the frequencies with a histogram. Based
on the frequency distribution table you have just created, we can
generate a histogram to illustrate the number of babies that had
scores in each range. Follow the steps below to create your histogram.
1 Select the data to be graphed. Click with your mouse in
cell D2, hold the button down, and drag first to the right
to cell E2, and then down to cell E7. Now release the
left mouse-button. Cells D2 through D7 and E2 through
E7 should now be selected (highlighted).
Click on the ?Insert? tab at the top of the spreadsheet.
You can see that there a number of different ways that
we can illustrate the data. For a histogram, click on the
?Column? option. Then click on the leftmost chart in the
?2-D Column? section.
A bar graph should now be visible. It is important to realize that this is a bar graph, because there are spaced
between the bars. To change it into a histogram, use
your right mouse button to click on one of the bars. This
will open up a small menu on your screen. Select ?Format Data Series.? Enter ?0? in the Gap width window or
move the slider all the way to the left, so that you have
essentially selected the ?No Gap? option. In this same
screen, you can change many features of the graph
(such as the color of the bars show borders). To change
the color of the plotted bars, go to ?Fill.? Feel free to
change the colors of your histogram if you would like.
When you are finished, select ?Close.?
To really communicate aspects of your data, it is important that charts are clearly labeled and axes include the
appropriate titles. Therefore, you will need to add a title
and labels to your chart so that viewers know what they
are looking at. To do this, click ?Layout? in the Chart
Tools options. Select ?Axis Titles? > ?Primary Horizontal
Axis? > ?Title Below Axis.? You can now enter a label for
the X-axis. Titles and labels should be concise but still
communicate exactly what the viewer is seeing. Something like, ?Looking Time Range? would be an adequate
label here. When you have entered your label for the Xaxis, go back and enter a label for the Y-axis as well.
You will follow the same steps, but select ?Primary Vertical Axis? in place of ?Primary Horizontal Axis.?
To add a title to your chart, click ?Layout? in the Chart
tools options. Select ?Chart Title? and then select
?Above Chart.? Again, enter a title that concisely summarizes what viewers are seeing. Something like ?Frequency of Babies? Looking Times by Range? would sum
up the data for the histogram that you?ve just created.
Feel free to play around with changing colors, fonts, etc.
on your chart. Because the labels on our axes completely communicate what the viewer is seeing, you can
delete the key that contains the label ?Series 1.? When
you are done editing your chart, move it by left-clicking
near its border, and dragging it over while holding the
left button down. Position it with its top-left corner approximately along the edge of cell G2. Note: At this
point it would be good to save the Excel file, to make
sure you don?t lose any of your work.
Part 3. Graphing the frequencies with a polygon chart. As
described in your text, another way to plot these data is with a
polygon chart. Follow the steps below to create your polygon
1 Click a cell in the spreadsheet to make sure your histogram is not selected (the borders will change to plain
2 Select the data as you did in Step 1 above.
3 Again click on the ?Insert? tab at the top of the spreadsheet. This time click on the ?Line? option. Then click on
the leftmost chart in the ?2-D Column? section.
4 Repeat the steps listed above to add labels to your axes
and a title to your chart. Again, the ?Series 1? label
does not add any new information, so you can delete it.
5 Again, You may feel free to modify the colors, fonts, etc.
of your chart. When you are finished, move the polygon
chart below your histogram so that both are clearly visible. Save your work.
Part 4. Graphing the frequencies with a pie chart. Now we
will construct a chart that was not discussed in the book, a pie
chart. The pie chart looks like a pie, with the sizes of the different
slices corresponding to the frequencies of the different categories.
Thus, a category with a high frequency count will show up as a
large slice of the pie. Follow the steps below to create your pie
In the Tab bar of Excel (at the very top), go to the Insert tab. Now
click on Pie in the options that come up (it should show a pie symbol next to it).
Click a cell in the spreadsheet to make sure that your
polygon chart is not selected.
2 Selecting the data as in the instructions above.
3 Again click on the ?Insert? tab at the top of the spreadsheet. This time click on the ?Pie? option. Then click on
the leftmost chart in the ?2-D Pie? section.
4 The pie chart should now be visible. This time, there are
no axes to label, but you do need to provide a label for
the chart to describe what information it contains. This
time you should not delete the information in the legend. Think about why this is the case.
5 Again, you may feel free to modify the colors, fonts, etc.
of your chart. When you are finished, move your pie
chart below your polygon chart so that all three of the
charts that you have created are visible. Save your
Part 5. Calculating measures of central tendency with Excel and calculator. To calculate the mean, median, and mode,
Excel uses the following commands:
Mean (for values 1 to i of X)
Median (for values 1 to i of X)
Mode (for values 1 to i of X)
Using these commands, calculate the mean, median, and mode
for the baby looking time data. Enter the formula for the mean in
cell B22, the median in B23, and the mode in B24 of the Excel file.
Save your work.
To calculate the mean and median with your calculator, enter the
data on baby looking times in a list in your calculator. To do this,
follow these steps:
1 Change your calculator to STAT mode by pressing 2nd and
2 You will have the option to select either 1-var or 2-var option.
The 1-variable option is used for a list of data for one variable. The 2-variable option is used for a list of data for two
variables, such as an independent and dependent variable
(we will talk more about these terms later). Select the 1-var
option and press ENTER.
Press the DATA key to enter your data. Your calculator will
ask you to enter a value for X1. This is the looking time
recorded for the first baby. So, enter this value and press the
Now you will be asked to enter the frequency with which this
value occurred. You may indicate multiple frequencies for
values that show up more than once, or you may enter each
value separately with a frequency of 1. It is up to you. When
you have entered the appropriate frequency, again press the
Enter all of the values and frequencies in our data set. Remember that if you choose to enter numbers that appear
more than once as multiple frequencies, you will not enter
that value again. Make sure that you press the down arrow
after entering the last value, or your calculator will not save
Once you have entered the data, all you need to do is press
the STAT button on your calculator (or STATVAR if you have
a newer model). Note: You may need to again select 1-Var
Stats, the list you used to enter the data, and One for FRQ.
Press calc. The calculator window should now show the output. The value of the mean is on the third line, where it
shows an x with a line above it (x bar). You can find the median as the ninth item in the output, where it says ?Med =?.
Use the arrow keys to go down to this line. Verify that the
value you find for the mean and median with your calculator
are the same as the values you found with Excel. You do not
need to enter these values on your Learning Lab answer
sheet. The calculator we use for this course does not output
the mode. Fortunately, the mode will not come up later in
the course as a measure of central tendency.