How to calculate RMSE in Excel
Formula (Excel and Google Sheets)
=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))Square the errors, average them and take the square root: =SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7)).
- Result on the example
- 7.23 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
RMSE is "root, mean, square" read backwards. Each error (8, -6, 3, 12, -5, 6) is squared so negatives don't cancel and big misses count more. SUMPRODUCT adds the squares (314), dividing by the count gives the mean, and SQRT brings it back to the original units: about 7.2 units off on average.
=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))Result7.234178138
B2:B76 rows × 1 col: 120, 135, 128, 150, …A range: 6 cells in one column.
C2:C76 rows × 1 col: 112, 141, 125, 138, …A range: 6 cells in one column.
B2:B7-C2:C7{6 rows × 1 col} 8, -6, 3, 12, …The - operator subtracts.
B2:B7-C2:C7)^2{6 rows × 1 col} 64, 36, 9, 144, …The ^ operator raises to a power.
SUMPRODUCT((B2:B7-C2:C7)^2)314SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B7-C2:C7)^2{6 rows × 1 col} 64, 36, 9, 144, …
- array1
COUNT(B2:B7)6COUNT: Counts cells that contain numbers.- value1
B2:B76 rows × 1 col: 120, 135, 128, 150, …
- value1
SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7)52.33333333The / operator divides.
SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))7.234178138SQRT: Square root.- number
SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7)52.33333333
- number
In Google Sheets
The same formula works in Google Sheets: =SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7)). We ran it in Google Sheets mode on the same data and it returns 7.23.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SQRT(AVERAGE((B2:B7-C2:C7)^2))Excel only | 7.23 | Excel 365 averages the squared errors directly. Older versions need Ctrl+Shift+Enter; Google Sheets needs ARRAYFORMULA. |
Common mistakes
- Dividing by COUNT-1 instead of COUNT gives a different number. RMSE for forecasts divides by the number of points.
- Blank forecasts are treated as 0 inside the subtraction and produce huge errors. Keep both columns complete, or filter the rows first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate RMSE
F1, calculate the root mean square error of the forecasts in C2:C7 against the actuals in B2:B7.Questions
How do I calculate the mean absolute error (MAE)?
Use =SUMPRODUCT(ABS(B2:B7-C2:C7))/COUNT(B2:B7), which gives 6.67 here.
Is there a built-in function?
No single one, but =SQRT(SUMXMY2(B2:B7,C2:C7)/COUNT(B2:B7)) uses SUMXMY2, which adds the squared differences of two ranges.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.