moving averages

[muppet77]

i am trying to make a 6 value moving average.

the only problem is the list sometimes has non numeric data eg "" meaning a blank.

the data analysis tool doesn't calculate a moving average if there is non numeric data, and using a zero instead will alter the average.

any ideas?

 

[muppet77]

It's ok boss - worked it out and works a treat. thanks a lot!!

 

[muppet77]

looked at data again and still not really doing what i want it to. i'd like to have a 6 running average (data analysis tool pack does not like non entries). see example below, where i've used x in the data column to mean no data, and the first few averages are not given as there is not 6 pieces of data in the history. i'd prefer to stay away froma macro solution if possible. thanks in advance.

data, moving average 6
5, x
5 , x
3 , x
1, x
2 , x
x , x
5 , 3.5
8 , 4
x, 4
x , 4
7, 4.333333333
8 , 5.166666667
1 , 5.166666667
5 , 5.666666667
x , 5.666666667
1 , 5
2 , 4

 

[muppet77]

hope i explained that correctly - basically want to find the average of the 6 previous pieces of data above in the column.

 

[Domenic]

Try the following...

B1, copied down:

=IF(COUNT($A$1:A1)>=6,AVERAGE(INDEX($A$1:A1,LARGE(IF($A$1:A1<>"",ROW($A$1:A1)-CELL("row",$A$1)+1),6)):INDEX($A$1:A1,MATCH(9.99999999999999E+307,$A$1:A1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[muppet77]

looks good domenic. let me play around with it. cheers boss.

 

[muppet77]

perfect domenic! thanks again.

you da man!!!