Test 2 Review

• This Document describes cleaning data.
• Functions and formulas
  • Ideas
    • What are functions and formulas?
    • Why should functions be used instead of entering formulas?
    • Finding and using functions in excel.
    • Optional/required function arguments.
    • The function editor
  • Tasks
• Computing grades:
  • Ideas:
    • What are assumptions?
    • What does it mean to isolate assumptions?
    • Why is it a good idea to use multiple worksheets?
  • Tasks
    • Entering formulas across multiple worksheets
    • Adding/renaming/copying ... multiple worksheets.
    • Formatting in excel (Borders around cells)
    • Converting text to columns
• A second project part I
  • Ideas
    • Data Custodian
    • Sanitizing data (removing identifying information)
  • Tasks
    • Randomizing a set of data
    • rand, randbetween functions
    • Copying values only
• The Homework field
  • Ideas
    • Review of techniques we have used to explore data.
      • Five number summary
      • Extract unique words
      • Frequency distributions
      • Histogram
      • Box and Whisker plot
    • Sorting ranges of data with the sort function.
    • Modifying graphs.
  • Tasks
    • Perform all of the above tasks
    • Fine tuning of charts (graphs)
• Cleaning and exploring the data
  • Ideas
    • Data should be consistent.
    • Data should be well labeled.
    • What should you do about blank cells?
    • Documenting actions performed on data.
    • Using charts to display multiple data fields at the same time.
    • Interpreting charts.
  • Tasks
    • More fine tuning of charts.
• Database Functions (vlookup)
  • Ideas
    • Basic database ideas
      • Data is stored in rows
      • Only one value per cell
      • Order of the rows is not important
    • Tables (not pivot tables) in excel.
    • Filtering and sorting with tables.
    • Slicers
    • Total rows in tables.
    • Vlookup
      • Exact vs approximate vlookup.
  • Tasks
    • Create a table.
    • using vlookup
    • Wordwrap in a cell
    • Converting a table back to a range.
  String functions
  • Ideas
    • What are strings
  • Tasks
    • trim, clean, find, left, right, mid, len
• Date and Time functions/ Basic Pivot tables
  • Ideas
    • Use of a pivot table.
  • Tasks
    • today, day, month, year, weekday, date