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

Leave a Reply

Your email address will not be published.