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

#### CoopGeko

##### New Member
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### CoopGeko

##### New Member
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

##### Well-known Member
Hi

Welcome to the forum.

Try this in B610 -
Code:
``=(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

##### New Member
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

##### Well-known Member
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 -

Code:
``=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

##### New Member
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

Replies
7
Views
384
Replies
1
Views
124
Replies
0
Views
552
Replies
2
Views
495
Replies
3
Views
526

Threads
1,191,708
Messages
5,988,232
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back