add different parts of spreadsheet

add different parts of spreadsheet

Post by <yos.. » Thu, 10 Jul 2003 23:51:12



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

 
 
 

add different parts of spreadsheet

Post by <yos.. » Thu, 10 Jul 2003 23:52:41



> 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

 
 
 

add different parts of spreadsheet

Post by Harlan Grov » Fri, 11 Jul 2003 01:31:54


..
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.

 
 
 

add different parts of spreadsheet

Post by Jeff Geisl » Fri, 11 Jul 2003 05:50:17




> > 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!
 
 
 

1. compare data in two different spreadsheets

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
any ideas please ??

2. offsetof and classes

3. 2 Spreadsheets give different answers on Excel 7??

4. software piracy

5. Adding sums from different sheets in Quattro Pro?

6. Oracle connection problem (ASP.NET ADO.NET OLEDB)

7. Excel 97 - Adding/Summing the same cell in different worksheets

8. How Oracle determines the use of an index ?

9. Excel 4.0 Adding curves with different X-axis

10. HOw to add different charge for weekend cost in resources

11. Add different cases

12. my spreadsheet's not adding up....

13. Is there a way to add a sum routine to an excel spreadsheet.