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:

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:

• Press Enter.
• In this way, the AVERAGE function will give you the average of the dataset.

• Now, click cell D5.
• Next, input the formula:

• 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:

• 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:

• 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:

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

Read More: How to Calculate Variance of Stock Returns in Excel

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:

• This will compute the sample mean.

• Then, choose cell C19.
• Insert the formula:

• 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:

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

Read More: Budget vs Actual Variance Formula in Excel

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:

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

Read More: How to Do Price Volume Variance Analysis in Excel

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:

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

Read More: How to Calculate Portfolio Variance in Excel

Download Practice Workbook

Download the following workbook to practice by yourself.

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

• How to Calculate Variance Inflation Factor in Excel
• How to Create Minimum Variance Portfolio in Excel
• How to Calculate Schedule Variance Using Excel Formula
• How to Calculate Budget Variance in Excel

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