Homework 3: Dates, Times and Lookups

• Demonstrate your ablilty to use variaous lookup functions.

• Download this workbook.
  • It is a dataset of game projects on kickstarter.
  • I have cleaned it up quite a bit.
  • I have added two additional
• [5%] Add your name to the about sheet.
• On the Raw Data sheet, add a column between the Launched and the Pledged column
  • Label this days
  • Compute the number of days in the campaign.
  • 
• [5%]Add a column next to your days column
  • Label this start time.
  • Based on the "launched" column, comput the time the campaign started.
  • Format this as hours:minuts am/pm
  • 
• [10%]Add a column after the Deadline column
  • Label this Start Month
  • Compute the month number from the deadline column and store it in this cell.
  • Add a new worksheet called Constants
  • Build a table on this worksheet to convert month numbers to month names.
  • Add a column Month Name next to the Start Month column.
  • Use a vlookup function to convert the Start Month into a month name using vlookup.
  • YES, I WANT YOU TO USE VLOOUP here.
  • 
• [10%]Add a column after goal
  • Call this Goal Size
  • Add the following table (as two rows) to the constants sheet
  • 
  • Use a hlookup function (yes, a hlookup) to find the appropriate goal size for the goals in the goal column using the table.
  • 
• [15%] Add a column after the currency column
  • Call this Currency Name
  • Using the data in the Currency table lookup the name of the Currency
  • 
  • You may not rearrange the columns on the currency workbook.
• [15%] Add a column after the country column.
  • Call this Country Name
  • Without changing the order of the columns on the Country Codes worksheet, lookup the country name corresponding to the value in the country column.
  • 
• Add a new worksheet called Pledges
  • All work on this sheet is related to the usd pledged column in raw data.
• [5%] Finding Outliers
  • Compute the Q1 and Q3 values for this data.
  • Compute the Inter Quartile Range (IQR) Q3-Q1
  • Compute a possible limit for outlier : Q3+(IQR)*1.5
  • Compute the number of outliers (ie values greater than the limit)
  • 
• [15%] Compute a frequency distribution for the USD pledged data.
  • There should be 8 evenly spaced bins.
  • The low should be 0 and the High should be the High Outer Limit
  • There should be a bin for the outliers.
  • Frequency will use a bin beyond the last bin to hold the count of everything that does not fit into the last bin.
  • 
• [20%] Largest Pledges
  • Find the top three pledgeds and the associated project names.
  • 

Submit