Simple (i would think!) Autofill Question......

[CoopGeko]

Hi-

I was referred to this forum after searching Google all morning looking for an efficient solution to a seemingly simple question about dragging down a formula.

I have a body of data (600 rows x 100 columns) that I'd like to analyze daily, pasting in a fresh 600x100 batch of numbers and crunching them the same way. Each 6 rows, the data repeats itself with a different variable (stock).

Starting with row 610 (to get outside the cut/paste input data area) I'm simply trying to drag down a percent change formula from each of the six-row groups into new rows 610 to 710.

That is, in B610 I'm looking for the value of the formula =(B$4-B$3)/B$3, and on the next row the value of =(B$10-B$9)/B$9 and so on for the next 98 rows.

But using Excel 2002 SP3, I can't seem to get the calcs to occur only every 6th row by dragging or cutting/pasting. I've tried selecting several cells at once, selecting a group of individual cellls, using the crosshair handle as well as the cell perimeter and used the shift and control keys all to no avail. Gotta me missing somthing really basic here!

I'm almost sure I've done this before with no problem at all, just forgot how.....maybe I had Excel 2003 at work and functionality was different....we have not started using 2007 yet.

Not sure how to attach image, but hopefully my question is simple enough as I think I'm missing some very obvious trick here.

Thanks in advance for thoughts here.

Regards,

Gordon

 

[CoopGeko]

I've attached a screenshot of the partial spreadsheet. Oh well, I was going to post attachement, but as a newbie, I guess I can't attach. But the below example ought to be sufficiently clear to explain what I'm trying to do.

Starting in B610, I'm trying to calculate percent change each day of the PX_CLOSE and PX_OPEN.

So in B610: =(B$4-B$3)/B$3
in B611 I have: =(B$10-B$9)/B$9
in B612 I have: =(B$16-B$15)/B$15

All I'm trying to do is drag this set of formulas down another 97 rows. Seems there are plenty of people who'd like to be able to do this easily.....

Hello Microsoft! You guys listening?

Thanks for any simple suggestions here.

 

[ukmikeb]

Hi

Welcome to the forum.

Try this in B610 -

=(INDEX($B$4:$B$600,1+(ROWS($B$610:$B610)-1)*6)-INDEX($B$3:$B$600,1+(ROWS($B$610:$B610)-1)*6))/INDEX($B$3:$B$600,1+(ROWS($B$610:$B610)-1)*6)

and copy down

hth

 

[CoopGeko]

Wow Mike! Awesome!

You nailed it in one shot! Unbelievable! I would never in a milion years have figured that out with two functions and both absolute and fixed references..... man I'm gonna have to study that line to appreciate the logic. But for right now all I know is it returns exactly what I was attempting to do.

Thank you Mike for helping out. And thanks for the time thinking about a solution for me. Was working on it all last night. Can't believe that such a simple idea as trying to skip every nth line could be so complicated. You just got my week started on the right note!

Cheers,

Gordon

 

[ukmikeb]

Gordon

You're welcome, pleased to have helped.

Theory behind the row calculation part of the formula 1+(ROWS($B$610:$B610)-1)*6 -

1 - points to the first row cell B3 or B4

ROWS($B$610:$B610) - provides a total of the number of rows from cell B610 as you drag down eg 1 in this instance

-1)*6 - is used to calculate the offset from cell B3 or B4 such that for row 611 -
1+(ROWS($B$610:$B611)-1)*6 evaluates to 1+(2-1)*6 = 7 to arrive at cell B10 or B11

Here's hoping that explanation clarifies it for you.

Finally, the formula can be shortened to -

=INDEX($B$4:$B$600,1+(ROWS($B$610:$B610)-1)*6)/INDEX($B$3:$B$600,1+(ROWS($B$610:$B610)-1)*6)-1

Good luck with your project

 

[CoopGeko]

Mike-

Thank you for the explaination and thanks again for finding a solution.

I pasted your formula into a word doc so I could better disect what it does and was then able to use it for other columns. Removing the $ prior to the column letter allowed me to drag it sideways too.

One thing I noticed is that I was unable to drag a group of three similarly coded columns, each performing a different calc, over by one column. But by first dragging the rightmost column over one, and then the middle and then the leftmost over one column in that order, then all worked fine.

Regards,

Gordon