Chapter 17, Arrays

• Excel provides arrays.
• The power of these appears to be in the ability to do vector operations.
• Warning
  • I don't like some of the operations we are about to do.
  • They seem to me to be more complex ways to accomplish a task.
  • They also seem to combine many steps into one.
    • Both of these can lead to more errors.
  • Some of these operations will work differently depending on context.
• However
  • These techniques will eventually lead to solutions that are awkward at best with traditional Excel techniques.
  • So just stick with it for now.
• Arrays are collections of data
  • A one dimensional array will contain a set of cells.
    • Each cell has a value and an index.
    • Go to the Sandbox worksheet of thisworksheet.
    • Highlight cells H10:l10
    • After the command, press <Ctrl><Shift>Enter not enter.
    • Enter the command ={"Fred","Sue","Ann","Tom","Bob"}
    • In cell H12 enter Position
    • Put a number between 1 and 5 in cell I12
    • In cell H13 enter Value
    • In cell HI13 enter the function =index(h10:l10,i12)
    • Change the value in I12
    • 
  • A strange result for index
    • If an index value of 0 is given, the entire array is returned.
    • Try a 0 in I12
    • Strange
    • Highlight H15:l15
    • We are entering an array formula so finish with <Ctrl><Shift>Enter
    • Enter the formula =index(h10:l10,0)
  • By the way, ={"Fred","Sue","Ann","Tom","Bob"} build a row array.
  • ={"Ann";"Bob";"Fred";"Sue";"Tom"} will create a column array.
    • Do this somewhere and build a index lookup like before.
    • 
  • A two dimensional array is also possible
    • Highlight H23:J25
    • Enter ={1,2,3;4,5,6;7,8,9} as an array.
    • Add a row and column reference
    • Add a index function to lookup the value.
    • 
• Arrays can be created in the worksheet, or in memory.
  • I would like to compute the sum of my grocery list in cells H2:I5
  • I could compute a cost per line item in column J, then sum this.
  • Or I could do an array operation
  • Enter Total in cell I7
  • In cell j7 enter the following as an array =sum(h2:h5*i2:i5)
  • This does a vector multiplication, then adds the result.
    • The array operation caused the vector multiplication.
  • Note in the formula area it has the following
  • 
  • In this case, the array h2:h5*i2:i5 is stored in memory.
• You can build an array with an equation
• I would like to count the occurrences of different values in column A
  • The range has been named data
  • First, let's extract the unique values.
  • Select Advanced from the Sort & Filter workgroup on the Data tab.
  • Match the following
  • 
  • Sort the resulting data.
  • We will use a countif function to compute the frequency
  • Highlight D3:D53 (the spaces along the new data)
  • Enter =countif(data,c3:c53) as an array formula
• This has just created a new array, the rules are somewhat different.
  • Go to cell d3 and type the letter q
  • On page 397 he lists advantages and disadvantages of using an array like this.
    • It is a good way to make sure all of the formulas are identical.
    • It will keep novice users from changing the values.
    • It will keep you from accidental changing the values.
  • but
    • You can't insert rows into the array, you need to rebuild it.
  • If you need to edit an array
    • You can select an array by selecting a cell
    • The Home then Find & Select select Go To Special
    • 
    • Select Current Array
    • 
  • Arrays can be named just like ranges.
    • Name the newly created array freq_array
    • Use this name to compute the max and min of the array
  • Some functions use arrays
    • The frequency function, covered on pages 311-313 allows you to build a frequency distribution.
    • You must select a target array for this function
    • The first argument is the array (freq_array)
    • The second argument is a set of upper bounds.
    • In cell F47 put Lower
    • In cell G47 put Upper
    • In Cell F48 put the minimum value.
    • Lets make a bin width of 2, so in G48 put F48 +1
    • In F49 put F48 + 2
    • Copy down
    • 
    • In cell H48 put =concatenate(text(f48,0)," - ", text(g48,0))
    • Copy this down.
    • Select I48:i52
    • Enter =frequency(freq_array, G48:G51) as an array function
      • Notice we used the upper range.
      • Notice we did not include the last bin.
    • 
  • Build a bar chart out of this data.