Root Mean Square Error (RMSE) is used to measure the error between the actual value and the expected value. In short, it compares the two data sets, and if the RMSE value is small then we can say that the actual and expected values are close to each other. In this post, we are going to see how to calculate RMSE in excel.
Table of Contents
RMSE is also known as the name of Root Mean Square Deviation. We use RMSE when it is necessary to compare actual and expected values.
How do you calculate RMSE in Excel?
In this tutorial, we are going to see how you can calculate root mean square deviation with 3 different methods. We are also going to mention the formula for each step that will help you to understand the whole process even better.
Method 1: SUMSQ Function
First, you need to enter actual and expected values. In my case, I am assuming some random numbers. Then find the difference between actual values and expected values.
Now you can use the SUMSQ Function to calculate the MSE (Mean Squared Error). You have to calculate MSE before RMSE. Once you calculate MSE, just find the square root of MSE i.e., RMSE.
For MSE =SUMSQ(D2:D13)/COUNT(D2:D13)
For RMSE =SQRT(F3)We have successfully found the RMSE by using SUMSQ Function. It is one of the easiest methods.
Method 2: AVERAGE Function
In this method, you need to make an extra column for the “Square of the differences”. As you can see below, the Actual, Expected, and Difference Column is as it is and we only added one extra column that contains the square of the differences.
For Square Of Differences =D2^2
Now all you need is to find the average of your “Square Of Differences”. You can calculate it by using the above formula.
=AVERAGE(E2:E13)
The output of this step is MSE (Mean Square Error). In order to find RMSE, you have to find the square root of MSE as we did earlier in SUMSQ Function.
=SQRT(H3)
As you can see we obtain the same result that we have already obtained from SUMSQ Function.
Method 3: RSME Formula
RSME formula is also one of the easiest ways to calculate RMSE in excel. All you need is to find the sum of the square of differences and divide it by the total number of counts. And then take the square of the obtained value.
=SQRT(SUM(E2:E13)/COUNT(E2:E13))
FAQ
For RMSE the lower the value the better it is. It is dependent on the actual and expected value whether you get a lower or higher value of RMSE.
The higher RMSE value means that the data model is not a good fit. Again it is much more dependent on the expected and actual value.
As you have already observed that in all the methods we have the same answer in the end. No matter what method you apply the answer will never change at all.
So it is all about calculating RMSE in excel. I hope you find this post helpful. If you have any issues regarding this topic let us know by commenting below.
Source: Wikipedia
