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?

 

[Pekkavee]

If you have your numbers in column A so

in B6 input =SUM(A1:A6)/COUNT(A1:A6)

and copy down.

Regards
Pekka

 

[muppet77]

that looks at the last 6 CELLS, and not the last 6 VALUES. sometimes there may be 2 blanks together.

eg
2
6
4
""
6
""
""
6
88
3

 

[Pekkavee]

OK. Sorry it's not working like you had in mind.

I hope somebody finds a way

Pekka

 

[Domenic]

Try the following...

=AVERAGE(INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),6)):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

Hope this helps!

 

[muppet77]

cheers for your efforts fellas.

not sure where to put your formula domenic. got data in col A. put yours in b1 and it just gives NUM error.

 

[Domenic]

Sorry! Forgot to mention the all important...

'confirm with CONTROL+SHIFT+ENTER'

Hope this helps!

 

[muppet77]

so i paste it in then press CSE simultaneously? doesn't seem to work. must be me!

cheers for your time.

 

[Domenic]

After you enter the formula, hold the CONTROL and SHIFT keys down, then while you have them pressed, hit the ENTER key. Excel should place braces {} around the formula and you'll know you've entered it correctly.

The other possibility is that you have less than 6 values in your column. Is that the case? If so, try...

=AVERAGE(INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),MIN(6,COUNT(A1:A100)))):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[muppet77]

got it pasted in and it just gives the sam number all the way down?