Hello all,

I have a spread sheet which is long vertically.  It is structured such
that a smaller table of the same structure is repeated down.  I want to
add elements of each table and come up with an aggregate of numbers with
the same structure of table (did I explain well?)

I can do
=sum(a1,a125,a249,a373,...)
like that, but kind of tedious.

I thought array formula like
{=sum(offest(\$a\$1,{0,1,2,3,..}*124,0))}
would work, but it seems like it is picking only the first element, not
the sum of all tables.  I also transposed the array to {0;1;2;3..}, and
I also tried to have those increasing sequence of integer into a part of
spreadsheet and referenced to it, but it does the same.

So my questions are
1) Why my array formula does not work in the way I intended (the regular
sum(a1,a125,..) formula)

More importantly,
2) What would you recommend to do this kind of aggregation?  If possible
I want to avoid writing macro, as I often forget to run macro when I
update data.

Thank you very much,

yosuke kimura

--
yosuke kimura
Center for Energy and Environmental Resources
The Univ. of Texas at Austin, USA

> I have a spread sheet which is long vertically.  It is structured such
> that a smaller table of the same structure is repeated down.  I want to
> add elements of each table and come up with an aggregate of numbers with
> the same structure of table (did I explain well?)

Sorry, forgot to tell you that I use Microsoft Excel 2000.

--
yosuke kimura
Center for Energy and Environmental Resources
The Univ. of Texas at Austin, USA

..
Quote:>I can do
>=sum(a1,a125,a249,a373,...)
>like that, but kind of tedious.

>I thought array formula like
>{=sum(offest(\$a\$1,{0,1,2,3,..}*124,0))}
>would work, but it seems like it is picking only the first element, not
>the sum of all tables.  I also transposed the array to {0;1;2;3..}, and
>I also tried to have those increasing sequence of integer into a part of
>spreadsheet and referenced to it, but it does the same.

..

This is an obscure and undocumented area in Excel's array semantics (as if
Microsoft provides any documentation of array semantics). Purely empirically, it
appears that when you give OFFSET array 2nd or 3rd arguments and 1 as both 4th
and 5th arguments explicitly or implicitly, it returns an array of range
references (apparently, so does INDIRECT when given an array argument). Arrays
of ranges may be entered directly into ranges, in which case they evaluate as
expected. However, when used as operands or function arguments in larger
formulas, Excel evaluates them oddly, neither as arrays of values nor as
multiple area ranges. The trick is to convert arrays of range references to
arrasy of the corresponding values.

The work-around is to enclose OFFSET inside N or CELL("Contents",.), e.g.,

=SUM(N(OFFSET(\$A\$1,{0,1,2,3,..}*124,0)))

or

=SUM(CELL("Contents",OFFSET(\$A\$1,{0,1,2,3,..}*124,0)))

The advantage of CELL("Contents",.) is that it returns both numbers and text.
Noet, though, that the following also works

=SUM(SUM(OFFSET(\$A\$1,(ROW(\$1:\$25)-1)*4,0,1,1)))

but

=SUM(0+OFFSET(\$A\$1,(ROW(\$1:\$25)-1)*4,0,1,1))

or using similar standard numerical coercions returns #VALUE!

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.

> > I have a spread sheet which is long vertically.  It is structured such
> > that a smaller table of the same structure is repeated down.  I want to
> > add elements of each table and come up with an aggregate of numbers with
> > the same structure of table (did I explain well?)

This sounds like a job for...PivotTables!

all I am trying (never done this before) to compare two spreadsheets,
each spread contains letters and nubers in every cell. After that I
would like it to create a 3rd spread which would list the diff from
the compare.

I have been told I can do this in a "excel vba add-in" ?? anybody have