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
"Jason R" wrote in message
...
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" wrote:
Hi!
Try this:
=SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3))
The results will be:
A1 = SUM(D1:D3)
A2 = SUM(D4:D6)
A3 = SUM(D7:D9)
etc
Biff
"Jason R" <Jason wrote in message
...
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(D1:D3). When I drag the
formula
in
cell A1 down to A2 I want the range to change to (D4:D7) not (D2:D5). I
believe I need to use the offset function but I am confused by it.
Please
help.