Introduction to the Root Mean Square Error (RMSE)
The Root Mean Square Error (RMSE) calculates the amount of error between 2 datasets. 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 the Root Mean Square Error in Excel: 3 Quick Methods
We have a dataset (B4:C8) like the screenshot below which contains some Expected and Real values. We need to calculate the root mean square error of them.
Method 1 – Apply the SUMSQ Function to Calculate the Root Mean Square Error in Excel
Case 1.1 – Using SQRT and COUNTA
Steps:
- Select cell C10.
- To get the root mean square error, use 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.
- Press the Ctrl + Shift + Enter keys, and you will get the result like the screenshot below.
Case 1.2 – Using a Helper Column
The dataset (B4:C8) below contains some Expected Values (B5:B8) and Real Values (C5:C8).
Steps:
- We need to find the differences between the Expected and Real Values. For this, select the cell D5 and use the formula:
=B5-C5
- After pressing Enter, we will get the value of the difference in cell D5.
- To get all the differences, drag the fill handle.
- Select the cell C10, and use the following formula in the cell:
=SQRT(SUMSQ(D5:D8)/COUNTA(D5:D8))
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.
- 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.
Read More: How to Calculate Mean Squared Error in Excel
Method 2 – Find the Root Mean Square Error Using the AVERAGE Function
We calculated the differences between the Expected Values and the Real Values as shown in the picture below.
Steps:
- To calculate the square of the difference, use this formula in the cell E5:
=D5^2
- After pressing Enter, we will get the result.
- Drag the fill handle to find the square for all the difference values.
- To find the mean square error (MSE) using the Average function, use this formula in cell C10:
=AVERAGE(E5:E8)
- Press the Enter key to get the result.
- To calculate the root mean square error, use this formula in cell C11:
=SQRT(C10)
- Press the Enter key to find the result.
Method 3 – Root Mean Square Error Calculation with Excel RMSE Formula
- Calculate the square of the difference values by following Method 2.
- To calculate the RMSE, use this formula in cell C10:
=SQRT(SUM(E5:E8)/COUNT(E5:E8))
The range E5:E8 indicates the squares of the differences.
How Does the Formula Work?
- SUM(E5:E8)
Sums up the values in the range E5:E8.
- COUNT(E5:E8)
Counts the number of cells in the range E5:E8.
- SQRT(SUM(E5:E8)/COUNT(E5:E8))
Calculates the square root of the whole calculation.
- Click Ctrl + Shift + Enter to get the result.
Download the Practice Workbook
Related Articles
- How to Remove #DIV/0! Error in Excel
- How to Remove Value Error in Excel
- [Fixed!] NUM Error in Excel
- [Fixed!] VALUE Error in Excel
- Excel Error: The Number in This Cell is Formatted as Text
<< Go Back To Errors in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
