it's still an array.

Entered like this and array entered in one cell, the formula

=(ROW(A10:A20))

will still return just the first result of the array, i.e. the value 10. If several cells are selected and the formula is entered into the first cell and then array entered, the second cell will show the value 11, the next one 12, etc. The outer round braces can be omitted for that exercise.

The expression (ROW(A10:A20)) only returns an array of the numbers 10 to 20 if the formula it is used in is either array entered with Ctrl-Shift-Enter, or if the formula it is used in is in itself an array formula.

Compare

=sum(ROW(A10:A20)) with simple Enter

=sum(ROW(A10:A20)) with Ctrl-Shift-Enter

and

=sumproduct(ROW(A10:A20)) with simple Enter.

Sumproduct() is inherently an array function and will interpret (ROW(A10:A20)) as an array, even if confirmed with a simple Enter. Sum() however, is not an array function and to make it regard (ROW(A10:A20)) as an array instead of just the first value, it needs to be array entered.

There are many possibilities to exploit this behaviour and help create shorter and more concise formulas.

Also of note is that named formulas, i.e. the formulas entered to define a named range are

**always**array formulas. A named range "MyRange" with the formula

=SUM(ROW(Sheet1!A10:A20))

Entered into a worksheet cell like

=MyRange

this will return the sum of the row numbers. If the range is defined with relative references, as in the example, the result will be different, depending on what row the =MyRange name is called upon. This can be a VERY powerful tool, since very often, helper calculation cells in worksheets can instead be carried out by such range names.

Good resources for learning more about arrays are Chip Pearson's site and Daniel Ferry's ExcelHero site.

http://www.cpearson.com/Excel/MainPage.aspx

http://www.excelhero.com/

cheers, teylyn