Changing AutoFill cell reference (WindowsXP, Office 2003)

[pssureshbabu]

Hello all!

I notice my AutoFill option do like this:

=SUM(A1:A5)

After dragging down using autofill, the result is

=SUM(A2:A6)
=SUM(A3:A7)

is it possible to program autofill to do the following?

=SUM(A6:A10)
=SUM(A11:A15)

and so on...

thanks for your help,

Babu

 

[Andrew Poulsom]

You would need a formula like these:

=SUM(INDEX(A:A,((ROW(A1)-ROW(A$1))*5)+1):INDEX(A:A,((ROW(A1)-ROW(A$1))*5)+5))

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

The INDEX formula is longer, but it's not volatile like OFFSET is.

 

[pssureshbabu]

Thanks ya!

It works.

I would really appreciate if you could help me my other question in this forum.

http://www.mrexcel.com/board2/viewtopic.php?t=236372

Babu