Generating a range of values.

• We can use arrays time we wish to do a "loop" in excel.
• m:n
  • I can't find an explanation anywhere but he uses this
  • 1:12
  • So grab a new worksheet
  • Fill the first corner as pictured
  • 
  • in cell a6 put =1:1
    • Before you press enter, notice what is highlighted.
    • Copy this across
    • Put the same formula in a7
    • Copy the formula from a7 down.
    • Try the same with =$1:$1
  • Clear the contents
  • Try something else
    • Move to a6 and enter =1:4
    • Ok it doesn't like that
    • #VALUE! says that there is an error in your formula.
    • Apparently we can't do this in a cell.
  • Clear the contents
  • Now try this an an array
    • Highlight A6:d6
    • Enter =1:4 as an array function.
    • Try A7:A10
    • Try A6:D10
  • Just for kicks, highlight f1:f4 and enter =A:A as an array function
• The row and column functions
  • in cell a12 enter =row(a1)
  • in cell a13 enter =row(b3)
  • try =row()
  • Experiment with =column() as well.
  • Try =row(a1:a4)
  • Highlight four cells in a column
  • try =row(1:4) as an array formula
  • They tell us that this is the way to generate a range of numbers
  • It is a common practice in programming languages like python and bash to generate a range and loop over values in that range.
    • In python

    • for x in range(5):
      	print x
      	      

  • Try =row(15:20) as a column array
• A problem:
  • Clear everything out out again.
  • Select a6:a9
  • enter =row(1:4) as an array function
  • Now insert a row anywhere above
  • Not good.
• The indirect function
  • Indirect will read a value from a given cell address
  • The cell given as an argument is the cell that contains the address
    • In cell a7 put a4
    • In cell a8 put =indirect(a7)
    • Change the values in a7
  • Or a string can be given.
    • Try =indirect("c2")
  • & is string concatenation
  • So put a b in cell b10, and a 4 in cell c10
  • In cell b11 put =indirect(b10&c10)
  • Try selecting a column of 4 and enter =indirect("1:4") as an array
• A solution
  • Select a6:a9 again
  • Enter =row(indirect("1:4")) as an array
  • Insert a row above.
  • Try selecting a 5 cell column range
  • enter =row(indirect("7:11")) as an array