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)`

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

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

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

`=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.

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

`=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.

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

`=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.

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