Subtotals

• This is section one of chapter 5
  • We will discuss three ways to look at data in excel.
  • The first was the table, which we will be back to in the future.
  • The second is Subtotals.
    • This is a stand alone feature.
    • It is used to summarize data.
    • Data should not be in a data table.
  • The tools we are looking at right now are to find subtotals in a dataset.
  • Let's look at the data from last time (this sheet.
    • Delete row 4 again
  • The feature we are going to explore requires the data to be sorted
    • We can explore a single feature
    • Or several levels of features.
    • For example, what if we are interested in unemployment rates, by year, by month, by state.
    • We should sort the data that way.
      • Select the entire table, including the column titles.
        • Ctrl-end, move to the last cell in the data table
        • F5 - enter A3, but don't click ok.
        • Hold down the shift key and press ok.
      • Sort from the data tab.
        • Sort by Year
        • Then by Month
        • Then by State FIPS Code.
  • The next step can be time consuming, it is a difficult computation.
    • On the Data tab in the Outline workgroup.
    • Select the subtotal
    • Since Year is our highest level, select Year in At each change in
    • For now, let's look at the average unemployment rate.
      • Select Average in the Use function cell.
      • Select Unemployment Rate in the Add subtotal to
      • Keep the replace current subtotals selected.
      • Select OK.
    • Notice now that there are categories on the left.
      • Select 1, note we have a grand average.
      • Select 2
        • note an average for each year.
        • Also note the plus signs on the left.
        • Click on the plus sign for 2009 and see all of the data for 2009.
    • If we go back to the subtotals button, we can find the sum civilian labor force by year.
      • Keep the replace current selected.
      • Note that the computation is now changed.
      • Do this one more time, but add average unemployment and unchecked the replace current.
      • Notice now that we have a total and an average for each year.
    • Clear all of the totals.
      • The Subtotal button
      • Remove all
  • You can subtotal on several levels, but be careful sometimes you can perform computations that don't make sense.
    • Let's add an average by year back in again.
    • Now add an average by Month, but don't remove the year averages. .
      • Select "Month" as At Each Change in
      • Unchecked replace current sub totals.
      • This will take a little bit of time.
      • And will include levels 1-4.
      • Explore these.
        • Level 1 is the grand average
        • Level 2 is the average by year.
        • Level 3 is the average by month.
          • Expanding 2009 and 2010 we see that the worst unemployment occurred around the first of the year in 2010.
    • Finally add an average by state (State FIPS code)
      • This will be a longer computation.
      • This will add level 5.
      • Level 4 will now be the average unemployment by each state represented
      • And level 5 will be the raw data.
      • Go back to 2010
      • Look at January, where the unemployment rate was highest.
      • Note that FIPS code 26, Michigan
  • You should probably do Hands on exercise 1