Homework 3: Let me count the ways...

• Demonstrate/Practice your ability to employ counting techniques.

1. Download this workbook.
2. (10%) Modify and maintain the About tab as you work.
   • Add your name.
   • Discuss any data errors you find. (I don't think there are any)
   • Discuss any major techniques you employ (probably not much)
   • Maintain a list of sheets and contents.
3. (10%) Document your work by
   • Naming ranges used
   • Naming worksheets appropriately.
4. Name the cells in raw data
   • Click in cell A1 of the sheet
   • press and hold ctrl shift
   • Press the down arrow once
   • Press the right arrow once
   • This should select the entire range.
   • Name the columns in the range.
5. (30%) Basic Analysis
   • Create a new worksheet, Basic Analysis.
   • Copy the column headers from RawData to this sheet, B1:L1
   • Add a row describing the data as text, date, currency or numeric
   • 
   • Add the following labels in row A
   • 
     • I am organizing this so you can develop a set of formulas in column B and copy it across to column L
     • You will not use range names in this (unless someone knows something clever)
     • Do use addresses
     • Note the data goes from 2:2584
     • Remember RawData!A2:A2584 will give access to the data in row a on the RawData table.
   • Perform a basic analysis of the name data in column B
     • In B3 develop a formula that will count all the non-blank cells in the name data.
     • In B4 develop a formula that will count all numeric cells in the name data.
     • In B5 develop a formula that will count all blank cells in the name data.
     • In B7 develop a formula that will count the number of unique cells in the name data.
     • In B9-B13, if the data is identified as something other than text in B2, compute the five number summary. You should do this in ALL columns, but text columns should display a blank.
   • Copy these formulas across to column L to compute the five number summary for all data.
   • Compute outliers for any currency or numeric data.
     • The interquartile distance (d) is the Q3-Q1
     • The interquartile range (IQR) is [Q2-1.5d, Q2+1.5d]
     • Any value that falls outside of this range is an outlier.
     • In row 18, compute the number of outliers for this dataset.
   • Format all cells in this table appropriately.
   • 
     • I spot checked this, but there may be errors.
     • I tried to draw box and whisker plots, but the outliers are too far out, perhaps a log scale?
     • Feel free to draw any graphs of non-text data you feel are appropriate.
6. (30%) Text Analysis
   • I feel we should analyze the text with fewer than 40 unique values.
   • Make a new worksheet called Text Analysis
   • You did add this to the About sheet, right?
   • For country, build a basic frequency distribution and histogram
     • Extract the unique countries.
     • Build frequency counts
     • Sort by frequency
     • I used a column chart for my graph.
     • 
   • For currency do the same
     • But add a column to count the number of countries that use each type of currency.
     • 
   • For state
     • Find the frequency for each project state.
     • Find the average backers for each project state.
     • Find the average pledged, in US Dollars, for each project state
     • Find the Average goal, in US Dollars, for each project state.
     • 
7. (20%) Exploring Further.
   • Add a worksheet labeled Explore
   • Add an input area where the user can change country and state
   • 
   • Add an area where the following are calculated based on the user's input
     • The number of projects matching the criteria
     • The average goal, in US dollars, fore projects matching the criteria
     • The max/min goal, in US dollars, fore projects matching the criteria
     • The average pledged, in US dollars, fore projects matching the criteria
     • The max/min pledged, in US dollars, fore projects matching the criteria
     • 
   • Build the following table, displaying the number of backers for each currency for each project state
     • You should develop a single formula that is copied to all cells.
     • 
   • Compute, using a single formula, the highest pledge per backer for any project.
   • Based on the previous result, find the name of the project with the highest pledge per backer rate.
     •