Average from every other row

[dgharp]

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

 

[GorD]

It is with great hesitation that I say this but I can't get Aladins formula to work.

I have used both Gregs and Aladins formulas with the same data set and they come up with different values. Then checked it manually and Gregs appears to be giving the correct answer.

Aladins formula gives an answer of 25.63492063
Gregs gives an answer of 25.56451613

Not a big difference I know.

Also, I initially tried reducing the data set to 24 rows to make the manual average easier to count and then the results are widely different.

Aladins giving an answer of 58 to Gregs 35.66666667 and manually 35.666...is the correct answer.

Anybody else noticed this or where am I going wrong.

I'm still hesitating but here goes.. fingers on the submit

 

[Aladin Akyurek]

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

Almost...

If you want to average every Nth (here: N=8) starting with the first cell of the range...

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

If you want to average every Nth starting from the first instance of the Nth (8th cell/value)...

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

 

[just_jon]

Which row is your 1st row to include? The problem here, I think, is that 1 set goes 1-9-17 while another is 8-16-24 perhaps?

 

[dgharp]

All,

Thanks for the replys and the "playfulness". From my "simple" little test, they all seem to work for my application. I can see that I will be seeking your advise more in the future. Keep it up.

Thanks,
Darren