dragging a formula down a column

[Guest]

I need to get the same range of values as I drag a formula down. For example
in cell A1 I might have the formula =sum(D13). When I drag the formula in
cell A1 down to A2 I want the range to change to (D47) not (D25). I
believe I need to use the offset function but I am confused by it. Please
help.

 

[Biff]

Hi!

Try this:

=SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3))

The results will be:

A1 = SUM(D13)
A2 = SUM(D46)
A3 = SUM(D79)
etc

Biff

 

[Guest]

Thanks Biff for the reply. It worked. I was hoping u could give an
explanation of how it works so I can apply it to other formulas.

 

[Biff]

Hi!

=SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3))

We're using the Offset function to determine the range argument for the Sum
function.

OFFSET(D$1,(ROWS($1:1)-1)*3,,3)

D1 is the starting point.

So, we want to offset D1 by an amount of rows.

The number of rows is calculated using:

(ROWS($1:1)-1)*3

The first cell that this formula is entered into, the number of rows to
offset D1 by is calculated to be zero:

(ROWS($1:1)-1)*3

Evaluates to:

(1-1)*3 = 0

As you drag copy the formula down a column the ROWS() argument will
increment and calculate a different row offset.

(ROWS($1:2)-1)*3

Evaluates to:

(2-1)*3 = 3

That would then mean to offset D1 by 3 rows (D4)

Biff

 

[Guest]

Thanks a bunch

Hi!

=SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3))

We're using the Offset function to determine the range argument for the Sum
function.

OFFSET(D$1,(ROWS($1:1)-1)*3,,3)

D1 is the starting point.

So, we want to offset D1 by an amount of rows.

The number of rows is calculated using:

(ROWS($1:1)-1)*3

The first cell that this formula is entered into, the number of rows to
offset D1 by is calculated to be zero:

(ROWS($1:1)-1)*3

Evaluates to:

(1-1)*3 = 0

As you drag copy the formula down a column the ROWS() argument will
increment and calculate a different row offset.

(ROWS($1:2)-1)*3

Evaluates to:

(2-1)*3 = 3

That would then mean to offset D1 by 3 rows (D4)

Biff