Up/Downside standard deviation

[duration]

Hello Everyone,

Any idea on how to compute a up/downside standard deviation from a data series?

Also, a harmonic and geometric mean with negative values?

Many thanks,

[duration]

Ideas anyone?

Thanks in advance,

[MrShorty]

I'm not a statistician, so I didn't know definitions for those means, but a couple of quick google searches led to mathworld.wolfram.com where the necessary equations are outlined. The geometric mean will be easily calculated using the PRODUCT function and either the POWER function or the exponential operator (^).

The harmonic mean can be calculated quite easily using the defining equation if you can easily add another column to calculate the reciprocal of the data points. Otherwise it could easily be calculated from its given relationship to the arithmetic mean and geometric mean.

A quick google search for upside standard deviation suggests that this is a finance/stock formula. While I have some familiarity with statistics, I know next to nothing about high finance. I didn't get very far in looking, but it should be doable if you can find the proper formulas

[duration]

Hello MrShorty,

Thank you for your reply!

I actually solved the mean issues with the following website

http://www.buzzardsbay.org/geomean.htm#negative_values

However I am still trying to find an efficient way for the up/downside standard deviations. My workaround at the moment is to create a new row with that duplicates positive/negative values only, and calculate a STDEVA() from there. But there must be a better way...

[janschepens]

Hello MrShorty,

Thank you for your reply!

I actually solved the mean issues with the following website

http://www.buzzardsbay.org/geomean.htm#negative_values

However I am still trying to find an efficient way for the up/downside standard deviations. My workaround at the moment is to create a new row with that duplicates positive/negative values only, and calculate a STDEVA() from there. But there must be a better way...

Have you looked at this spreadsheet: http://investexcel.net/507/downside-deviation-excel/

[mg87]

Hi there,

were you able to find a way to calculate the upside/downside standard deviation in excel? I am also trying to figure this out in excel...

[protonLeah]

mg87, Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.