# How to Calculate Semi Variance in Excel (3 Simple Methods)

Microsoft Excel is a powerful software. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we have to carry out mathematical and statistical operations in excel. In real life, itâ€™s necessary to assess the downside risks of any business investments. We can estimate that by determining the Semi Variance, which is a statistical term. This article will show you 3 simple methods to calculate Semi Variance in Excel.

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

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

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

Here, all the observed values in the formula must lie below the mean or target. To illustrate, weâ€™ll use a sample dataset as an example. For instance, the below dataset contains month-wise investment returns. This article will show you how you can calculate the semi-variance in Excel to estimate the downside risk of this investment.

### 1. Combine SUM and COUNT Functions to Calculate Semi Variance in Excel

In our first method, weâ€™ll create a formula by combining the SUM and COUNT functions. The SUM function computes the sum of a range, and the COUNT function counts the number of cells in a range that only numbers. Therefore, follow the steps below to calculate Semi Variance in Excel.

STEPS:

• First, sleet cell C18.
• Then, type the formula:
`=AVERAGE(C5:C16)`

• Now, click cell D5.
• Next, input the formula:
`=IF(C5:C16<D18,C5:C16,"")`
• Subsequently, press Enter.
• Use AutoFill to complete the series.
• The IF function gives out the values that are smaller than the average i.e. cell D18.

• After that, choose cell D19.
• Insert the formula:
`=AVERAGE(D5:D16)`
• Hence, weâ€™ll get the new mean of the returns that are less than the original sample average.

• Afterward, click cell E5.
• Type the formula:
`=IF(D5<>"",(D5-\$D\$19)^2,"")`
• Hit Enter and apply AutoFill.
• Thus, itâ€™ll return the square of the differences.

• Lastly, weâ€™ll calculate the semi-variance.
• For this purpose, select cell D20.
• Input the formula:
`=SUM(E5:E16)/COUNT(E5:E16)`
• Click Enter.
• The COUNT function determines the total count of the cells which has numbers in the range E5:E16.
• Hence, weâ€™ll get the semi-variance.

### 2. Apply SUM, IF, and COUNTIF Functions for Computing Semi Variance

However, if you want to avoid the step-by-step approach in the previous example which involved multiple columns, try this method instead. Here, weâ€™ll join the SUM, IF, and COUNTIF functions thatâ€™ll calculate the semi-variance. So, learn the following steps.

STEPS:

• Firstly, select cell C18.
• Type the formula:
`=AVERAGE(C5:C16)`
• This will compute the sample mean.

• Then, choose cell C19.
• Insert the formula:
`=AVERAGE(IF(C5:C16<C18,C5:C16,""))`
• The IF function will give out values that are smaller than the sample mean.
• The AVERAGE function will calculate the average of the IF(C5:C16<C18,C5:C16,””) formula outputs.

• Finally, in cell C20, input the formula:
`=SUM(IF(C5:C16<C18,(C5:C16-C19)^2,""))/COUNTIF(C5:C16,"<833.75")`
• Accordingly, click Enter.
• The IF function determines the square of the differences of each value in the range C5:C16 that are smaller than the old mean i.e. C18 with 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 that are smaller than 75 or the old mean.
• As a result, weâ€™ll get semi-variance.

### 3. Insert VAR.P Function for Calculating Semi Variance in Excel

Moreover, we can use the default excel function to get the semi-variance. The VAR.P function computes the variance based on population. Hence, follow the below process to carry out the operation.

STEPS:

• First of all, weâ€™ll get the mean in cell C18 as we did in the previous examples.
• Next, click cell C19.
• After that, type the formula:
`=VAR.P(IF(C5:C16<C18,C5:C16))`
• Subsequently, click Enter.
• The IF function returns the values that are less than the C18 i.e. sample mean.
• Therefore, youâ€™ll see the semi-variance.

## How to Calculate Semi Deviation in Excel

Similarly, we can calculate the Semi Deviation. The semi-deviation measures the dispersion of the observations. And it only deals with samples that are below the mean value or a target. Now, learn the following process to find out the semi-deviation.

STEPS:

• In the beginning, weâ€™ll find the mean in cell C18 as we demonstrated in the previous examples.
• Then, choose cell C19.
• Type the formula:
`=STDEV.P(IF(C5:C16<C18,C5:C16))`
• Press Enter.
• The STDEV.P function calculates the standard deviation of an entire population.
• Here, we use it with the IF function to find the semi-deviation.
• The IF function returns only the values that are smaller than C18 i.e. sample mean.
• In such a process, we can get the semi-deviation as well.

## Conclusion

Henceforth, you will be able to calculate Semi Variance in Excel using the above-described methods. Keep using them and let us know if you have more ways to do the task.Â Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

## 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