Moving AVERAGE function

[ABSTRAKTUS]

Hi Gurus,

I need to build a moving average function as formula will be copied across. The trick is that formula will be copied in every 10th column but it's moving step is only one column. How do I solve this? See dummy...

Thanks guys.

[Paul]

Not to be difficult, but is this something you're doing a lot of? And how many columns are we talking about here? Typing at 80+ WPM, I'm pretty sure I can type a simple AVERAGE formula and press the right arrow key 7 times and repeat several times.

If this is a one-time-deal, by the time an elaborate formula is devised, the formulas could have been manually typed..

[ABSTRAKTUS]

Well, this formula will be copied at least 365 times. Does it make a problem?

[ABSTRAKTUS]

what is that magic key combination?

[DonkeyOte]

based on the sample - a couple of approaches:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the latter though more elegant and succinct than the former is Volatile by virtue of OFFSET - if you have lots of these you may choose to opt for the INDEX approach.
(also it would make sense to store the COUNTIF result in row 1 and calculate once per column given the value is the same for all rows in that column)

[ABSTRAKTUS]

Works well, but... There is another problem. There are quite a few zero values within data and averaging them function gives me false figures. How to modify this formula to exclude zero values from average?

=AVERAGE(INDEX($B17:$X17,COUNTIF($B$2:B$2,"Average*")):INDEX($B17:$AH17,10+(COUNTIF($B$2:B$2,"Average*")-1)))

[DonkeyOte]

Rather depends on the version(s) you need this to work with - your profile states 2003 whereas file type of sample implies 2007 or later.

XL2007+
change AVERAGE to AVERAGEIF and add test of <>0

Please Login or Register  to view this content.

XL2003+
you will need to either use an Array else revert to SUM(range)/COUNTIF(range,"<>0")
(the latter option assumes only numbers/blanks)

[ABSTRAKTUS]

Thanks! Works well.