downside standard deviation

Discussion in 'Microsoft Excel Misc' started by Stephen, Jul 10, 2004.

  1. Stephen

    Stephen Guest

    Hi,

    I have found a standard deviation function in Excel and
    have used it in the column of numbers below. The average
    being 30.97 and the standard deviation be 39.75.

    But I need to know the "downside standard deviation,"
    which is the the standard deviation of all of the numbers
    below the average, that is below 30.97. I haven't found a
    function in Excel for this. The only way I know to do it
    is to copy all the numbers below 30.97 to a separate
    column and do the standard deviation. Does anyone know a
    simpler way? Here is the column:

    45.80259
    7.649806
    -17.492966
    38.2224
    -0.039098
    120.446022
    17.183628
    5.695066
    39.07161
    16.612884
    17.517976
    12.661228
    17.925472
    112.34281

    Average 30.97
    Standard deviation 39.75

    Thanks a lot for any help,

    Stephen
     
    Stephen, Jul 10, 2004
    #1
    1. Advertisements

  2. Stephen

    AlfD Guest

    Hi!

    A quick way to do what you want:

    Assume data is in A1:A14.
    A16 has formula =average(A1:A14)

    In B1 put =IF(A1<$A$16,A1,"") and copy down to B14
    In B16 put =stdevp(B1:B14) if the data is the whole of the population
    or =stdev(B1:B14) if you are sampling.

    Al

    --
     
    AlfD, Jul 10, 2004
    #2
    1. Advertisements

  3. Another way:

    =STDEV(IF(A1:A14<AVERAGE(A1:A14),A1:A14))

    This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
    correctly, excel will wrap curly brackets {} around your formula. (don't type
    them yourself.)

    With your data in A1:A14, I got: 11.64946737.

    Stephen wrote:
    >
    > Hi,
    >
    > I have found a standard deviation function in Excel and
    > have used it in the column of numbers below. The average
    > being 30.97 and the standard deviation be 39.75.
    >
    > But I need to know the "downside standard deviation,"
    > which is the the standard deviation of all of the numbers
    > below the average, that is below 30.97. I haven't found a
    > function in Excel for this. The only way I know to do it
    > is to copy all the numbers below 30.97 to a separate
    > column and do the standard deviation. Does anyone know a
    > simpler way? Here is the column:
    >
    > 45.80259
    > 7.649806
    > -17.492966
    > 38.2224
    > -0.039098
    > 120.446022
    > 17.183628
    > 5.695066
    > 39.07161
    > 16.612884
    > 17.517976
    > 12.661228
    > 17.925472
    > 112.34281
    >
    > Average 30.97
    > Standard deviation 39.75
    >
    > Thanks a lot for any help,
    >
    > Stephen


    --

    Dave Peterson
     
    Dave Peterson, Jul 10, 2004
    #3
  4. You can use an array formula and avoid using extra cells
    =STDEV(IF(A1:A14<AVERAGE(A1:A14),A1:A14))
    array entered (Ctrl-Shift-Enter)

    Jerry

    AlfD < wrote:

    > Hi!
    >
    > A quick way to do what you want:
    >
    > Assume data is in A1:A14.
    > A16 has formula =average(A1:A14)
    >
    > In B1 put =IF(A1<$A$16,A1,"") and copy down to B14
    > In B16 put =stdevp(B1:B14) if the data is the whole of the population
    > or =stdev(B1:B14) if you are sampling.
     
    Jerry W. Lewis, Jul 10, 2004
    #4
  5. Stephen

    Stephen Guest

    Hi,

    Thanks for your help. I guess I must be doing something
    wrong.

    When I cut and pasted the formula,

    =STDEV(IF(F4:F17<AVERAGE(F4:F17),F4:F17))

    I didn't get any brackets (even though I did the Shift-
    Control-Enter instead of just Enter) and I got a #VALUE!
    in the cell. I don't know that much about Excel so I am
    not sure what I am doing wrong.

    I thought maybe it was because I cut and pasted it. So I
    typed in the formula. I got the brackets around it but in
    the cell was now #N/A! When I hit Shift-Control-Enter a
    couple more times, I lost the brackets and got the #value!

    If anyone has any idea what I am doing wrong, I would
    really appreciate knowing it.

    Thanks again,

    Stephen

    >-----Original Message-----
    >You can use an array formula and avoid using extra cells
    > =STDEV(IF(A1:A14<AVERAGE(A1:A14),A1:A14))
    >array entered (Ctrl-Shift-Enter)
    >
    >Jerry
    >
    >AlfD < wrote:
    >
    >> Hi!
    >>
    >> A quick way to do what you want:
    >>
    >> Assume data is in A1:A14.
    >> A16 has formula =average(A1:A14)
    >>
    >> In B1 put =IF(A1<$A$16,A1,"") and copy down to B14
    >> In B16 put =stdevp(B1:B14) if the data is the whole of

    the population
    >> or =stdev(B1:B14) if you are sampling.

    >
    >.
    >
     
    Stephen, Jul 11, 2004
    #5
  6. Stephen

    AlfD Guest

    Hi!

    Did you paste it into the formula bar? Then use C-S-
    (Ctrl-shift-enter))?

    Is your data in column F?

    There aren't many other things that could be awry.

    If all else fails: remember KiSS. You can still try the quick approac
    I suggested ealier...!

    Al

    --
     
    AlfD, Jul 11, 2004
    #6
  7. Stephen

    thepcer

    Joined:
    Jul 26, 2010
    Likes Received:
    0
    thepcer, Mar 22, 2012
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Mike Lechner

    Weighted Standard Deviation

    Mike Lechner, Aug 9, 2003, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    11,371
    David J. Braden
    Aug 11, 2003
  2. Steve

    Creating Standard Deviation plot

    Steve, Jan 7, 2004, in forum: Microsoft Excel Misc
    Replies:
    7
    Views:
    527
    Steve
    Jan 7, 2004
  3. Guest

    Downside Deviation

    Guest, Jun 2, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    4,103
    tyrian
    Nov 16, 2011
  4. Guest

    How do I annualize a standard deviation

    Guest, Dec 13, 2004, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    647
  5. Guest

    standard deviation

    Guest, Oct 13, 2005, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    327
  6. bombayterror

    A question about Standard Deviation

    bombayterror, Jan 5, 2006, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    356
    bombayterror
    Jan 5, 2006
  7. Guest
    Replies:
    0
    Views:
    351
    Guest
    Mar 21, 2006
  8. Myl
    Replies:
    3
    Views:
    396
    Mike Middleton
    Apr 5, 2006
Loading...