a twist to relative ranges
 

I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down one
row, but I want the data collected to move down by three rows. My question
is this: Is there a way to write the function in cell B1 so that I can copy
it and paste it to cell B2 and on down without having to re-write the range
of cells in the parentheses?

Thank you!

a twist to relative ranges
 

Ray

In B1 enter =SUM(OFFSET($A$1,ROW()*3-3,0,3,1))

Drag/copy down to B4


Gord Dibben MS Excel MVP

On Fri, 16 Feb 2007 15:45:32 -0800, Ray Pendergast
wrote:

I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down one
row, but I want the data collected to move down by three rows. My question
is this: Is there a way to write the function in cell B1 so that I can copy
it and paste it to cell B2 and on down without having to re-write the range
of cells in the parentheses?

Thank you!

a twist to relative ranges
 

=SUM(OFFSET($A$1,(ROW(A1)-1)*3,0,3,1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ray Pendergast" wrote in message
...
I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down
one
row, but I want the data collected to move down by three rows. My
question
is this: Is there a way to write the function in cell B1 so that I can
copy
it and paste it to cell B2 and on down without having to re-write the
range
of cells in the parentheses?

Thank you!

a twist to relative ranges
 

You could try:

=SUM(OFFSET(B1,ROW()-ROW($B$1),-1,3))

change B1 to whatever cell you enter the formula in.

"Ray Pendergast" wrote:

I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down one
row, but I want the data collected to move down by three rows. My question
is this: Is there a way to write the function in cell B1 so that I can copy
it and paste it to cell B2 and on down without having to re-write the range
of cells in the parentheses?

Thank you!

a twist to relative ranges
 

Somewhere along the way the *2 got left out. Also, after seeing others
suggestions, changed it to offset from A1 so the column offset doesn't have
to be hardcoded.

=SUM(OFFSET(A1,(ROW()-ROW($B$1))*2,0,3))

"JMB" wrote:

You could try:

=SUM(OFFSET(B1,ROW()-ROW($B$1),-1,3))

change B1 to whatever cell you enter the formula in.

"Ray Pendergast" wrote:

I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down one
row, but I want the data collected to move down by three rows. My question
is this: Is there a way to write the function in cell B1 so that I can copy
it and paste it to cell B2 and on down without having to re-write the range
of cells in the parentheses?

Thank you!

a twist to relative ranges
 

Another way ... non-volatile,
Can be entered anywhere, and copied down to total every 3 rows of the column
referenced in the Index() portion of the formula:

=SUM(INDEX(A:A,3*ROWS($1:1)-2):INDEX(A:A,3*ROWS($1:1)))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ray Pendergast" wrote in message
...
I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down
one
row, but I want the data collected to move down by three rows. My
question
is this: Is there a way to write the function in cell B1 so that I can
copy
it and paste it to cell B2 and on down without having to re-write the
range
of cells in the parentheses?

Thank you!