Re: how to increment cell formula by 5 rather than 1

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Takeadoe" <mtonkovich@xxxxxxx> wrote in message
news:35d78cf9-a48c-42a5-859c-524c890abb9d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Earl - Hey, thanks so much for taking time out of your busy schedule
to help me out. I couldn't get that to work as constructed. The
problem was the "5" in the formula. I had to change that to a 1 and
write this ugly monster.

=SLOPE(((OFFSET('PVT Antlered Hrvst 5-years'!$D$10,ROW()*10-10,0,1,1)):
(OFFSET('PVT Antlered Hrvst 5-years'!$D$10,ROW()*10-6,0,1,1))),
((OFFSET('PVT Antlered Hrvst 5-years'!$B$10,ROW()*10-10,0,1,1)):
(OFFSET('PVT Antlered Hrvst 5-years'!$B$10,ROW()*10-6,0,1,1))))

It get's the job done, but man is it ugly!
--------------------------------------------------------------------------------------------

It's not just ugly, it's dangerous, as it's uncheckable, and anyone else
following on from you will not be able to follow it. (That's probably you in
3 months time!) Assuming that maintainability and error-tracking are
important to you, I would not do it that way.

The simplest way is to make a cross-reference to the data on the same row as
the slope formula. So, I would put
in G1 =INDIRECT("B"&5*ROW()+5)
in H1 =INDIRECT("B"&5*ROW()+5+1)
in I1 =INDIRECT("B"&5*ROW()+5+2)
J1 similar
K1 similar
L1 =INDIRECT("D"&5*ROW()+5)
etc

It's then very easy to check that these are pointing to the correct data in
cols B &D, and the slope function then just refers to G1:K1 etc and can then
be copied down easily.



The other way is to write a simple macro to enter the formulae for you, such
as the following:

Sub Macro1()

For ii = 1 To 88

'Cell to enter formula into
Myrange = "A" & ii

'Formula required is =SLOPE(D10:D14,B10:B14)
Mytarget1 = "D" & 10 * ii
Mytarget2 = "D" & 10 * ii + 4
Mytarget3 = "B" & 10 * ii
Mytarget4 = "B" & 10 * ii + 4
Myformula = "=SLOPE(" & Mytarget1 & ":" & Mytarget2 & "," & Mytarget3 & ":"
& Mytarget4 & ")"

Range(Myrange).Formula = Myformula

Next

End Sub

This is very easy to alter if the data or your requirements change, and a
few comments should make it intelligible in a couple of months' time.
HTH



.