Mean Squared Error (MSE) is an important calculation in regression modeling. It is the mean of the squared difference between the actual values and the expected (forecasted) values.
If you’re doing data analysis in Excel that requires you to calculate MSE, it would be useful to know how to do that in Excel (given that there is no dedicated formula to do this in Excel).
What is Mean Squared Error?
In statistics, we use the below formula to find the mean squared error.
Mean Squared Error (MSE)=(Forecasted value-Actual value)2No. of observations
A low mean squared error value implies that the model is good at forecasting. If the mean squared error is large, the model is unsuitable for predicting.
You can use any of the below methods to calculate the Mean Squared Error in Excel.
Method 1 – Find the Average of the Squared Values to Calculate the Mean Squared Error
Below is a data set with the forecasted number of new subscribers to a Youtube channel in column B and the actual number of new subscribers to the Youtube channel in column C.
In column D, the difference between the forecasted and the actual number of new subscribers is calculated.
Now I need to calculate the mean squared error for the below data set.
To find the mean squared error by finding the average of the squared values, you have to follow the below steps.
Step 1 – Calculate the squared value of each month’s difference
You can use the below formula to calculate the squared value of the January month difference and then copy that formula to the below cells.
=D2^2
Step 2 – Calculate the mean squared error
To find the mean squared error, you have to find the average value of step 1’s calculated values (in column E).
You can use the below formula for that.
=AVERAGE(E2:E13)
The syntax of the AVERAGE function is AVERAGE(number1,[number2],…).
The AVERAGE function supports arrays as well. So, you can select the entire squared values of differences as a range to the first parameter of the function.
Then you will get 304 as the mean squared value of the data set.
Also read: How to Calculate Variance in Excel?
Method 2 – Divide the Manually Calculated Sum of Squared Differences by the Number of Observations to Calculate the Mean Squared Error
Another way to calculate the Mean squared error is to divide the manually calculated sum of squared differences by the number of observations.
Below is a data set with the forecasted number of new subscribers to a Youtube channel in column B and the actual number of new subscribers to the Youtube channel in column C.
In column D, the difference between the forecasted and the actual number of new subscribers is calculated.
Now I need to calculate the mean squared error for the below data set.
To find the mean squared error by dividing the manually calculated sum of squared differences by the number of observations, you have to follow the below steps.
Step 1 – Calculate the squared value of each difference
You can use the below formula to calculate the squared value of the January month difference and then copy that formula to the below cells.
=D2^2
Step 2 – Calculate the sum of the squared values
You have to find the total value of step 1’s calculated values (in column E).
You can use the below formula for that.
=SUM(E2:E13)
The syntax of the SUM function is SUM(number1,[number2],…).
The SUM function supports arrays as well. So, you can select the entire squared values of differences as a range to the first parameter of the function.
You will get 3,650 as the sum of squared values.
Step 3 – Calculate the total number of observations
You have to find the number of observations in the data set.
You can use the below formula for that.
=COUNT(E2:E13)
The syntax of the COUNT function is COUNT(value1,[value2],…).
The COUNT function supports arrays as well. You can enter any data, which are numbers or dates, for the COUNT function.
So, you can select any column in the data set except the month column. In this example, I have used column D to get the count of the observations.
Then you get 12 as the number of observations.
Step 4 – Calculate the mean squared error.
To find the mean squared error, you have to divide the sum of the squared differences by the number of observations.
You can use the below formula for that.
=H2/H3
When you divide 3,650 by 12, you will get 304. It is the mean squared error.
Also read: How to Calculate Covariance in Excel?
Method 3 – Divide the Sum of the Squared Differences by the Number of Observations to Calculate the Mean Squared Error
Using the below method, we can find the mean squared error without adding a new column for the squared differences for each month.
Below is a data set with the forecasted number of new subscribers to a Youtube channel in column B and the actual number of new subscribers to the Youtube channel in column C.
In column D, the difference between the forecasted and the actual number of new subscribers is calculated.
Now I need to calculate the mean squared error for the below data set.
To find the mean squared error by dividing the manually calculated sum of squared differences by the number of observations, you have to follow the below steps.
Step 1 – Calculate the sum of the squared values
You have to find the sum of the squared values of the differences (in column D).
You can use the below formula for that.
=SUMSQ(D2:D13)
The syntax of the SUMSQ function is SUMSQ(number1,[number2],…).
The SUMSQ function supports arrays as well. So, you can select all the differences as a range to the first parameter of the function.
You will get 3,650 as the sum of squared values.
Step 2 – Calculate the total number of observations
You have to find the number of observations in the data set.
You can use the below formula for that.
=COUNT(D2:D13)
The syntax of the COUNT function is COUNT(value1,[value2],…).
The COUNT function supports arrays as well. You can enter any data, which are numbers or dates, for the COUNT function.
So, you can select any column in the data set except the month column. In this example, I have used column D to get the count of the observations.
Then you get 12 as the number of observations.
Step 3 – Calculate the mean squared error.
To find the mean squared error, you have to divide the sum of the squared differences by the number of observations.
You can use the below formula for that.
=G2/G3
When you divide 3,650 by 12, you will get 304. It is the mean squared error.
Also read: How to Square a Number in Excel?
Method 4 – Using a Single Formula to Calculate the Mean Squared Error
Even Though there is no direct function to calculate the Mean Squared Error in Excel, you can use a single formula to find the mean squared error without adding new columns to your data set.
Below is a data set with the forecasted number of new subscribers to a Youtube channel in column B and the actual number of new subscribers to the Youtube channel in column C.
In column D, the difference between the forecasted and the actual number of new subscribers is calculated.
Now I need to calculate the mean squared error for the below data set.
You can use the below formula and calculate the mean squared error in a single step.
=SUMSQ(D2:D13)/COUNT(D2:D13)
The syntax of the SUMSQ function is SUMSQ(number1,[number2],…).
The SUMSQ function supports arrays as well. So, you can select all the differences as a range to the first parameter of the function.
The syntax of the COUNT function is COUNT(value1,[value2],…).
The COUNT function supports arrays as well. You can enter any data, which are numbers or dates for the COUNT function.
So, you can select any column in the data set except the month column. In this example, I have used column D to get the count of the observations.
If you evaluate this formula, it works like this.
Below, image 1 shows the formula that you have used to find the MSE.
Below, image 2 shows the value of the first expression of the formula. So, 3,650 represents the sum of the squared values of the data set.
Below, image 3 shows the value of the second expression of the formula. So, the number of observations of the data set is 12.
Below, image 4 shows the final result of the given formula. So, 304 is the mean squared error of the data set.
In this final method, you need only a single cell to calculate the mean squared error.
Therefore, it will help you to find the value without taking up too much space on your Excel sheet.
In this lesson, you have learned to find the mean squared error by creating a new column for squared values of differences as well as without that.
If you want to do the calculation in a single step, you can use the last method.
Other Excel articles you may also like:
