MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Average of the every 7 rows & Attention MrExcel


Posted by Eric on June 04, 2001 3:33 PM

I suddenly find I cannot start new posts, so I hope someone notices this one and reposts it for me, thanks (and my appologies M. Killkelly).
Column (A) has 1400 rows of data. I want the average of every 7 rows (A1:A7, A8:A15, etc.).
So far what I've thought of is to put in a column (B) that has the count from 1 to 1400 by sevens (1,8,15,22,...) and copy down =average(a1:a7) from C1 to C1400. Then use =index(c1:c1400,b1) in column (D) and copy down. Is there an easier way?


Posted by Aladin Akyurek on June 04, 2001 3:43 PM

Re: Average of the every 7 rows & Attention Mr Excel

Eric

It was me who reposted your question on behalf of you as you requested. I had a hell of time to do it. That [Post message] at the start of this page does not seem to work. IE wasn't able to get at the end of the page. iCab arrived at the end, but the part by means of which you can post was unusable. Opera succeeded at once, whence the posting.

Here is a solution, probably a bit easier than of your own solution.

I'll assume the numbers are in A from A1 on.

In B1 enter: 1 [ just the number 1 ]
In B2 enter: =IF(ISNUMBER(B1),IF(ISNUMBER(INDIRECT(ADDRESS(B1+6,1))),B1+7,""),"") [ copy down this as far as needed ]

In C1 enter: =IF(ISNUMBER(B1),IF(ISNUMBER(B2),AVERAGE(INDIRECT((ADDRESS(B1,1)&":"&ADDRESS(B2-1,1)))),""),"") [ copy down this as far as needed ]

Aladin

Posted by Sean on June 04, 2001 3:51 PM

Re: Average of the every 7 rows & Attention Mr Excel


=============
Hi Eric,


Put the following formula in the next column to your data starting in row 7 and then just paste to the bottom of your data...

=IF(MOD(ROW(),7)=0,AVERAGE(A1:A7),"")

It puts blanks in rows not divisible by 7, if you want something else eg 0 just change the else part of the if statement

Hope it helps
Sean
s-o-s@lineone.net
===========

Posted by Mark W. on June 04, 2001 3:55 PM

Re: Average of the every 7 rows & Attention Mr Excel

Enter the formula, =AVERAGE(OFFSET($A$1,ROW()*7-1,,-7)),
into cell B1 and Copy down to cell B200.

Posted by Aladin Akyurek on June 04, 2001 4:13 PM

Re: Average of the every 7 rows...

I thought no array-formula for just once. ;)

Aladin

Posted by Mark W. on June 04, 2001 4:36 PM

Re: Average of the every 7 rows...

Aah, but this isn't an array formula since AVERAGE()
accepts an array: "...arguments must be either
numbers or names, arrays, or references that
contain numbers".

But, I must admit that I cheated! Negative height
arguments in OFFSET() are technically illegal. See
http://support.microsoft.com/support/kb/articles/Q184/1/09.ASP?LN=EN-US&SD=gn&FR=0&qry=offset%20height&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97
However, Microsoft continues to roll out new versions
(i.e., Excel 2000) that permit this deviant practice. : )

If Microsoft ever closes this loophole then it'll
be necessary to use...

=AVERAGE(OFFSET(OFFSET($A$1,ROW()*7-1,),-6,,7))

Posted by Eric on June 05, 2001 6:33 AM

Thanks for the advice and the repost

Sorry to hear that about your trouble posting this too. Opera did not work for me (yikes)

Posted by Eric on June 05, 2001 6:51 AM

Thanks Mark,

That gets everything except the average of the first 7 numbers, but I can get that average manually,
thanks again for your attention

Posted by Eric on June 05, 2001 6:57 AM

Oops, only does that if you dont start in a1 and b1, ignore previous post

Dont let my thick headedness besmirch your genius
:-)

Posted by Eric on June 05, 2001 8:35 AM

anyone know a way to make Mark's 1-column solution work when rows are inserted?

I love the 1-column idea, but it doesnt work if users insert rows after putting in the formula, you basically have to subtract the inserted rows from the row count within the formula each time the rows change
e.g if formula started off as
=AVERAGE(OFFSET($A$1,ROW()*7-1,,-7))
with data starting in A1, and a user inserts a row for column headings, then data moves to A2 and formula changes to
=AVERAGE(OFFSET($A$2,ROW()*7-1,,-7))
to get the appropriate data set it has to be manually edited to
=AVERAGE(OFFSET($A$1,(ROW()-1)*7-1,,-7))
to compensate for the row change.
Any way of making excel adjust that value and still keep the formula at 1-column?

Posted by Mark W. on June 05, 2001 9:07 AM

Change it to...

Posted by Eric on June 05, 2001 9:22 AM

Thanks!

Works like a charm :)