How To Calculate Mean Squared Error?

We can use Mean Squared Error (MSE) to compare forecasted values to actual values, for example, comparing actual sales to forecasted figures. The lower the MSE score, the closer the forecast is to the actual results, and so MSE checks the accuracy of the forecast.

What is MSE?

In MSE the difference between the actual and forecasted value is squared; the total of all the squared values within the period is then divided by the number of values.  We square the results to prevent negative results.

We can use the SUMXMY2 function to sum the squares of the differences between the actuals and forecasts and then divide this sum by the number of values in the period.

To run this function, you will need a spreadsheet with actual values and forecast values.

For the purposes of this guiden sample data has been created for you to use. In the sample data, the scenario is of a manager of a car dealership wishing to gauge the accuracy of the sales forecast.

Steps to calculate the MSE

The steps below will take you through the process using some sample data. A quick reference guide is available at the end of this document.

1. Open the Mean Squared Error - sample data Excel spreadsheet.

2. Click where you’d like the results to appear. Use E2 for this example.

3. In E2 type =SUMXMY2(

4. Select the first array, the values in the Actual column, cells B2:B13 - type a comma ','.

5. Select the second array, the values in the Forecast column, cells C2:C13. Close the bracket, but leave the cursor in E2.

6. Type ‘/Count(’ select B2:B13, close the bracket, press Enter.

7. Excel displays the result 417. This is the Mean Squared Error for the actual versus forecast car sales figures. This result shows that the forecast values were quite accurate, that is there was little error/difference between the actual value and the forecast.

Quick reference guide

1. Open the Excel spreadsheet containing the actual and forecast values you wish to analyse.
2. Click where you’d like the results to appear
3. Type =SUMXMY2(
4. Select the values in the Actual column, type a comma ‘,’
5. Select the values in the Forecast column, close the bracket, but leave the cursor in the cell.
6. Type ‘/Count(’ select the values in the Actual column, close the bracket, press Enter.
7. Excel displays the result; this is the Mean Squared Error for the actual versus forecast figures. 

Further reading:
How to calculate accuracy in Excel
Calculating standard error in Excel
Excel forecast function