How to change an absolute reference while copying cells?

[Lurka]

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

[Bob Phillips]

You only need to edit the first in the series, and with the F4 key that is
not too onerous.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"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
>

[Max]

Perhaps one way to get it to fill down directly from the starting cell

Instead of say, in the starting cell B1: =A1/AVERAGE(A$1:A$10)

Try in B1:

=A1/AVERAGE(INDIRECT("A"&(INT((ROWS($A$1:A1)-1)/10)+1)*10-10+1&":A"&(INT((RO
WS($A$1:A1)-1)/10)+1)*10-10+10))

Copy B1 down as desired

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"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
>

[Harlan Grove]

Max wrote...
>Perhaps one way to get it to fill down directly from the starting cell
>
>Instead of say, in the starting cell B1: =A1/AVERAGE(A$1:A$10)
>
>Try in B1:
>
>=A1/AVERAGE(INDIRECT("A"&(INT((ROWS($A$1:A1)-1)/10)+1)*10-10+1
>&":A"&(INT((ROWS($A$1:A1)-1)/10)+1)*10-10+10))
....

First a quibble. Algebraic simplification is a GOOD THING.

(INT((ROWS($A$1:A1)-1)/10)+1)*10-10+1 == INT((ROWS($A$1:A1)-1)/10)*10+1

and

(INT((ROWS($A$1:A1)-1)/10)+1)*10-10+10 ==
INT((ROWS($A$1:A1)-1)/10)*10+10

The more unnecessary terms there are in a formula, the greater the
opporunity to make typos. It's the diminished opportunity for typos
that's the real reason shorter formulas are almost always much better
than long formulas.


This is an excellent example of when *NOT* to use INDIRECT. FAR SIMPLER
to use OFFSET for deriving ranges based on geometric relationships of
cells.

=A1/AVERAGE(OFFSET(A1:A10,MOD(1-ROW(A1),-10),0))

or

=A1/AVERAGE(OFFSET(A$1:A$10,INT((ROW(A1)-1)/10)*10,0))

Due to brevity, I prefer the MOD variant.

On the assumption that INDEX and ROW aren't volatile, this could be
done in a nonvolatile way using

=A1/AVERAGE(INDEX(A:A,INT((ROW(A1)-1)/10)*10+1)
:INDEX(A:A,INT((ROW(A1)-1)/10)*10+10))

[Harlan Grove]

Bob Phillips wrote...
>You only need to edit the first in the series, and with the F4 key
that is
>not too onerous.
....

And take your vitamins too!

You missed the 'and so on'. If the OP needs to do this down to row
20000, that's 4000 edits. When does it become onerous?

[Bernie Deitrick]

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
>

[Max]

Dazzling ! Thanks, Harlan.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Lurka]

Thank you very much to everybody.
Excel is really amazing. ^-^