Relative Cell Reference

The fill handle allows me to create a function in a cell (a1) and drag it to
cell (a2). With relative referencing the cells referenced in my function
will also move a single row down.

Is there a way to use relative referencing to move a single row down, but
change the function's reference by 5 units?

ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

all I seem to be able do is to get a2 to say sum(b2:b6)

> ie function in cell a1 is sum(b1:b5)
> I want cell a2 to say sum(b6:b10)

One way to achieve it ..

Put in A1:
=SUM(OFFSET(INDIRECT("B"&ROW(A1)*5-5+1),,,5))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
> The fill handle allows me to create a function in a cell (a1) and drag it to
> cell (a2). With relative referencing the cells referenced in my function
> will also move a single row down.
>
> Is there a way to use relative referencing to move a single row down, but
> change the function's reference by 5 units?
>
> ie function in cell a1 is sum(b1:b5)
> I want cell a2 to say sum(b6:b10)
>
> all I seem to be able do is to get a2 to say sum(b2:b6)

> ie function in cell a1 is sum(b1:b5)
> I want cell a2 to say sum(b6:b10)

One way to achieve it ..

Put in A1:
=SUM(OFFSET(INDIRECT("B"&ROW(A1)*5-5+1),,,5))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
> The fill handle allows me to create a function in a cell (a1) and drag it to
> cell (a2). With relative referencing the cells referenced in my function
> will also move a single row down.
>
> Is there a way to use relative referencing to move a single row down, but
> change the function's reference by 5 units?
>
> ie function in cell a1 is sum(b1:b5)
> I want cell a2 to say sum(b6:b10)
>
> all I seem to be able do is to get a2 to say sum(b2:b6)

And of course, the non-volatile alternative:

=SUM(INDEX(B:B,5*ROWS($1:1)-4):INDEX(B:B,5*ROWS($1:1)))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Max" <demechanik@yahoo.com> wrote in message
news:752FF333-CF4B-413B-BD8C-CCABAED520F9@microsoft.com...
> > ie function in cell a1 is sum(b1:b5)
> > I want cell a2 to say sum(b6:b10)
>
> One way to achieve it ..
>
> Put in A1:
> =SUM(OFFSET(INDIRECT("B"&ROW(A1)*5-5+1),,,5))
> Copy down
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Keith" wrote:
> > The fill handle allows me to create a function in a cell (a1) and drag
it to
> > cell (a2). With relative referencing the cells referenced in my
function
> > will also move a single row down.
> >
> > Is there a way to use relative referencing to move a single row down,
but
> > change the function's reference by 5 units?
> >
> > ie function in cell a1 is sum(b1:b5)
> > I want cell a2 to say sum(b6:b10)
> >
> > all I seem to be able do is to get a2 to say sum(b2:b6)