How to Calculate Root Mean Square Error in Excel

When comparing two data sets, we employ root mean square error in a variety of applications. Moreover, the root mean square error is one of the most used statistics in GIS. This article will guide you to calculate the root mean square error in Excel with some easy and quick methods. Let’s see the methods below.

Introduction to Root Mean Square Error (RMSE)

The Root Mean Square Error (RMSE) calculates the amount of error between 2 datasets. That means, it compares a predicted value to an observed or known value. Hence, the lower the RMSE, the closer the anticipated and observed values are.

How to Calculate Root Mean Square Error in Excel: 3 Quick Methods

In this article, we will discuss 3 quick methods to calculate the root mean square error in Excel. Furthermore, we have used some excellent examples with explanations for a better understanding of the methods. Here, we have used a dataset (B4:C8) like the screenshot below which contains some Expected and Real values. Now, we need to calculate the root mean square error of them. So, without further delay, let’s get started.

1. Apply SUMSQ Function to Calculate Root Mean Square Error in Excel

1.1 First Scenario

In the first method, we will apply the SUMSQ function in Excel to calculate the root mean square error. Here, we have used a combined formula that contains the COUNTA function and the SQRT function also. The SUMSQ function in Excel finds the sum of the squares of a set of numbers. Let’s see the steps below to use this function for calculating the root mean square error in Excel.

Steps:

First, select cell C10.
Secondly, to get the root mean square error, type the formula:

=SQRT(SUMSQ(B5:B8-C5:C8)/COUNTA(B5:B8))

Here, the range B5:B8 indicates the Expected Values and C5:C8 indicates the Real Values.

🔎 How Does the Formula Work?

SUMSQ(B5:B8-C5:C8)

This will first square the differences between the Expected and Real Values and then calculate their sum.

COUNTA(B5:B8)

It counts the number of non-empty cells in the B5:B8 range.

SQRT(SUMSQ(B5:B8-C5:C8)/COUNTA(B5:B8))

This will calculate the square root of the whole calculation.

Finally, press the Ctrl + Shift + Enter keys and you will get the result like the screenshot below.

1.2 Second Scenario

The dataset (B4:C8) below contains some Expected Values (B5:B8) and Real Values (C5:C8). Here, we will learn to find the root mean square error from the differences between Expected and Real Values using the SUMSQ function. The steps are below.

Steps:

In the beginning, we need to find the differences between the Expected and Real Values. For this, select the cell D5 and type the formula:

=B5-C5

After pressing Enter, we will get the value of the difference in cell D5.
Thereupon, to get all the differences, drag the fill handle.

In this way, we have got all the differences.
Now, select the cell C10, and to find the root mean square error, type the formula in the cell:

=SQRT(SUMSQ(D5:D8)/COUNTA(D5:D8))

In this formula, the range D5:D8 refers to the differences between Expected and Real Values. The SUMSQ function will square the differences between the Expected and Real Values. The COUNTA function will count the non-empty cells of the selected range and finally, the SQRT function will calculate the square root of the whole calculation.

In the end, click the Ctrl + Shift + Enter button to get the value of the root mean square error (RMSE). We can see the final result in the screenshot below.

2. Find Root Mean Square Error Using Excel AVERAGE Function

You may also use the AVERAGE function in Excel to determine the root mean square error. To acquire the difference between the Expected Values and the Real Values as shown in the picture below, first, follow the instructions in the way indicated in the SUMSQ function method above. Now, we need to calculate the square of the differences then the Mean Square Error (MSE), and finally the Root Mean Square Error (RMSE). The steps to do so are below.

Steps:

Firstly, to calculate the square of the difference, type the formula in the cell E5:

=D5^2

After pressing Enter, we will get the result.

Eventually, drag the fill handle to find the square for all the difference values.

As a result, we have got all the results.
At this time, to find the mean square error (MSE) using the Average function type the formula in cell C10:

=AVERAGE(E5:E8)

Here, the range E5:E8 indicates the square of the difference values.

Subsequently, press the Enter key to get the result.

Therefore, to calculate the root mean square error, type the formula in cell C11:

=SQRT(C10)

Here, C10 denotes the mean square error (MSE) value.

Lastly, press the Enter button to find the result.

3. Root Mean Square Error Calculation with Excel RMSE Formula

Utilizing the Excel RMSE formula is another way to determine the root mean square error (RMSE) of a dataset. This combined formula contains the SQRT function, the SUM function, and the COUNT function respectively. For this method, first, calculate the square of the difference values by following method 2. The steps for calculating the root mean square error using the RMSE formula are below.

To calculate the RMSE type the formula in cell C10:

=SQRT(SUM(E5:E8)/COUNT(E5:E8))

In the formula, the range E5:E8 indicates the squares of the differences.

🔎 How Does the Formula Work?

SUM(E5:E8)

It sums up the values in the range E5:E8.

COUNT(E5:E8)

This counts the number of cells in the range E5:E8.

SQRT(SUM(E5:E8)/COUNT(E5:E8))

It calculates the square root of the whole calculation.

Lastly, click Ctrl + Shift + Enter to get the result.

Download Practice Workbook

Download the practice workbook from here.

Calculating Root Mean Square Error.xlsx

Conclusion

I hope the above methods will be helpful for you to calculate root mean square error in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.