Summing Arrays

[Guest]

I was playing around with these two functions...
=SUM((A1:A10)*(MOD(ROW(A1:A10),2)))
=SUMPRODUCT(A1:A10,MOD(ROW(A1:A10),2))

The first formula being an array function (you have to press
ctrl+shift+enter). With
both of these functions I have to use a specified range, I can't use the
whole colum A:A. Why is this?

 

[Bob Phillips]

To quote MS

The "Entire Column" Rule
Although you can create very large arrays in Microsoft Excel, you cannot
create an array that uses a whole column or multiple columns of cells.
Because recalculating an array formula that uses a whole column of cells
(there 65,536 cells in a column) is a little time consuming, Microsoft Excel
does not allow you to create this kind of array in a formula.

See

http://support.microsoft.com/default.aspx?scid=kb;en-us;166342
Description of the limitations for working with arrays in Excel 2000, Excel
2002, and Excel 2003

for the full article

--

HTH

RP
(remove nothere from the email address if mailing direct)