standard deviation

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!

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.
--
HTH

JonR


"Arne Hegefors" wrote:

> 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!

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

> 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.
> --
> HTH
>
> JonR
>
>
> "Arne Hegefors" wrote:
>
> > 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!

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.
--
HTH

JonR


"Arne Hegefors" wrote:

> yeah I thought about that too but that does not seem very professional when
> my boss looks at it...thanks anyhow!
> "JonR" skrev:
>
> > 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.
> > --
> > HTH
> >
> > JonR
> >
> >
> > "Arne Hegefors" wrote:
> >
> > > 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!

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:

> 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.
> --
> HTH
>
> JonR
>
>
> "Arne Hegefors" wrote:
>
> > yeah I thought about that too but that does not seem very professional when
> > my boss looks at it...thanks anyhow!
> > "JonR" skrev:
> >
> > > 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.
> > > --
> > > HTH
> > >
> > > JonR
> > >
> > >
> > > "Arne Hegefors" wrote:
> > >
> > > > 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!

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. ;-)
--
HTH

JonR


"Arne Hegefors" wrote:

> 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:
>
> > 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.
> > --
> > HTH
> >
> > JonR
> >
> >
> > "Arne Hegefors" wrote:
> >
> > > yeah I thought about that too but that does not seem very professional when
> > > my boss looks at it...thanks anyhow!
> > > "JonR" skrev:
> > >
> > > > 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.
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "Arne Hegefors" wrote:
> > > >
> > > > > 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!

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))}
--
Gary's Student


"Arne Hegefors" wrote:

> 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:
>
> > 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.
> > --
> > HTH
> >
> > JonR
> >
> >
> > "Arne Hegefors" wrote:
> >
> > > yeah I thought about that too but that does not seem very professional when
> > > my boss looks at it...thanks anyhow!
> > > "JonR" skrev:
> > >
> > > > 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.
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "Arne Hegefors" wrote:
> > > >
> > > > > 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!

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


"JonR" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> HTH
>
> JonR
>
>
> "Arne Hegefors" wrote:
>
>> 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!