STDEV of values from last 6 months.

[kaxxp]

Hi all,

I have large range of dates each column a with mean strength test results in coloumn D.

Any ideas on how i can calculate stdev of values from all results in previous six months from date shown in column a?

I want to do this at end of every month. but date range is dynamic and changes as number of tests is not consitant.

Cheers

[Pauleyb]

Try this in E10:
=STDEV(IF($A$9:A10-A10>=-180,$D$9:D10,""))
Use Ctrl-Shift-Enter so that it is entered as an array formula. Then drag down the rest of your data.

I 'cheated' for this proof of concept, the 180 is my simple 6 month check. If this looks like it is working for you, then I would recommend improving that check to something more accurate (DATEDIF?).

[kaxxp]

Thanks Pauley,

works will with estimate of six months. Cant figure out how to incorporate dateif into function. and one got ideas?

Cheers

Kym

[Pauleyb]

This should do it (again, enter the formula with ctrl-shift-enter):
=STDEV(IF(DATEDIF($A$9:A10,A10,"m")<=6,$D$9:D10,""))

Be warned. This is an array formula, and as the list gets longer, this calculation could tie up some resources and CPU cycles.