# How to calculate RMSE in Excel

Square the errors, average them and take the square root: `=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))`.

**Excel:** `=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Month | Actual | Forecast |  | RMSE |
| Jan | 120 | 112 |  |  |
| Feb | 135 | 141 |  |  |
| Mar | 128 | 125 |  |  |
| Apr | 150 | 138 |  |  |
| May | 142 | 147 |  |  |
| Jun | 160 | 154 |  |  |

Formula in F1: `=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))`

Result: 7.23

## 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.

## Other ways

- `=SQRT(AVERAGE((B2:B7-C2:C7)^2))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-rmse