# Excel won't pick up my cell pattern

#### deck0120

##### New Member
Hello,

I am having trouble getting Excel to pick up on a pattern. I have two spreadsheets. Sheet 1 contains data and Sheet 2 is form statistical computations on sheet 1 data. I think the reason is beause my computations use the same cell twice. How can I overcome this? Here is simple pattern of what I would like to do. Each time the range increases by 6 but still uses the last cell from the previous computation. I would like to be able to drag this pattern across the entire spreadsheet since there are nearly 5000 cells but Excel will not pick up the pattern.

=AVERAGE(Data!A1:A6)
=AVERAGE(Data!A6:A12)
=AVERAGE(Data!A12:A18)

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### mikerickson

##### MrExcel MVP
A1:A6 has 6 cells, A6:A12 has 7 cells, A12:A18 has 7 cells (breaking the pattern of increasing range size)

What pattern are you using?

#### Oaktree

##### MrExcel MVP
Assuming, as in Mike's question above, that you actually want 6 cells in the first group and 7 thereafter, try

B1 =AVERAGE(Data!A1:A6)
B2 =AVERAGE(OFFSET(Data!\$A\$1,(ROW()-1)*6-1,,7)) copied down

#### deck0120

##### New Member
I sorry for the confusion. I would like 7 cells for each computation.

#### mikerickson

##### MrExcel MVP
Modifying Oaktree's solution to
B1 =AVERAGE(OFFSET(Data!\$A\$1,(ROW()-1)*6+1,,7)) copied down

Will give the ranges A1:A7, A7:A13, A13:19, A19:A25, ...

Last edited:

#### deck0120

##### New Member
Thanks for the help! Everything Works Great! For anyone else looking at this posting. Make sure you start in the first row for this to work. I started in Row 4 and had to modify the equation a bit to get the correct result.

Replies
5
Views
210
Replies
5
Views
206
Replies
5
Views
125
Replies
2
Views
118
Replies
16
Views
534

1,191,670
Messages
5,987,951
Members
440,121
Latest member
eravella

### 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.

### Which adblocker are you using?

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

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