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

    --
    Message posted from http://www.ExcelForum.com
     
    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

    --
    Message posted from http://www.ExcelForum.com
     
    AlfD, Jul 11, 2004
    #6
  7. Stephen

    thepcer

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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike Lechner

    Weighted Standard Deviation

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

    Creating Standard Deviation plot

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

    Downside Deviation

    Guest, Jun 2, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    3,286
    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:
    393
  5. Guest

    standard deviation

    Guest, Oct 13, 2005, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    153
Loading...

Share This Page