Summing Arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sloth - 18 Nov 2005 23:06 GMT

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?

Reply to this Message

Bob Phillips - 18 Nov 2005 23:22 GMT

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

Signature

HTH

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

> I was playing around with these two functions...
> =SUM((A1:A10)*(MOD(ROW(A1:A10),2)))
[quoted text clipped - 4 lines]
> both of these functions I have to use a specified range, I can't use the
> whole colum A:A. Why is this?

Reply to this Message