Hard to say why that happened.

As you can see, the result I posted for Jan is the same result that you say
you should have gotten. I tested based on a larger data sample with other
dates/values besides the sample you posted.

The other numbers I posted are just made up for purpose of the example.

Did you enter the Stdev formula as an array?

Select cell E2

Move the mouse cursor to the end of the formula in the formula bar.

Hold down both the CTRL key and the SHIFT key then hit the ENTER key.

If done properly Excel will place squiggly braces { } around the formula.
You can't type these in, you MUST use the key combination.

Did that fix it?

Biff

"frank" wrote in message
...
Biff and Joe,

Thanks for the help. I've got a question for Biff. I copy the exact
formula
to cell D2 and E2 (the other setting is just as you assumed). I got
different
answer as Jan 1999 = 0.013515868. But i know that 0.011753089 is the right
answer.

Any idea?

Frank


"Biff" wrote:

=TEXT(DATE(1999,0+ROWS($1:1),1),"mmm yyyy")

Don't need the 0+. Don't know why I put that in there!

=TEXT(DATE(1999,ROWS($1:1),1),"mmm yyyy")

Biff

"Biff" wrote in message
...
Hi!

Try this:

Dates in column A, Return in column B.

Say, in D2, enter this formula: (I'm assuming your data starts in
January
1999)

=TEXT(DATE(1999,0+ROWS($1:1),1),"mmm yyyy")

In E2 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=STDEV(IF(TEXT(A2:A500,"mmm yyyy")=D2,B2:B500))

Select both D2 and E2 and copy down as needed.

The results will look like this:

Jan 1999..........0.0117530886768349
Feb 1999.........0.0036587412554555
Mar 1999........0.0154788521445444
Apr 1999.........0.0054782145874155
etc
etc

Adjust the range in the Stdev function to suit.

Biff

"frank" wrote in message
...
I have a time series data contains two columns. (example as below) It
records
stocks' daily return for 6 year period. What I am interested is to
find
out
the monthly return volatility presented by standard deviation.
Therefore,
my
first monthly return volatility will be =stdev(B2:b21). I can do this
manually each month for 6 year (6*12=72 times) and repeat for another
stock.
Can anyone help me to simplify the process?

Date Return
1/4/1999 0.010539721
1/5/1999 0.016203103
1/6/1999 0.021746115
1/7/1999 -0.000462872
1/8/1999 0.009811546
1/11/1999 -0.003875422
1/12/1999 -0.020677227
1/13/1999 -0.0102644
1/14/1999 -0.005702611
1/15/1999 0.025050688
1/18/1999 0.009379503
1/19/1999 -0.00936856
1/20/1999 -0.006888695
1/21/1999 -0.011408838
1/22/1999 -0.006306738
1/25/1999 0.003726166
1/26/1999 0.006794611
1/27/1999 -0.00176185
1/28/1999 0.002516646
1/29/1999 0.009164049