how to increment cell formula by 5 rather than 1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Takeadoe - 28 Apr 2008 15:39 GMT

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

Reply to this Message

Earl Kiosterud - 28 Apr 2008 16:22 GMT

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

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Good morning group.
>
[quoted text clipped - 17 lines]
>
> Mike

Reply to this Message

Takeadoe - 28 Apr 2008 19:36 GMT

> Takeadoe,
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Reply to this Message

GB - 28 Apr 2008 21:37 GMT

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

Reply to this Message

Takeadoe - 02 May 2008 19:42 GMT

> 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
[quoted text clipped - 55 lines]
> 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

Reply to this Message

GB - 03 May 2008 00:07 GMT

> The simplest way is to make a cross-reference to the data on the same row
> as
[quoted text clipped - 6 lines]
> 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)

Reply to this Message

Lori - 04 May 2008 13:17 GMT

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.

Reply to this Message

Earl Kiosterud - 28 Apr 2008 22:01 GMT

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! :)

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Takeadoe,
>
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Reply to this Message