Naming Ranges and Basic Analysis

• This is from chapter 4
  • There is a lot of good information in chapter 4, you should skim it.
  • One item is on page 98, creating range names in a workbook.
  • This is really helpful as it
    • Simplifies selecting ranges
    • Improves readability
    • Improves accuracy.
  • To name a range
    • Highlight the range
    • Change the name in the name box
    • Name the data in column N to be Win
    • 
    • 
    • rules on page 98
      • No white space
      • letters, numbers, underscore, backslash, period
      • Must not start with numbers
      • Can't look like a cell address (A2, AW3, but \A2 and \AW3 are ok)
      • limit to 255 characters.
  • You can name all columns/rows in an area
    • select all of the data in the area, including labels
    • On Formulas->Defined_Names->Create from Selection
    • 
    • Excel will "fix" the headings to make good names.
    • 
    • Name Columns A-J with the column heading name.
  • Note that you can navigate to the named range using the dropdown in the name box.
  • You can manage names
    • Formulas, Defined Names, Name Manager.
    • 
    • Add a comment, which is probably a really good thing.
  • Let's add a new sheet to summarize the Win data.
    • Add a new sheet called Win Percent Analysis
      • Add this to the About table
    • Count the data,
      • But use the value Win not Data!N2:...
      • Make sure you give everything a label
      • 
    • Find the five number summary, average , standard deviation and range of the data
    • 
    • Build a box and whisker plot
      • Insert the chart (Insert->Charts->Statistics Chart -> Box and Whisker)
      • 
      • Set the Chart Data Range to be Win
      • 
      • Adjust the plot so it is acceptable.
        • Title
        • Added data labels and removed horizontal axis
        • Changed the color
        • Changed the width.
        • 
      • Finally, let's build a column chart (histogram) to display this data.
      • 
  • Now build an analysis of the different attributes
    • Add a new worksheet, Attribute Analysis
      • Don't forget to add this to the About sheet
    • Copy the names of all of the column heading (B-J) and paste them in a column.
      • I'm not sure a five number summary would be useful.
      • But we can count the number of times each attribute occurs.
      • I used sum, but other functions are possible.
      • Then I sorted this.
      • 
      • Finally I added a Column chart of the frequency.
      • 
• Most of this is not from chapter 4, but last semester
• Save your workbook, we will continue.