Simple Formula Excel 2003

[BJ&theBear]

Can anyone give me some suggestions as to resolve a problem
I want to create a formula that can be dragged down without using a
macro
For all you Excel gurus this is rather a pathetic question but I do
not seem to be able to solve it

What I want to do in cell
c1 = sum(a1:a7)
c2 = sum(a8:a15)
c3 = sum(a16:a23)

c4 etc etc

Can anyone point me in the right direction

Thanks

Brian
Scotland

 

[Jim Cone]

Well that depends...
Are you summing a repeating set of: 7 cells, 8 cells, 8 cells ?
-or-
The set size is always 8 cells ?

If the latter then use: =SUM(OFFSET($A$1,(ROW()-1)*8,0,8,1))
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware



"BJ&theBear" <[email protected]>
wrote in message
Can anyone give me some suggestions as to resolve a problem
I want to create a formula that can be dragged down without using a
macro
For all you Excel gurus this is rather a pathetic question but I do
not seem to be able to solve it

What I want to do in cell
c1 = sum(a1:a7)
c2 = sum(a8:a15)
c3 = sum(a16:a23)

c4 etc etc

Can anyone point me in the right direction

Thanks

Brian
Scotland

 

[BJ&theBear]

Well that depends...
Are you summing a repeating set of:  7 cells, 8 cells, 8 cells ?
-or-
The set size is always 8 cells ?

If the latter then use: =SUM(OFFSET($A$1,(ROW()-1)*8,0,8,1))
--
Jim Cone
Portland, Oregon  USAhttp://www.mediafire.com/PrimitiveSoftware

"BJ&theBear" <[email protected]>
wrote in messageCan anyone give me some suggestions as to resolve a problem
I want to create a formula that can be dragged down without using a
macro
For all you Excel gurus this is rather a pathetic question but I do
not seem to be able to solve it

What I want to do in cell
c1 = sum(a1:a7)
c2 = sum(a8:a15)
c3 = sum(a16:a23)

c4 etc etc

Can anyone point me in the right direction

Thanks

Brian
Scotland

Thank you - this worked perfectly

Can you tell me how it tracks which lines to add up as I have never
used offset or row before

Thanks again
Brian

 

[Jim Cone]

There is explanations in Excel help for both Row() and Offset()...

Row returns a number that is the row number of the cell with the formula. (in our use of it)

Offset does two things:
1. It provides a reference that is x rows by y columns away from a specified cell, so...
Offset(A1, 3, 3) returns D4
2. It resizes the returned range to i rows by j columns, so...
Offset(A1, 3, 3, 3, 3) returns range D4:F6 (a range that is 3 rows by 3 columns)
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(30+ ways to sort - a review)

..
..

"BJ&theBear" <[email protected]>
wrote in message

Well that depends...
Are you summing a repeating set of: 7 cells, 8 cells, 8 cells ?
-or-
The set size is always 8 cells ?

If the latter then use: =SUM(OFFSET($A$1,(ROW()-1)*8,0,8,1))
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware

"BJ&theBear" <[email protected]>
wrote in messageCan anyone give me some suggestions as to resolve a problem
I want to create a formula that can be dragged down without using a
macro
For all you Excel gurus this is rather a pathetic question but I do
not seem to be able to solve it

What I want to do in cell
c1 = sum(a1:a7)
c2 = sum(a8:a15)
c3 = sum(a16:a23)

c4 etc etc

Can anyone point me in the right direction

Thanks

Brian
Scotland

Thank you - this worked perfectly

Can you tell me how it tracks which lines to add up as I have never
used offset or row before

Thanks again
Brian

 

[BJ&theBear]

There is explanations in Excel help for both Row() and Offset()...

Row returns a number that is the row number of the cell with the formula.(in our use of it)

Offset does two things:
1.  It provides a reference that is x rows by y columns away from a specified cell, so...
   Offset(A1, 3, 3) returns D4
2. It resizes the returned range to i rows by j columns, so...
   Offset(A1, 3, 3, 3, 3) returns range D4:F6 (a range that is 3 rowsby 3 columns)
--
Jim Cone
Portland, Oregon USAhttp://www.contextures.com/excel-sort-addin.html
(30+ ways to sort - a review)

.
.

"BJ&theBear" <[email protected]>
wrote in message






Thank you - this worked perfectly

Can you tell me how it tracks which lines to add up as I have never
used offset or row before

Thanks again
Brian

Thanks Jim - I apprecaite your help

Brian
Scotland