Get startedGet started for free

Compute the semideviation

The semideviation measures the amplitude of below-average returns. Investors sometimes prefer using it in place of the volatility because this metric focuses on the downside risk of an investment.

In this exercise, your goal is to compute the semideviation of ABC historical returns.

To do so, take the square root of

[(R_1-Average)^2 + (R_2-Average)^2 + ... + (R_L-Average)^2] / L, where R_1, R_2, …, R_L are the L returns lower than the average return.

Use a combination of SUMIFS(), COUNTIFS(), and SQRT(). Recall that to write a logical condition, include it within quotation marks “… ” and if necessary, use the concatenation operator & (for example, “<”&H4 means smaller than cell H4).

This exercise is part of the course

Financial Analytics in Google Sheets

View Course

Exercise instructions

  • In E3:E62, compute the squared deviations from the mean.

  • In H10, use SUMIFS() to compute the sum of squared deviations. Consider only below-average returns. This is the numerator of the formula.

  • Next, use COUNTIFS() to count below-average returns. This is the denominator of the formula.

  • Finally, use SQRT() to compute the semideviation.

Hands-on interactive exercise

Turn theory into action with one of our interactive exercises

Start Exercise