Arrays

• This is chapter 18.
• Let's use the subway menu again.
• You can do some tricky cool stuff with arrays.
  • But they are different from everything else.
• Grab a new worksheet
  • Highlight A1:C1
  • type ={1,2,3} and press ctrl-shift-enter
  • Now try to change the value in any one cell.
  • Highlight A2:A4
  • Type ={1;2;3} and press ctrl-shift enter.
  • Try something horizontal with ;
  • Try something vertical with ,
  • Highlight A6:C8
  • Type {1,2,3;"A","B","C";1.1, 2.2, 3.3} and press ctrl-shift-enter
• These are all examples of arrays.
  • , separates values in rows.
  • ; separates rows
• Let set up a silly bill of sale
  • 
  • Highlight D13:D15
  • Enter =B13:B15*C13:C15 and press ctrl-shift-enter
  • Notice this multiplies each entry by each other entry to produce an array of results, which it stores in the cells.
  • In C16 enter =sum(B13:B15*C13:C15) as a normal function
  • Notice it is not happy with this.
  • Now try to enter this as ctrl-shift-enter
  • Notice, in this case it "performs" a vector operation
    • Pairwise multiply the two ranges
    • Then sum the result.
  • The last activity could have been accomplished with the function sumproduct
• We need to be careful and pay attention when doing vector operations
  • Again, select a range of three cells and enter =B13:B15*C13:C15
  • This time do not press ctrl-shift-enter, just enter
  • What is the result.
• A simple first application is with quartiles.
  • grab a new worksheet.
  • Label A1:A5 with Min, Q1, Q2, Q3, Max
  • Highlight B1:B5
  • enter =Quartile.inc(calories,{0;1;2;3;4}) use ctrl-shift-enter
  • Can you use a similar technique to find the five items with the most sodium
  • Just as a quick refresher, find the names of those items.
  • 
• This is kind of silly but
  • Next to the names you just entered add a column that computes the length of each name.
  • Now total those
  • 
  • My names are in C9:C13
  • In a cell enter =sum(len(c9:c13)) with crtl-shift-enter
  • Notice this will compute the sum
    • First it will build an array of lengths of cells
    • Then total that array
• Select five horizontal cells
  • enter =transpose(c9:c13) with enter
  • Repeat with ctrl-shift-enter
• One silly trick to send you on your way.
  • Try =row() alone
  • Try =row(c4)
  • Select 5 vertical cells and enter =row(1:5) as an array
  • Try the same with column.
  • Row and column give us a nice way to generate a sequential array.
  • Next put 5 in cell a30
    • This could be user input, their result of a computation or anything.
  • Build the string 1:5 (based on the value of cell a30) in A31
  • Now select c30:c34 and enter =row(indirect(a31)) as an array
  • Ok,now we can build an array from a text value.
  • Can you compute the sum of the top n items with sodium using this, where n is the value in A30
• Some other things:
  • ={1,2,3,4,5}^2
  • Index will work on an array.
  • =index({5,6,7},2)
  • Just wierd
  • put 1,2,3,4,5 in a range of cells
  • =sum((f19:F23>2)*1)
  • You need the *1 because of < href="http://dailydoseofexcel.com/archives/2004/12/04/logical-operations-in-array-formulas/">this
• He has eliminated a second chapter on array processing from the 2016 book.
• I am not sure about the value of arrays.