Average of the every 7 rows & Attention MrExcel

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

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

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.

Re: Average of the every 7 rows...

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

Aladin

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

Thanks for the advice and the repost

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

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

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

Dont let my thick headedness besmirch your genius
:-)

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?

Change it to...

Thanks!

Works like a charm :)