How to Calculate Mean Squared Error (MSE) in Excel


One of the most common metrics used to measure the forecast accuracy of a model is MSE, which stands for mean squared error. It is calculated as:

MSE = (1/n) * Σ(actual – forecast)2

where:

  • Σ – a fancy symbol that means “sum”
  • n – sample size
  • actual – the actual data value
  • forecast – the forecasted data value

The lower the value for MSE, the better a model is able to forecast values accurately.

How to Calculate MSE in Excel

To calculate MSE in Excel, we can perform the following steps:

Step 1: Enter the actual values and forecasted values in two separate columns.

How to calculate MSE in Excel

Step 2: Calculate the squared error for each row.

Recall that the squared error is calculated as: (actual – forecast)2. We will use this formula to calculate the squared error for each row.

Column D displays the squared error and Column E shows the formula we used:

Mean squared error in Excel

Repeat this formula for each row:

MSE calculation in Excel

Step 3: Calculate the mean squared error.

Calculate MSE by simply finding the average of the values in column D:

MSE in Excel

The MSE of this model turns out to be 5.917.

Additional Resources

Two other popular metrics used to assess model accuracy are MAD – mean absolute deviation, and MAPE – mean absolute percentage error. The following tutorials explain how to calculate these metrics in Excel:

How to Calculate Mean Absolute Deviation (MAD) in Excel
How to Calculate Mean Absolute Percentage Error (MAPE) in Excel

One Reply to “How to Calculate Mean Squared Error (MSE) in Excel”

  1. Very useful article, everything described in very convenient form, without mathematical/statistical hard language. I very appreciate it, thank You brother!

Leave a Reply

Your email address will not be published. Required fields are marked *