[Back to Home]
Chapter 2 Technology Assignment.
Use Excel to generate histograms.
Step by step instructions for #5 on page 44. You must also do #6 on p. 45, without step by step instructions!
Before beginning: Make sure Excel has the Analysis Toolpak installed. (Same as Step 1 in Chapter 1 Assignment).
Step 1: Enter all the data values in a single column of Excel. (Double check your entries when done.)
Step 2: Order the data from smallest to greatest by first highlighting the column and then clicking the AZ button. (In the newer version of Excel you will find this under the Data tab.) When done you will be easily able to read off the smallest and largest values of the data set.
Step 3: In a different column, perhaps column C, type one of the following formulas so that you can calculate the class width (#5 asks for five classes):
=(52-10)/5 or =(A55-A1)/5 *
*this one assumes that your largest value is in cell A55 and smallest value is in A1. You don’t actually have to type “A55” or “A1”, you can click on those cells after typing “ =( ” and it will automatically fill in.
Step 4: Round your answer up to the next highest integer. You may just do this in your mind, or you can try out this formula: =INT(C1) +1 *assuming your formula for step 3 was in cell C1.
Step 5: Now we need to figure out what our class limits are. For making a histogram in excel we actually only need the upper class boundary values! Let’s mentally work out what the first class interval would be: 10 – 18, thus the first class boundaries would be: 9.5 to 18.5.
In cell E1 type this title: Upper Class Boundaries
In cell E2 type the value of 9.5.**
In cell E3 type the following formula: = E2+9
Drag the corner of cell E3 down until you have copied the formula and created a total of 6 classes** (we only want 5 classes, but this will create a space before the first bar in the final histogram).
You now have what Excel calls the Bin numbers and you are ready to make the histogram!
**Note: If your lowest number had been a zero then you would have just started with the upper class boundary of the first interval, in this case 18.5, and then you would only need the list of 5 classes. We only do this here to prevent our graph from beginning at 9.5 on the horizontal axis.
Step 6:
If you have the older version of Excel go to Tools > Data Analysis and select Histogram from the list.
If you have the newer version of Excel go to the Data tab > select Data Analysis and then choose Histogram.
For the input range, select the entire list of data values (from 10 to 52).
For the Bin range, select your five upper class boundary values (from 9.5 to 54.5).
Select Output range and then choose a blank area of your orksheet for where your graph will “land”.
Select Chart output.
Click OK.
(It will look something like the picture here, your range values will be different.)
Step 7: Tidy up your histogram. You can explore the various settings by right-clicking on different parts of the histogram chart area. You can make it larger or smaller by dragging the edges or corners. Most importantly though, you must not have any gaps between your bars! Here’s how to fix that:
Right-click on any bar of the histogram and choose: Format Data Series.
Older version go to Options tab and set gap width to be zero.
Newer version go to Series Options (will probably be what is first displayed) and slide the slider over to No gap.
Step 8: Answer the question – what shape does the histogram of data on highway fuel consumption for passenger cars have? Pick the best option out these: uniform, bimodal, skewed left or right, symmetrical.
Step 9: Submit your work.
Save the file to have a file name that includes the assignment number, your last name, and your section according to this format: Chapter2_LastName_01(if you are in the 1:30 PM class) or (_02 if you're 3:00 PM class). For example: Chapter1_Smith_01 or Chapter1_Jones_02, etc.
Send this file as an attachment to my BUBBS email: angela.j.loudon@biola.edu
If you have any questions, email Angela Loudon on BUBBS.