Statistics Unit

2.6 Tables and Graphs

    Frequency Table – Average Teacher Salaries by State 2020-2021

    Average Salary Number of States Relative Frequency
    <$50,000

    3

    6%
    $50,000 to $55,000

    17

    33%
    $55,000 to $60,000

    8

    16%
    $60,000 to $65,000

    8

    16%
    $65,000 to $70,000

    3

    6%
    >$70,000

    12

    24%
  1. Create a bar graph showing average teacher salaries.

    2. A bar graph of the average teacher salaries by state. The horizontal axis represents the salary ranges and the vertical axis represents the number of states in each range and goaes from 0 to 18 couning by 2. Ther are 3 states whose average salaries are less than $50,000. There are 17 states whose salaries are $50,000 to $55,000. There are 8 states who salaries are $55,000 to $60,000. There are 8 states whose salaries are $60,000 to $65,000. There are 3 states whose salaries are $65,000 to $70,000. There are 12 states who salaries are greater than $70,000.

    Frequency Table – U.S. Vehicle Sales October 2022

    Company Number Sold Relative Frequency
    Toyota 165,570

    26%
    Ford 158,327

    25%
    Honda 72,409

    11%
    Hyundai 60,604

    10%
    Kia 58,276

    9%
    Subaru 48,568

    8%
    Mazda 25,319

    4%
    Lexus 21,797

    3%
    Acura 9,136

    1%
    Lincoln 7,196

    1%
    Genesis 4,353

    1%
    Total

    631,555

    100%

  2. Create a pie chart showing percentages of each type of vehicle sold in the United States in October 2022.

    3. A pie chart showing the percentages of U.S. Vehicle Sales for October 2022. Toyota sold 26%, Ford 25%, Honda 11%, Hyundai 10%, Kia 9%, Subaru 8%, Mazda 4%, Lesus 3%, Lincoln 1%, Genesis 1%, and Acura 1%.

  3. Create a bar graph showing vehicles sold in the United States in October 2022.

    4. A bar graph representing the total U.S. vehicle sales for October 2022. The horizontal axis represents the companies. The vertical axis represents number of cars sold by each company starting at 0, counting by 20,000, and ending at 180,000. From left to right on the graph Toyota sold 165570, Ford 158327, Honda 72409, Hyundai 60604, Kia 58276, Subaru 48568, Mazda 25319, Lexus 21797, Acura 9136, Lincoln 7196, and Genesis 4353.

    Time Chart - Percentage of U.S. Population that is Foreign –Born

    Year Percent Foreign-Born
    1940 8.8
    1950 6.9
    1960 5.4
    1970 4.7
    1980 6.2
    1990 8.0
    2000 10.4
    2010 12.2
    2020 13.5

  4. Create a time series graph showing the percentage of U.S. population that is foreign-born from 1940 to 2020.

    5. A time series graph for the percentage of the U.S. population that is foreign-born. The horizontal axis represents the years and goes from 1930 to 2030. The vertical axis represents the percentage of the total population and goes from 0 to 14. There are several points plotted and connected on the graph. The points are (1940,8.8), (1950,6.9), (1960,5.4), (1970,4.7), (1980,6.2), (1990,8.0), (2000,10.4), (2010,12.2), and (2020, 13.5). The line decreases from 1940 to 1970 and then increases to the last point at 2020.

Using Excel

Part 1: Teacher Salaries

  1. Copy and paste the data into a new sheet so you don’t lose the original data.
  2. Delete any columns you don’t need.
    1. Select the column
    2. “delete sheet column.”
  3. Sort the salary data smallest to largest.
    1. First highlight the cells you want to sort.
    2. You can choose to sort A-Z or Z-A.
    3. Click yes to expand the selection.
  4. Make a frequency table.
    1. For average salaries you can use ranges <50000, 50000-55000, 55000-60000, 60000-65000, 65000-70000, >70000
    2. For starting salaries you can use ranges 30000-35000, 35000-40000, 40000-45000, 45000-50000, 50000-55000
  5. Create a graph for the number of states in each salary range.
    1. Highlight the frequency table you made
    2. Click “insert” a graph
    3. Label the axes by adding axis titles and give the graph a descriptive title.
  6. Copy and paste the graph into a Word document. Save your Word document on your H drive or One Drive.

Part 2: Number of States in Each Region

  1. Copy the original data into another new sheet.
    1. You may delete all columns except state names and regions.
  2. Sort the data according to Region.
  3. Make a frequency table showing the number of states in each region.
  4. Create a bar graph for the number of states in each region.
  5. Label the axes and give the bar graph a descriptive title.
  6. Save your bar graph to your Word document.

Part 3: Percent of States in Each Region

  1. Make another table showing the percentage of states in each region.
  2. Create a circle graph for the percentage of states in each region.
  3. Choose a style for your circle graph so that the percentages are labeled.
  4. Give a descriptive title to your circle graph.
  5. Save your circle graph to your Word document.

Part 4: States in Region with Income Tax

  1. Copy the original data onto another new sheet.
    1. Delete the first two header rows.
  2. Insert an empty row above all the data.
  3. Filter the data so that only the states in the Southeast are showing.
    1. Highlight the data
    2. Click “filter.”
  4. Sort the data according to whether these states have a state income tax.
  5. Start a table showing the percentage of states in a particular region with an income tax. Record the percentage of Southeast states that have an income tax.
  6. Clear the filter.
  7. Filter the data so that only the Northeast states are showing.
  8. Sort the data according to whether these states have a state income tax.
  9. Add to your table the percentage of Northeast states with an income tax.
  10. Continue with the other three regions.
  11. Create a clustered column graph showing the percentage of states in each region with an income tax. Label axes with titles and give a descriptive title to your graph.
  12. Save your clustered column graph to your Word document.

Part 5: States in the Region with or without Income Tax

  1. Copy the original data onto a new sheet.
    1. Delete the first two header rows
    2. Delete the salary columns.
  2. Insert an empty row above all the data.
  3. Filter the data so that only the Southeast states are showing.
  4. Sort the income tax column.
  5. Start a frequency table showing the numbers of Southeast states with and without an income tax.
  6. Clear the filter. Now filter so that only the Northeast states are showing. Sort the income tax column.
  7. Add to your frequency table the numbers of Northeast states with and without an income tax.
  8. Highlight the frequency table and insert a stacked column OR clustered bar graph. Label axes and give a descriptive title.
  9. Save graph to your Word document.

Extra note:

To edit the axis numbering on a graph, click on your graph. Then from the drop down menu in the upper left corner of Excel, select Vertical (Value) Axis. Click Format Selection right under the drop down menu. In the box that opens on the right side of your screen, click on the picture of the histogram. Then edit the numbers accordingly. You can also edit the Horizontal Axis if needed.