1 / 28

Introduction to Excel 2007 Bar Graphs & Histograms

Introduction to Excel 2007 Bar Graphs & Histograms. Psych 209 February 1st, 2011. Bar Graphs and Histograms. Although they may look similar, bar graphs and histograms differ in several important ways: 1. The types of information they convey 2. The x-axis 3. The y-axis.

holt
Télécharger la présentation

Introduction to Excel 2007 Bar Graphs & Histograms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Excel 2007Bar Graphs & Histograms Psych 209 February 1st, 2011

  2. Bar Graphs and Histograms • Although they may look similar, bar graphs and histograms differ in several important ways: 1. The types of information they convey 2. The x-axis 3. The y-axis

  3. Bar Graphs and Histograms • Let’s say Instructors 1 and 2 both teach the same Psych class. You are trying to decide which instructor to take the class from (based on the grades they gave last quarter). • First, you take a look at a bar graph…

  4. 1. Types of Information: Bar Graph • Bar graphs compare the mean scores for two or more groups • What can we tell about the average grade you might receive from these two instructors?

  5. 1. Types of Information: Histogram • Histograms show the frequency of values in the data set. • Based on the histograms above, from which instructor would you choose to take Class A? Why?

  6. 2. The x-axis • The x-axis of a histogram includes the range of possible scores in the data set. In our example, students received grades ranging from 2.4 to 4.0, so the x-axis includes this range of values. - NOTE: Even though Instructor 2 gave no grades of 3.0, 3.3, etc., ALL consecutive values between 2.4 and 4.0 are included • The x-axis of a bar graph simply identifies the levels of the independent variable (i.e., the groups you wish to compare: Instructor 1 and Instructor 2).

  7. 3. The y-axis • In this course, we always label the y-axis of a histogram as “Frequency,” since it represents a count of how frequently each value appears in the data set. - NOTE: The values on the y-axis of a histogram must always be consecutive whole numbers • The y-axis of a bar graph always represents values of the dependent variable and must be labeled as such. Since these values are means, the y-axis on a bar graph can include decimal values.

  8. Creating a Bar Graph Step 1: Use the means you calculated for Instructor 1 and 2. Step 2: Highlight the cells and click on the ‘Insert’ tab at the top. Select ‘Column’ under the ‘Charts’ menu.

  9. Step 3: Select a ‘2-D Clustered Column.’

  10. Your graph first appears unformatted. Notice there are no chart or axis titles: Step 4: First remove the legend (‘Series1’) by selecting it and pressing ‘Delete.’ Step 5: Then right-click on the chart and choose ‘Select Data’ from the menu.

  11. Step 6: In the ‘Select Data Source’ dialog box, click on ‘Edit’ under ‘Horizontal (Category) Axis Labels.’ When the ‘Axis Labels’ box appears, highlight both of your variable names on the spreadsheet and hit OK twice.

  12. Step 7: Now you will add a chart title and y-axis title under the ‘Layout’ tab: • For ‘Chart Title’ you want the ‘Above Chart’ option. Type your title and press Enter. • For ‘Axis Titles’ add a vertical (y-axis) title by choosing the ‘Rotated Title’ option.

  13. Step 8: Finally, fix the formatting of the y-axis numbering scale. Right-click on the y-axis, then select ‘Format Axis…’

  14. Change the ‘Minimum’ value to 0.0, the ‘Maximum’ value to 4.0, and the ‘Major unit’ to 0.5

  15. That’s it! Your bar graph should now be easier to interpret than when you first created it: We will now turn our attention to creating histograms.

  16. Creating a Histogram When you create a histogram in Excel, you need to make “bins.” Bins represent the entire range of values in your data set. First, let’s consider our variable: GPA is measured on a scale of 0.0 through 4.0, so we could potentially create bins for each grade (0.0, 0.1, 0.2….3.9, 4.0). However, the scores (i.e., grade values) for Instructor 1 only range from 2.7 to 3.3. So we will instead use a bin range of 2.6 through 3.4. This will include all values for Instructor 1 as well as one empty bin on both ends of the figure.

  17. NOTE: Whatever bin range you choose, you MUST include ALL consecutive values between the lowest and highest score (even if their frequency is zero).

  18. Step 1: Create a column for the bins. In this example, the bin range will be 2.6 through 3.4. This will include all recorded values, plus give us one empty bin on each end of the figure. Precede each value with a single quote (‘) when entering them into the bin list, as in ‘2.5, ‘2.6, etc. This informs Excel that these are categorical variables that can be counted up.

  19. Step 2: Histograms depict frequencies for specific data values. To count up frequencies in Excel, use the function COUNTIF. Create a label in cell E1 named “Frequency.” In cell E2 enter your function: =COUNTIF(B2:B11,D2). B2:B11 is the data range we wish to count from D2 is the cell that contains the value we want ot know the frequency of. In other words: “if the value is 2.6, then count all occurrences of 2.6 in the list of grades for Instructor 1”

  20. Step 3: Next we want to copy the formula for the other bins. To prevent the range of grades from changing when you paste, add a $ before each column letter and row number as illustrated: Leave D2 unchanged. Remember that D2 contains the criteria that tells the COUNTIF what to count. We want that to be different for each bin that is going to be counted.

  21. Step 4: Copy the COUNTIF formula from cell E2 through all the cells in column E down to cell E10. Notice how this action formed a frequency table!

  22. Step 5: Select the cells in the Frequency and Bin column. Select the Insert tab on the command bar and click Column.

  23. Steps 6-9: A general strategy for cleaning up the chart. Fix y-axis units Update title Delete the legend Note: the bars in a histogram should touch. Provide missing axis titles Excel automatically assigned the bin values as axis unit labels

  24. Step 6: Remove the legend by selecting it and pressing Delete. Step 7: Change the chart title to something more informative. (Note for your Excel assignments your title of the graph is your name and student number) Step 8: The y-axis of a histogram should NEVER include numbers with decimal places. Change your y-axis scale by right-clicking on the numbers and selecting ‘Format Axis…’ Change the Major unit from 0.5 to 1

  25. Step 9:Label both the x and y axes by selecting the ‘Layout’ tab on the command bar and clicking on ‘Axis Titles.’

  26. Your histogram should now look like this. Almost done!

  27. In a histogram, the bars should be touching.Left click a bar, then right click, and choose “Format Data Series…”. Set the Gap Width to Zero.

  28. Your histogram is now complete!

More Related