Lurka,

You've gotten an excellent answer from Harlan, but I wanted to offer an
alternative technique, for those with less mathematical ability than Harlan
(which is almost everybody ;-)).

Make up your first formula:

=A1/AVERAGE(A$1:A$10)

and copy it down for the first ten rows.

Then select all ten cells, and do a replace of $ with nothing.

With those cells still selected, copy them, and then in the same column
select from row 11 to row ????, (as long as ???? is a multiple of 10) and
paste.

Your formulas will be pasted just as you want.

If you need to get them back to $ form for some reason, simply select all
the cells and use a first replace of AVERAGE(A with AVERAGE($A and a second
replace of :A with :$A

HTH,
Bernie
MS Excel MVP


"Lurka" <loneluke@hotmail.com> wrote in message
news:1115024739.551105.50530@f14g2000cwb.googlegroups.com...
> Hello,
>
> I want to copy a formula with an absolute reference that needs to be
> changed after a number of cells, like:
>
> =A1/AVERAGE(A$1:A$10) for the first 10 rows
> =A11/AVERAGE(A$11:A$20) for the next 10 rows
> =A21/AVERAGE(A$21:A$30) for the next 10 rows and so on
>
> Is there any way to obtain this result with a single copy operation,
> without having to manually edit the absolute reference?
>
> Thankyou,
> /_urka
>