Good morning group.

Formula in Cell F1 =SLOPE(D10:D14,B10:B14)
Formula in Cell F2 =SLOPE(D20:D24,B20:B24)
Formula in Cell F3 =SLOPE(D30:D34,B30:B34)
Formula in Cell F88=....

Here's the problem. A simple copy formula down operation will not
work for filling in cells F4-F88. What I end up with is this:

Formula in cell F4=SLOPE(D31:D35,B31:B35)
It is incrementing by 1, rather than the 10 that I need it to
(SLOPE(D40:D44,B40:B44). I will need to do this several more times
(grab various numbers of years (10, 15) to generate slope estimates.
Thus if there is something that requires a bit of work on the front
end that will allow me to easily make changes to the number of years
being used in the calculations, I'm all for it.

Any help on this matter is very much appreciated.

Mike

Takeadoe,

If no one comes up with a less geeky way, use this:

=SLOPE(OFFSET($D$10,ROW()*10-10,0,5,1), OFFSET($B$10,ROW()*10-10,0,5,1) )
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Takeadoe" <mtonkovich@msn.com> wrote in message
news:92f39f32-fe70-46da-ac16-1c407cd4071b@y38g2000hsy.googlegroups.com...
> Good morning group.
>
> Formula in Cell F1 =SLOPE(D10:D14,B10:B14)
> Formula in Cell F2 =SLOPE(D20:D24,B20:B24)
> Formula in Cell F3 =SLOPE(D30:D34,B30:B34)
> Formula in Cell F88=....
>
> Here's the problem. A simple copy formula down operation will not
> work for filling in cells F4-F88. What I end up with is this:
>
> Formula in cell F4=SLOPE(D31:D35,B31:B35)
> It is incrementing by 1, rather than the 10 that I need it to
> (SLOPE(D40:D44,B40:B44). I will need to do this several more times
> (grab various numbers of years (10, 15) to generate slope estimates.
> Thus if there is something that requires a bit of work on the front
> end that will allow me to easily make changes to the number of years
> being used in the calculations, I'm all for it.
>
> Any help on this matter is very much appreciated.
>
> Mike
>
>

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!

On Apr 28, 11:22*am, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Takeadoe,
>
> If no one comes up with a less geeky way, use this:
>
> =SLOPE(OFFSET($D$10,ROW()*10-10,0,5,1), *OFFSET($B$10,ROW()*10-10,0,5,1) )
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------"Takeadoe" <mtonkov...@msn.com> wrote in message
>
> news:92f39f32-fe70-46da-ac16-1c407cd4071b@y38g2000hsy.googlegroups.com...
>
>
>
> > Good morning group.
>
> > Formula in Cell F1 =SLOPE(D10:D14,B10:B14)
> > Formula in Cell F2 =SLOPE(D20:D24,B20:B24)
> > Formula in Cell F3 =SLOPE(D30:D34,B30:B34)
> > Formula in Cell F88=....
>
> > Here's the problem. *A simple copy formula down operation will not
> > work for filling in cells F4-F88. *What I end up with is this:
>
> > Formula in cell F4=SLOPE(D31:D35,B31:B35)
> > It is incrementing by 1, rather than the 10 that I need it to
> > (SLOPE(D40:D44,B40:B44). *I will need to do this several more times
> > (grab various numbers of years (10, 15) to generate slope estimates.
> > Thus if there is something that requires a bit of work on the front
> > end that will allow me to easily make changes to the number of years
> > being used in the calculations, I'm all for it.
>
> > Any help on this matter is very much appreciated.
>
> > Mike- Hide quoted text -
>
> - Show quoted text -

"Takeadoe" <mtonkovich@msn.com> wrote in message
news:35d78cf9-a48c-42a5-859c-524c890abb9d@m36g2000hse.googlegroups.com...
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

Takeadoe,

Well, the 5 is there because your first formula referred to D10:D14 -- 5 cells. With a 1,
the OFFSET function will return only one cell, D10. I've not used the SLOPE function -- I
just took your formulas at face value! :)
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Takeadoe" <mtonkovich@msn.com> wrote in message
news:35d78cf9-a48c-42a5-859c-524c890abb9d@m36g2000hse.googlegroups.com...
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!

On Apr 28, 11:22 am, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Takeadoe,
>
> If no one comes up with a less geeky way, use this:
>
> =SLOPE(OFFSET($D$10,ROW()*10-10,0,5,1), OFFSET($B$10,ROW()*10-10,0,5,1) )
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------"Takeadoe"
> <mtonkov...@msn.com> wrote in message
>
> news:92f39f32-fe70-46da-ac16-1c407cd4071b@y38g2000hsy.googlegroups.com...
>
>
>
> > Good morning group.
>
> > Formula in Cell F1 =SLOPE(D10:D14,B10:B14)
> > Formula in Cell F2 =SLOPE(D20:D24,B20:B24)
> > Formula in Cell F3 =SLOPE(D30:D34,B30:B34)
> > Formula in Cell F88=....
>
> > Here's the problem. A simple copy formula down operation will not
> > work for filling in cells F4-F88. What I end up with is this:
>
> > Formula in cell F4=SLOPE(D31:D35,B31:B35)
> > It is incrementing by 1, rather than the 10 that I need it to
> > (SLOPE(D40:D44,B40:B44). I will need to do this several more times
> > (grab various numbers of years (10, 15) to generate slope estimates.
> > Thus if there is something that requires a bit of work on the front
> > end that will allow me to easily make changes to the number of years
> > being used in the calculations, I'm all for it.
>
> > Any help on this matter is very much appreciated.
>
> > Mike- Hide quoted text -
>
> - Show quoted text -

On Apr 28, 4:37*pm, "GB" <NOTsome...@microsoft.com> wrote:
> "Takeadoe" <mtonkov...@msn.com> wrote in message
>
> news:35d78cf9-a48c-42a5-859c-524c890abb9d@m36g2000hse.googlegroups.com...
> 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

Hey - Thanks so much for taking time to help with this. I'm sorry for
the delay in responding to this post, but I was busy fighting other
brush fires. Your idea involving the indirect function sounds like a
good one. Problem is, I'm not totally clear. let me make sure you
understand what I'm working with:

Worksheet 1 has the raw data in a pivot table. There are 10 rows of
data for each county, for a total of 880 lines of data.
Worksheet 2 has the slope formulas - 1 for each of 88 counties for a
total of 88 records.

I gather from your instructions, you're suggesting that I put the
indirect function in the cells in worksheet 2 and then have the slope
formulas in worksheet 2 reference the data in the 10 (5 x and 5 y
values) new columns that I created with the indirect function. If
that is correct, how do I modify the indirect function to refer to
Worksheet 1? I've tried to add the worksheet name to
INDIRECT('worksheet1!'"B"&5*ROW()+5+2) but kept throwing error
messages. If I'm on the right track, could you help me figure out how
to reference a cell on another worksheet and if I'm on the wrong
track, redirect me?

Thank you again for you help. It is genuinely appreciated.

Mike

"Takeadoe" <mtonkovich@msn.com> wrote in message
news:87d2cfae-5b7f-4f98-9b82-73641570c29d@y38g2000hsy.googlegroups.com...
>
> 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
>


I gather from your instructions, you're suggesting that I put the
indirect function in the cells in worksheet 2 and then have the slope
formulas in worksheet 2 reference the data in the 10 (5 x and 5 y
values) new columns that I created with the indirect function. If
that is correct, how do I modify the indirect function to refer to
Worksheet 1? I've tried to add the worksheet name to
INDIRECT('worksheet1!'"B"&5*ROW()+5+2) but kept throwing error
messages.

----------------------------------------------------------------------

The simple answer:

=INDIRECT("worksheet1!B"&5*ROW()+5+2)


The slightly more complicated answer:

NB: Is worksheet1 the name of your first worksheet? In my version of Excel
it names them Sheet1, Sheet2, etc. Anyway, whatever the name of the first
worksheet is, that's what you stick in front of the exclamation mark. If
the worksheet name has more than word in it, then that has to be in single
quotes. So, if worksheet1 is actually called County Data then the formula
would be:

=INDIRECT("'County Data'!B"&5*ROW()+5+2)

A simple approach might be to enter the first formula, select the first 10
cells and fill down so that every 10th row is filled. Now choose
f5>special>blanks to delete the blank rows.