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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
i can insert rows and greg's formula works like a charm. anything i'm missing?
 
Upvote 0
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}
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top