Standard Deviation

[King_Junior]

I have downloaded share prices of a particular company's stock and have calculated the daily returns over a given period (i.e. 2 years) and the Standard Deviation for that period.

Now, using the standard deviation obtained in the above, identify how many time share prices of the share moved up or down during this period:
(a) more than three (3) standard deviation,
(b) less than three (3) standard deviations, but more than two (2) standard deviations
(c) less than two (2) standard deviations, but more than one (1) standard deviation
(d) less than one (1) standard deviation

I need to find out this answer using Excel and I would really appreciate any help in this regard,

King Jr.

 

[CB]

Do you mean you want to count on how many *days* the share price change was within each of the categories of magnitude you have there? If so, just:

-Calculate standard deviation (of share price or daily return, whichever you're meant to be looking at, I'm not quite clear from the question)
-Divide each daily return by this SD
-Use an AND statement nested within a COUNTIF to count instances meeting each rule - e.g. =COUNTIF(A1,AND(A1>1,A1<2))
To include the cut point in the rule you can do '>=' (greater than OR equal to), etc.
[or just sort them and count them if you find formulas hard]

 

[King_Junior]

Standard Deviation - more info

Hi Cowboybear,

Thanks for your reply.

I have attached my worksheet for your reference.

Sample period is 01/01/05 to 30/06/09. But, we need to apply the SD obtained for this period to the share price movements during 01/07/09 to 31/08/09.

Look forward to receiving the correct excel formula for this question.

 

[CB]

No worries, sorry mate but I am too busy working on my thesis to sort out the formulas for your specific worksheet - IF/AND and COUNTIFS are pretty easy to get the hang of, esp. with example above, I'm sure you'll be fine on your own

 

[King_Junior]

Can anyone help me with my worksheet I have attached?