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.

Calculate the mean squared error using LINEST function

Another method to calculate the Mean Squared Error in Excel is to use the “LINEST” function. The “LINEST” function is a built-in regression analysis tool in Excel that can be used to calculate the MSE.

Calculate the mean squared error using POWER and AVERAGE functions

A third method to calculate the Mean Squared Error in Excel is to use the “POWER” and “AVERAGE” functions.

Calculate the mean squared error using SUMPRODUCT and COUNT functions

A fourth method to calculate the Mean Squared Error in Excel is to use the “SUMPRODUCT” and “COUNT” functions.

Calculate the mean squared error using DEVSQ and AVERAGE functions

A fifth method to calculate the Mean Squared Error in Excel is to use the “DEVSQ” and “AVERAGE” functions. The “DEVSQ” function calculates the sum of squares of deviations from a mean value, and the “AVERAGE” function calculates the average value of a set of numbers.

Calculate the mean squared error using SUM and SQRT functions

A sixth method to calculate the Mean Squared Error in Excel is to use the “SUM” and “SQRT” functions. The “SUM” function calculates the sum of a set of numbers, and the “SQRT” function calculates the square root of a number.

Calculate the mean squared error using MMULT and TRANSPOSE functions

A seventh method to calculate the Mean Squared Error in Excel is to use the “MMULT” and “TRANSPOSE” functions. The “MMULT” function calculates the matrix product of two arrays, and the “TRANSPOSE” function transposes an array or range from rows to columns or vice versa.

Calculate the mean squared error using pivot table

An eighth method to calculate the Mean Squared Error in Excel is to use a pivot table. A pivot table is a tool in Excel that allows you to summarize, sort, and analyze data in a flexible and dynamic way.

Calculate the mean squared error using SUMIF function

A ninth method to calculate the Mean Squared Error in Excel is to use the “SUMIF” function. The “SUMIF” function adds up the values in a specified range that meet a certain criteria.

Calculate the mean squared error using INDEX MATCH

A tenth method to calculate the Mean Squared Error in Excel is to use the “INDEX” and “MATCH” functions. The “INDEX” function returns the value of a cell in a specified range based on the row and column number, and the “MATCH” function returns the position of a value in a specified range.

Related posts:

How to calculate Kurtosis in Excel Everything about Standard Error in Excel Square Root in Excel