Two Steps at Once...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 29 Jul 2007 09:54 GMT

I have a column of stock prices in column B with associated dates in column
A.
Let's call it dates in A1:A100 and prices in B1:B100.

In cells A101:A200 I repeat the same dates
In cells B101:B200 I fill down the formula =B2/B1 so those cells show the
daily change in the stock prices.

I can now calculate the STDEV of those daily price changes =STDEV(B101:B200)

How can I do this in one step, direct from a column of stock prices to the
STDEV of the daily price changes?

Thanks,

Steve

Reply to this Message

Bob Phillips - 29 Jul 2007 10:36 GMT

=STDEV(N(B2:B100/B1:B99))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

I am assuming here that you don't copy the formula to B200, that would be
B101/B100

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have a column of stock prices in column B with associated dates in column
>A.
[quoted text clipped - 13 lines]
>
> Steve

Reply to this Message

Harlan Grove - 29 Jul 2007 11:13 GMT

"Bob Phillips" <bob.ngs@somewhere.com> wrote...

>=STDEV(N(B2:B100/B1:B99))

...

What does the N(..) call do?

Reply to this Message

Steve - 29 Jul 2007 13:13 GMT

Bob,

Thanks. Works exactly as requested.

What does N mean?

Steve

> =STDEV(N(B2:B100/B1:B99))
>
[quoted text clipped - 24 lines]
>>
>> Steve

Reply to this Message

Steve - 29 Jul 2007 13:17 GMT

In fact. this does work without the 'N' call. Like so:

=STDEV(B2:B100/B1:B99)

Steve

> Bob,
>
[quoted text clipped - 32 lines]
>>>
>>> Steve

Reply to this Message

Rate this thread: