This is a step by step solution, using Excel. It shows how to
draw bar graphs, using the data from the first day of class, where everyone
was asked to "choose a number from 1,2,3,4".
First the data were gathered, and typed into an Excel Spreadsheet.
Excel was also used to total the counts, and calculate the relative frequencies
(recall these are the counts divided by total counts). The result
looks like:
Recall how both "absolute" and "relative" cell indices work together
to give a formula that can easily be dragged along to give all of the relative
probabilities. If you forgot this, you may want to look again at
Step 3 in Class Example 1.
Now let's make both the "frequency" and "relative frequency" type bar
charts for these data. One way to get graphics into Excel is along
the menu path "Insert", "Chart" (there is a shortcut icon that may
appear somewhere on your screen), that pulls up a menu:
As you can see there is a lot of stuff
that you can do from here. The type of vertical bars that we are
using in Stat 23, come from the first choice "Column". So no extra
work needs to be done here.
Clicking on "Next" changes the window to:
Note that Excel has made some (not all the good!) guesses about what
we want to see. In particular, it chose the full range of the everything
numerical in the entire spreadsheet. It did a pretty good job of
figuring out what we wanted as the "x" points, but it gave bars
showing
both the counts and the relative frequencies on the same
plot (not very useful). Furthermore it also thought we wanted to
see the total on the same plot, so note that it added an "x = 5"
part.
A look at the underlying spreadsheet show what the problem is.
The region in the moving dotted lines (which is the "Data range:"
in the above menu) is too big. We should trim it down to just the
part of the table we are interested in. Various methods for filling
in such data ranges were discussed in Class Example
1.
The next step is to tell Excel what we really want. You could
just type in the range that we want. Another approach is go back
to the original sheet, use the mouse to highlight the correct region.
Another possibility is to have the part of sheet you wanted highlighted
when you first call for the chart. In any case the result is:
The "Series in:" radio buttons allow you to work with either rows or
columns of data.
Note that the menu is now showing you a decent looking bar graph.
But, there are many ways in which we may want to change it, including labeling.
Some of these are easily at the next stage of the operation. Click
"Next >" to get to something like:
Note that I filled in some of the fields (e.g. "title") which will
add stuff to the graph (and in fact does it in the version that you see
on the menu). There are also lots of other things that you can fiddle,
on the other folder tabs that you can see on the menu. For example,
note that I already went to the one called "legend", and there I turned
off the little "series 1" box you can see in the menu above (that is only
useful when you have more than one type of bar, and want to show which
is which).
Click "Next >" to pull up the final menu:
This let's you choose where the graphic should be put. "As a
new sheet" would be convenient if all I wanted to was to make a
print (e.g. as you often do on the homework), since it is very easy to
print a sheet. However, in this case, I want to keep everything together,
for posting on the web page, and thus it is better to keep it on the same
sheet as other parts of this problem.
Clicking "Finish" puts this onto the spread sheet, in a not very
nice way:
Note that part of the spreadsheet is covered up, and the "aspect ratio"
(ratio of height to width) is not very pleasing. These problems are
fixed by using the mouse to "pull on" the black boxes at edge of the graph,
to put it where you want, with the shape that you want.
Other properties of the graph can be adjusted by double clicking on
them. For example, the gray background can be removed by double clicking
there (which pulls up a menu controlling various things. The colors
can be changed by double clicking on the bars themselves. Here is
the result of twiddling several of these. Note I got rid of the colors,
since I am not using a fancy color printer. You should experiment
with some of these things by yourself.
Note that an additional advantage of having the graphic in the same
spreadsheet is that when it is highlighted (as here), the part of data
being plotted also gets highlighted. In computer programming terms,
these are "linked objects".
You might try using the third column of data to add a similar graphic
to the spreadsheet, which shows the "relative frequency" version of this
bar graph. Here is what I came up with for this:
Note that as expected, this is basically the same as the above plot,
except the Y-axis is different. However, this required careful consideration
of the "resizing" that was done.
The final result of all the work done here is available on the spread
sheet version of this example.
Back to Stat 23 Home Page
Back to Marron's
Home Page