standard deviation

[Arne Hegefors]

Hello! I have a small problem. I have two columns, A and B. In these columns
there are returns for different bonds. In column C I calculate the diffence
between A and B (C1=A1-B1). In column D I want to calculate the standard
deviation of the difference dating 30 data points back. Thus, in D31 i write
=STDAV(C1:C30).

This all works fine. However I want to skip having the C column and make the
calculations for the standard deviation of the dífference dierctly from the
values in A and B. However I have not managed to do so. Please help me out! I
know it does not sound worthwhile but having the extra column really messes
up my spreadsheet! Any help appreciated! Thanks alot!

 

[JonR]

I'd think the easiest thing to do would be to hide column C. I'm assuming
that you just don't like having it visible.

 

[Arne Hegefors]

yeah I thought about that too but that does not seem very professional when
my boss looks at it...thanks anyhow!
"JonR" skrev:

 

[JonR]

Another solution would be to do your calculations on a different sheet (which
you may elect to hide), if you dont' want to mess up the appearance of your
main tab. I do this a lot, especially if the data is underlying a chart or
something.

 

[Arne Hegefors]

yes thanks! I just thought that it would not be so hard to write a formula
that does it, but I have not managed yet (which does not say very much since
i am no excel aficionado). however i really just wanted a nice formula that
my boss (who is worse than me when it comes to excel) would find
"impressive". but it does not seem to be doable. thanks alot, i think i will
do it your way!

"JonR" skrev:

 

[JonR]

My experience is that the boss may ask about a hidden column (if he notices),
but is not really concerned about them as long as the information you
present is timely, accurate, and presented in an understandable format. As
long as you can offer a reasonable explaination as to why you hid the column
or have your calculations on a different sheet (best yet: "I do it this way
so that you get the consolidated information you need without being
distracted by the calculations.") the boss doesn't mind. The big thing is to
get the right answers. ;-)

 

[Gary''s Student]

You don't need the helper column if you use an array formula:

=STDEV((A1:A30)-(B1:B30))

once the formula is typed, confirm it with CNTRL-SHIFT-ENTER rather than
just enter. It will then appear with braces:

{=STDEV((A1:A30)-(B1:B30))}

 

[Microsoft Newsgroups]

Instead of using STDEV(C1:C31) have you tried the learners way of using a
coma for the values and missing the cells that you do not require.

Example

STDEV(C1,C2,C4,C5 AND SO ON .......)

This works both with rows and columns, but, beware, it only allows 1024
characters in the formula and every character in between the brackets count,
yes, including the comas)

HTH