# How to Calculate Semi Variance in Excel – 3 Simple Methods

Semi Variance evaluates the variation among the observations in a sample. Semi-variance dealsÂ  with the observations that are below the average of the dataset, or mean, or a target value only. The general formula to calculate semi-variance is:

Semi Variance = sum of ((observed value below mean – mean)^2) / number of observations below mean

The dataset below contains month-wise investment returns.

To calculate semi-variance and estimate the downside risk of this investment:

### Method 1 – Combine the SUM and the COUNT Functions to Calculate Semi Variance in Excel

Combine the SUM and the COUNT functions.

STEPS:

• Select C18.
• Use the formula:
`=AVERAGE(C5:C16)`

• Click D5.
• Enter the formula:
`=IF(C5:C16<D18,C5:C16,"")`
• Press Enter.
• Drag down the Fill Handle.
• The IF function returns values smaller than the average: D18.

• Select D19.
• Use the formula:
`=AVERAGE(D5:D16)`
• The new mean of the returns less than the original sample average is returned.

• Click E5.
• Enter the formula:
`=IF(D5<>"",(D5-\$D\$19)^2,"")`
• Press Enter and apply the AutoFill.
• The square of the differences will be returned.

• To calculate the semi-variance, select D20.
• Enter the formula:
`=SUM(E5:E16)/COUNT(E5:E16)`
• Press Enter.
• The COUNT function determines the total count of the cells with numbers in E5:E16.
• The semi-variance is displayed.

### Method 2 – Applying the SUM, IF, and COUNTIF Functions to compute the Semi Variance

STEPS:

• Select C18.
• Enter the formula:
`=AVERAGE(C5:C16)`
• The sample mean is returned.

• Select C19.
• Enter the formula:
`=AVERAGE(IF(C5:C16<C18,C5:C16,""))`
• The IF function returns values smaller than the sample mean.
• The AVERAGE function calculates the average of the IF(C5:C16<C18,C5:C16,””) formula outputs.

• In C20, use the formula:
`=SUM(IF(C5:C16<C18,(C5:C16-C19)^2,""))/COUNTIF(C5:C16,"<833.75")`
• Press Enter.
• The IF function determines the square of the differences between each value in C5:C16Â smaller than the old mean (C18) and the new mean in C19.
• The SUM function sums the IF(C5:C16<C18,(C5:C16-C19)^2,””) formula outputs.
• The COUNTIF function counts the number of cells smaller than 75 or the old mean.
• This is the semi-variance.

### Method 3 -Using the VAR.P Function to Calculate the Semi Variance in Excel

STEPS:

• Calculate the mean in C18, following the steps described in the previous methods.
• Select C19.
• Enter the formula:
`=VAR.P(IF(C5:C16<C18,C5:C16))`
• Press Enter.
• The IF function returns the values less than C18 (the sample mean).
• The semi-variance is displayed.

## How to Calculate the Semi Deviation in Excel

The semi-deviation measures the dispersion of the observations. It only deals with samples that are below the mean value or a target.

To calculate the Semi Deviation:

STEPS:

• Find the mean in C18.
• Select C19.
• Enter the formula:
`=STDEV.P(IF(C5:C16<C18,C5:C16))`
• Press Enter.
• The STDEV.P function calculates the standard deviation.
• The IF function to finds the semi-deviation and returns values smaller than C18 (the sample mean).
• The semi-deviation is displayed.

## Related Articles

<< Go Back to Calculate Variance in Excel | Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelorâ€™s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF