Copy and Paste of Relative Cell References

[Guest]

I would like assistance in understanding how I can perform a special copy and
paste of a cell which contains a formula with relative cell references. As
an example, I want to copy and paste the cell contents from A1 over to B1.
The formula in A1 contains a data array of D1:F1. When I copy cell A1 and
paste it in B1, the data array now shows E1:G1. I want the relative
references to move over not 1 column, but 3 columns. I want the pasted
formula in B1 to have the data array of G1:I1. I would like to know how to
do this so I don't have to edit the formula in B1 with the G1:I1
references...I'm a bit lazy.

This is a bit of a puzzle to me.

Thanks for the help in advance!

Tom

 

[David Biddulph]

Using an AVERAGE() formula as an example:
In A1 =AVERAGE(OFFSET($D1,0,3*(COLUMN()-COLUMN($A1)),1,3))
Copy across to B1 and C1 (but of course you can't copy any further or else
you'll hit your source data range from D1 onwards).