Gain / Loss Standard Deviation

  • Thread starter Thread starter JW
  • Start date Start date
J

JW

Does anyone know how to calculate gain standard deviation
or loss standard deviation? The definitions are below
but I am not sure how to do it in excel if I have a
column of numbers.

Gain Standard Deviation - Similar to standard deviation,
except this statistic calculates an average (mean) return
for only the periods with a gain and then measures the
variation of only the gain periods around this gain
mean. This statistic measures the volatility of upside
performance.

Loss Standard Deviation - Similar to standard deviation,
except this statistic calculates an average (mean) return
for only the periods with a loss and then measures the
variation of only the losing periods around this loss
mean. This statistic measures the volatility of
downside performance.

Thank you.
 
JW,

With your Gains and losses in A1:A10, for example, array enter (enter with
Ctrl-Shift-Enter)

Gain Standard Deviation
=STDEV(IF(A1:A10>0,A1:A10,""))

Loss Standard Deviation
=STDEV(IF(A1:A10<0,A1:A10,""))

These work because STDEV ignores text values.

HTH,
Bernie
MS Excel MVP
 
Back
Top