I recently made a code review and received a message asking me how to calculate root mean squared error (RMSE) from data in Excel.
Unfortunately, Excel does not have a function to calculate RMSE, so it is important to know the formula for computing this statistic:-
Mean squared error (MSE) is the sum of the squared differences between the predicted and observed values, being divided by the number of observations. The square root of this value is taken, resulting in the RMSE.
In Excel, the difference between the observed values and predicted values is calculated and it is this difference that is used to calculate the RMSE.
The formula in Excel to calculate RMSE (based on a column of data C2 to C11, representing the difference between the observed and predicted data) is:-
=SQRT(SUMSQ(C2:C11)/COUNTA(C2:C11))
The SQRT function calculates the square root of a value.
The SUMSQ returns the sum of the squares of the values provided.
An example of how to calculate RMSE in Excel can be seen in the screen shot below:-
