Homework 9: Looking at Some Burrito Tables

The goals of this homework are:

• Demonstrate your ability to use vlookup.

Instructions

All of these Terry Stops have made you hungry, so you have decided to get lunch. But where. You and your partner really like burritos, but where to eat? You have decided to analyze the burrito dataset.

Please note, I have deleted quite a few fields in this dataset.

This dataset is from kaggle, but the original is from here.

1. Put your name in cell a1 of the About worksheet.
2. The first thing you would like to do is assign a $ rating to the price.
   • Add a new worksheet called Constants
   • Build the following table:
   • 
   • Name your table PriceTable
   • On the Data worksheet
     • Add a column after the Cost column.
     • Label this column Price Range
     • Using the cost and the Price Table, look up the rating for each taco rating.
   • 
3. You are interested in the month when each rating was conducted.
   • While there are other ways to accomplish this task, you must follow my instructions for credit.
   • Insert 2 columns after the Date column.
   • Call these columns Month Number and Month
   • In the Month Number column use the month() function on the cell in column c to compute the month number.
     • Format this using the general format
   • 
   • Build a MonthTable on the Constants worksheet.
     • 
       • Note, you only need to add Jan, Feb, Mar, and drag down.
     • Make sure to name this table.
   • Sort the table alphabetically by month name.
     • you will lose 15 points if you fail to do this.
   • 
   • Using vlookup and the table you just built, look up the name of each month in the Month Number field of the Data worksheet. Place this in the Month field.
   • 
4. Finally, you want to add the ability for raters to be anonymous.
   • Fill in the blanks in the Reviewer column with NA.
   • On the Constants worksheet create a unique list of reviewer names.
   • 
   • Count the number of review each reviewer provided.
   • 
   • Add a Randomization column.
     • Follow these steps to randomize the order of the names.
     • Put =rand() in this column.
     • This will generate a random number each time the worksheet recalculates.
     • Don't worry, your results will not match mine.
     • Sort on the new random column.
       • Don't worry that the random column is not in order after the sort.
     • Your results WILL NOT MATCH MINE
   • Add an id column and fill it with sequential numbers.
   • 
   • After this
     • The names of the reviewers are in random order.
     • Each reviewer has a unique id.
   • Name your table ReviewerTable
   • Back in the Data worksheet
   • Use the table you just constructed to Add a Reviewer ID field which contains the Reviewer ID corresponding to the Reviewer in column I.
   • 

Submit