Gain / Loss Standard Deviation

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

 

[Bernie Deitrick]

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