How To Calculate Mean Squared Error Using Excel

Mean Squared Error (MSE) or Mean Square Deviation (MSD) is used to determine the measure of the average of the square of error for any model. In this tutorial, we are going to see how you can calculate the Mean Squared Error or Mean Squared Deviation In Excel by using just simple 3 steps.

Table of Contents

As we have already finished SMAPE, where we learned, that the smaller value we get the better model will be. The same goes for MSE.

Without further delay, let’s head to our main topic. Here we break down all these processes in just 3 steps. All you need is just follow these 3 points step-by-step.

1. Enter Actual and Forecasted Values separately

Insert Actual And Forecasted values in Excel separately

For now, I am just using the data 6 days a week. Then I assumed Actual Values and Forecasted Values.

2. Find the squared error

In order to find the squared Error consider this formula: (actual – forecast)2. We are going to use this formula in order to find the Squared Error of each row.

Find the squared error

I used Column D which indicates the Squared Error and Column E which represents the formula.

3. Determine Mean Squared Error

All you need is now to find the average of the Squared Error, it will become the MSE.

Determine Mean Squared Error

The MSE of this particular data model turns out to be approx 19.66.

Why We don’t Recommend Mean Squared Error

Now a day people prefer Mean Absolute Percentage Error (MAPE) over MSE because it is easy for a user and a developer to understand the model.

MSE is scaled-dependent, so it is very difficult for us to use it in business fields. People understand percentages better than squared errors.

In some rare cases, when the percentage makes no sense then we can use MSE indeed.

Leave a Comment