Array Formulas (see associated workbook for all examples)

1)Essence of Array Formulas:

  1. One or the other can be “the array”:
  2. You are simultaneously entering a Function into more than one cell
  3. You have a formula that is making calculations across ranges or arrays instead of individual cells
  4. You use Ctrl + Shift + Enter keys to enter an Array formula
  5. { } brackets will appear after you use Ctrl + Shift + Enter keys to enter an Array formula
  6. It is sort of like magic, you can not type in the brackets, they just appear
  7. If you need to edit an array formula, highlight the range or cell and hit the F2 key, edit, then hit Ctrl + Shift + Enter again.

2)What does “Array” mean in the phrase “Array Formula”?

  1. You have highlighted more than one cell and you are entering a function into all the cells at once using the keyboard sequence “Ctrl + Shift + Enter (CSE)
  2. Example: TRANSPOSEfunction in your formula
  3. You want to take the 4 vertically arranged cells with “Love” and have them arranged horizontally and also have a link (Figure 1). Highlight the same number of vertical cells in a horizontal direction

Figure 1

  1. Type your function (Figure 2):

Figure 2

  1. Highlight the cells you want to transpose (Figure 3):

Figure 3

  1. Type the closing “)”, then hold the Ctrl key and the Shift key and tap the Enter key (Figure 4):

Figure 4

  1. Now you can make changes to the vertical range and the licked cells will change(Figure 5):

Figure 5

  1. Other Examples of Array Functions are:
  2. FREQUENCY
  3. Used in statistics for frequency tables and histograms
  4. LINEST
  5. Used to return a myriad of Linear Regression related statistics
  1. You have one cell highlighted, and you are making Excel do a calculation across two ranges
  2. Example: Units * Price/Unitformula
  3. Your goal is to multiply all the units and prices to obtain total Revenue in ONE CELL! Type in the formula as seen in Figure 6:

Figure 6

  1. Then CSE

3)Array as different from range:

  1. Array:
  2. 1, 2, 3 or more rows crossed by 1, 2, 3 or more columns arranged as an argument in a function or stored in memory in other ways
  3. Columns are separated by , (comma)
  4. Rows are separated by ; (semicolon)
  5. =VLOOKUP(2, {1,Red;2,Blue;3;Yellow},2) will return “Blue”
  6. In essence, in this example, you can simulate what would have been a range in a number of cells. Compare the two ways of returning the word “Blue” by looking up the number “2”:
  7. See accompanying workbook (Figure 7):

Figure 7

  1. Key: IT Does not have to exist on a worksheet as seen in the VLOOKUP example
  2. Often a memory array
  1. Range:
  2. Array located on a worksheet, example A1:A6

4)Other Examples:

  1. Sum on three conditions is not possible with the SUMIF function (although in Excel 2007 there will be a new function called SUMIFS – but many businesses will not adopt Office 2007 anytime soon)
  2. To Sum with multiple conditions, we can use an Array formula like this (here, array means that actual ranges of cells are being evaluated in addition to just single cells) (Figure 8):

Figure 8

  1. To see what is going on, click in cell G14 and hold the Alt Key while successively hitting the keys T, U, F (Alt + TUF) to view Formula Evaluator. When you evaluate each step (by clicking Evaluate) you can see the arrays and that the arrays are coming out each as true or false. Each evaluate will take a long time, but do each one and you will get the real meaning of what is happening behind the scenes with a big array formula. Also use the scroll bar to view the whole array.
  1. Boolean Math (Figure 9):

Figure 9

  1. Here the Array formula is being used with Boolean Math. The four ranges are being multiplied and only where there is a True*True*True*SalesAmount will there be a number other than 0 to add.
  2. I have included two worksheets that have some info about Boolean Math and formulas
  1. There is also a sheet that has an array formula for Standard Deviation

ArraysPage 1 of 3