# Average from every other row

#### dgharp

##### New Member
I am an Excel "rookie" with I'm sure a very simple question. Can I use a formula to AVERAGE data (within the same column) from every 8th row (for rows 1 to 500)? Also, will this formula update when adding or deleting rows between 1 & 500?

Or should I use a macro?

Darren

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Zack Barresse

##### MrExcel MVP
i'm sure you'll see better, but you could always add a column and try this. here's one way...
Book1
ABCD
11 36
22
33
44
55
66
77
88Check
99
1010
1111
1212
1313
1414
1515
1616Check
Sheet1

=IF(MOD(ROW(),8)=0,"Check","")

average formula...
=SUMIF(B1:B64,"Check",A1:A64)/COUNTIF(B1:B64,"Check")

HTH

edit: looks funny in lieu of my data running to row 64, sorry for not mentioning that.

##### Well-known Member
Hello,

Here is the macro way

Code:
``````Sub Macro1()
MY_COUNT = 0
MY_TOTAL = 0
For MY_ROWS = Range("a65536").End(xlUp).Row To 1 Step -8
MY_TOTAL = MY_TOTAL + Range("A" & MY_ROWS).Value
MY_COUNT = MY_COUNT + 1
Next MY_ROWS
Range("B1").Value = MY_TOTAL / MY_COUNT
End Sub``````

Any use?

or use this code for rows 1 to 500

Code:
``````Sub Macro1()
MY_COUNT = 0
MY_TOTAL = 0
For MY_ROWS = 500 To 1 Step -8
MY_TOTAL = MY_TOTAL + Range("A" & MY_ROWS).Value
MY_COUNT = MY_COUNT + 1
Next MY_ROWS
Range("B1").Value = MY_TOTAL / MY_COUNT
End Sub``````

Any changes made and the macro will need to be run again, but this can also be overcome by code.

##### MrExcel MVP
Assuming that your secret range is A1:A500, try...

=AVERAGE(IF(MOD(ROW(\$A\$1:\$A\$500)-CELL("Row",\$A\$1:\$A\$500)+0,8)=0,(\$A\$1:\$A\$500)))

which you need to confirm with control+shift+enter instead of just with enter.

You can delete/add rows between A2:A500. Deleting A1 would give a #REF! error. If you need the flexibilty to delete the start, we need to modify the formula.

#### Greg Truby

##### MrExcel MVP

This appears to work for me in testing:

{=AVERAGE(IF(MOD(ROW(A\$1:A\$500),8)=0,A\$1:A\$500,""))}

Note the braces surrounding the formula, indicating that this is an array formula. You do not type them in - type in the formula inside the braces, then to confirm the formula hit Ctrl+Shift+Enter and Excel will turn this into an array formula and you will see braces in the formula bar.

Not sure what you mean by "adjusting", but this is coded to look at every 8th line, so if you insert a row at 7, what was in row 7 becomes row 8 and becomes part of this formula.

{Edit}(a)I'm pleased as punch with myself, I came up with the same basic approach as Aladin. Not quite as fancy, but still -- woohoo! (b) Near as I can tell, Aladin's formula averages rows 1, 9, 17, 25... whereas mine does 8, 16, 24, 32... {EndEdit}

HTH

##### MrExcel MVP
Greg Truby said:
This appears to work for me in testing:

{=AVERAGE(IF(MOD(ROW(A\$1:A\$500),8)=0,A\$1:A\$500,""))}

...

Not robust...

#### Greg Truby

##### MrExcel MVP

Where am I gonna get into trouble?

##### MrExcel MVP
Greg Truby said:
Where am I gonna get into trouble?

Insert one or more rows before the data, you'll be in trouble.

#### Zack Barresse

##### MrExcel MVP
i can insert rows and greg's formula works like a charm. anything i'm missing?

#### Greg Truby

##### MrExcel MVP
OK - using the Evaluate Formula tool, I think I'm getting it -- Cell("row",range) is returning a constant that corresponds to the row for the first cell in range, not an array of rows. So we end up with an array of differences that starts our mod function counting off from the first row. In your formula, you're starting with row 1 being "zero" and row 9 being "eight"? Correct? So to use your formula to get 8, 16, 24, etc. it's:

=AVERAGE(IF(MOD(ROW(A\$1:A\$500)-CELL("Row",A\$1:A\$500)+1,8)=0,(A\$1:A\$500)))

Correct? And thank you very much for the lesson.

{Edit} SHOOT! Forgot my manners (again) ...Darren - welcome to the board!{EndEdit}

Replies
3
Views
122
Replies
7
Views
696
Replies
3
Views
124
Replies
5
Views
406
Replies
13
Views
276

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,321
Messages
5,769,444
Members
425,546
Latest member
DisMissive

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