Moving/trailing standard deviation (multiple conditions?)

Hello everybody,

I have been working on something for most of the day, and I cannot seem to find a solution by myself. So, I thought I would give the excel forum a go and see if it is possible to do what I want in Excel.

Here it is:

I have some data and I want to know the moving (trailing) standard deviation. My data looks like this

Date Value
01-01-1999 2.014
03-01-1999 2.122
15-01-1999 1.980
22-03-1999 2.005

I now want to output the trailing 1-year standard deviation in a third column. When I try averages I can get there by using: =AVERAGEIFS(B:B;A:A;">"&A3-365;C:C;"<"&A3). With the first condition being the trailing start of period (-365 days in this case) and the second criteria being the cut off at the date of that moment.

A similar function for STDEV does not exist. Also, similar approaches with STDEV.S(IF(.......IF(........)) do not seem to work.

Any help is much appreciated.

Stikker

Wow, that was quick. And accurate. Thanks a lot!!

Don't understand what you did, but I checked and double checked and everything did was it was supposed to do.


I also have a much less urgent and important question too. Say that I want to exclude std deviations from it (by returning "99" or whatever), that are based on data that does not yet cover an entire period (365 days in this case). Is there a way to do so?

The obvious reason being, that the trailing average for 365 days, is misleading for a period that only cover the trailing two weeks or so.