Average from every other row

dgharp

New Member
Joined
Mar 9, 2004
Messages
7
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?


Thanks in advance,
Darren :rolleyes:
 

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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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


added column formula...
=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. :oops:
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jun 19, 2002
Messages
10,022

ADVERTISEMENT

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! :biggrin: (b) Near as I can tell, Aladin's formula averages rows 1, 9, 17, 25... whereas mine does 8, 16, 24, 32... {EndEdit}

HTH
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022

ADVERTISEMENT

Where am I gonna get into trouble? :p
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
i can insert rows and greg's formula works like a charm. anything i'm missing?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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. (y)

{Edit} SHOOT! Forgot my manners (again) :oops: ...Darren - welcome to the board!{EndEdit}
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

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
Top