In this article, I will show **how to calculate Mean Squared Error in Microsoft Excel** with quick steps and clear illustrations.

In practical life, we may have to forecast data and when we come to know the actual data, we have the chance to know the accuracy of the forecast technique. **MSE **or **Mean Squared Error** is one of the measures we can use to do this.

**Table of Contents**hide

## Download Practice Workbook

Please download the following workbook for your practice with ease before you start to go through the steps described in the next sections.

## What Is Mean Squared Error (MSE)?

First of all, let’s know the term, what is **MSE**?

“**Mean Squared Error**”, the name suggests what it means.

**Mean Squared Error Formula:**

**MSE=(1/n) ****✕**** Σ(Actual Data – Forecast Data)**^{2}**; where n is the number of occurrences.**

So, **Mean Squared Error** is the mean of the square of differences between expected and actual values.

**Note**

**MSE**is**always positive**.**The smaller the MSE, the better the accuracy**of the forecast method.

## 3 Ways to Calculate Mean Squared Error in Excel

Before starting, let’s introduce the data which are going to be used in this article.

In this dataset, we have some actual and budget amounts for several months of a year. The budget amounts are predicted using a forecasting technique and now, we will proceed to find the accuracy of this forecast model using **Mean Squared Error measurement**.

So, let’s see the methods to do this, one by one.

### 1. Calculation of Mean Squared Error Using SUM and COUNTA Functions

In the first method, we will use the **SUM **and **COUNTA** functions to find the **Mean Squared Error**. The steps are simple.

**🔀**** Steps:**

- Add a new column named
**Difference**on the right side and insert the following formula in the first cell of this column (i.e.**cell E5**). After that, drag the**fill handle**to copy this formula to the rest of the cells.

`=C5-D5`

- After that, add another column which is named
**Difference**, and insert the following formula in^{2}**cell F5**.

`=E5^2`

- Similarly, copy this formula to all cells in this column.

- Now, create a part just below the data, where the
**Mean Squared Error**calculation will be performed.

- In
**cell F18**, insert the following formula.

`=SUM(F5:F16)`

- This will give you the
**∑(Actual ~ Forecast)**value.^{2}

- After that, write the following formula in
**cell F19**.

`=COUNTA(B5:B16)`

- This will give you the number of months.

- Finally, divide the value in
**cell F18**by the value in**cell F19**using the following formula.

`=F18/F19`

- Hence, the
**Mean Squared Error**turns out to be**6.667**.

**Read More: ****Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)**

**Similar Readings**

**On Error Resume Next: Handling Error in Excel VBA****How to Remove Value Error in Excel (4 Quick Methods)****REF Error in Excel (9 Suitable Examples)****VALUE Error in Excel: 7 Reasons with Solutions****Reasons and Corrections of NAME Error in Excel (10 Examples)**

### 2. Calculation of Mean Squared Error Using AVERAGE Function

Here is another way to find Mean Squared Error, easier than the first one. In this method, we will utilize the **AVERAGE function** of Excel.

**🔀**** Steps:**

- Just like in
**method 1**,**add two columns**to calculate**differences**and**squared differences**. - After that, insert the formula below to get the
**Mean Squared Error**.

`=AVERAGE(F5:F16)`

Here, **F5:F16** is the range of cells that has the square of differences between actual and predicted values.

**Read More: ****[Fixed] Excel Found a Problem with One or More Formula References in This Worksheet**

### 3. Calculation of MSE Using SUMSQ and COUNT Functions

In the last method, we will use the **SUMSQ** and **COUNT** functions to calculate the **Mean Squared Error**.

**🔀**** Steps:**

- Just as in
**method 1**, this time**add one column**on the right side to calculate the**differences between the actual and predicted values**. - After that, use the following formula.

`=SUMSQ(E5:E16)/COUNT(E5:E16)`

Here, **E5:E16** is the range of cells that has the differences between actual and predicted values.

**Read More: ****Errors in Excel and Their Meaning (15 Different Errors)**

## Quick Notes

To find the **Root Mean Square Error in Excel**, just merge the **SQRT function** with the last output you get in any of the sections stated above. For example, you can use the following formula in the case of **method 3**.

`=SQRT(SUMSQ(E5:E16)/COUNT(E5:E16))`

## Conclusion

So, we are here to conclude now. Hope you have enjoyed this article. If you still face any problems in calculating **Mean Squared Error in Excel**. Please leave **us** a comment below. Thank you!

## Related Articles

**How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)****[Fixed!] ‘There Isn’t Enough Memory’ Error in Excel (8 Reasons)****How to Find Reference Errors in Excel (3 Easy Methods)****Fix “Fixed Objects Will Move” in Excel (4 Solutions)****Excel VBA: Turn Off the “On Error Resume Next”****How to Fix #REF! Error in Excel (6 Solutions)****[Fixed] Excel Print Error Not Enough Memory**