Homework 2: An array of problems.

• Provide practice in using array formulas.

1. Download this document.
   • The RawData tab contains part of the Taco Bell's Nutrition Information.
2. [10%] Insert an About tab and maintain appropriate information.
3. [40%] Create a new tab called Warmup
   1. In cells A1 through B28, use a formula to copy the cells from the Raw Data table.
      • Show Help
      Use the index function to do this.
      • Takes three arguments
      • The source, data
      • The rows: ROW(data) will return all the rows in an array
      • The You want columns 1:2, so any variation of that.
      
   2. Just for practice calculate some statistics on the name lengths.
      1. In B32 find the average letter count for all names combined
      2. In B33 find the maximum name length.
      3. In B34 find the minimum name length.
      4. 
   3. List the three top and three bottom values of calories in D31:E34
      • 
      • Show Help
        
   4. Build a table of the five number summary in A36:F47.
      • 
      1. Build a list of the attribute names with an array function.
         • Show Help
           
      2. Add the column headings (min ... max)
      3. In the min column, compute the min
         • Show Help
           • min function
           • Of the index of
             • data
             • don't care about the row, want the entire column returned, so blank.
             • I want B:L for the columns
               • But I want this in an array
               • And I want to add one to it.
               • 
      4. Compute the quartiles.
         • Use a single array formula.
         • Show Help
           • Build the data reference as before,
           • Use an array for the quartile you wish to compute
           • 
      5. Finally, compute the max with a single array function.
   5. [5%] Compute the number of different calorie values
      • 
4. Build a worksheet to compute the nutritional information for an order.
   1. 
   2. Add a new workstheet called Order
   3. Enter the headings in row 1
   4. Enter the values in columns A and B from the screenshot
      • By entering a row number in column a and a quantity in column B the totals in row 12 and the table to the right should change.
   5. [20%] In C2:K11 add an array formula to look up the values for the item in column a.
      • Show Help
        • Array functions can not use logical functions (and, or, ...)
        • Use * for and
        • Build this up slowly, it is a long expression
        • if (A2:A11 is a number and bigger than 1, return a row (index function), otherwise return "")
        • You might want the isnumber function.
        • A2:A11 yield the row number for the index function.
        • The column will be a columnm(indirect)) as in the previous exercise
        • 
   6. [10%] In C12 compute the total (quantity * calories per item) with an array formula
      • A sum
      • With an if (isnumber, computation, 0) inside
      • Copy this to D12:K12
   7. [25%]Finally, using two array formulas construct the chart N2:O10

Submit