Summing Arrays
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? |
Summing Arrays
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...b;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) "Sloth" wrote in message ... 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? |
All times are GMT +1. The time now is 03:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com