How to change an absolute reference while copying cells?

  • Thread starter Thread starter Lurka
  • Start date Start date
L

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
 
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)
 
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
 
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))
Click to expand...
....

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))
 
Bob Phillips wrote...
You only need to edit the first in the series, and with the F4 key that is
not too onerous.
Click to expand...
....

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?
 
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
 
Back
Top