# How to calculate MAPE in Excel

Average the absolute errors as a share of the actuals: `=SUMPRODUCT(ABS((B2:B6-C2:C6)/B2:B6))/COUNT(B2:B6)`.

**Excel:** `=SUMPRODUCT(ABS((B2:B6-C2:C6)/B2:B6))/COUNT(B2:B6)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Week | Actual units | Forecast |  | MAPE |
| W1 | 480 | 452 |  |  |
| W2 | 515 | 540 |  |  |
| W3 | 390 | 410 |  |  |
| W4 | 620 | 575 |  |  |
| W5 | 555 | 560 |  |  |

Formula in F1: `=SUMPRODUCT(ABS((B2:B6-C2:C6)/B2:B6))/COUNT(B2:B6)`

Result: 4.8%

## How it works

For each week, the error is divided by the actual: 28 units off on 480 is 5.8%. ABS makes over- and under-forecasts count the same, SUMPRODUCT adds the five percentages, and dividing by the count gives the average: the forecast is off by about 4.8% in a typical week.

## Other ways

- `=AVERAGE(ABS((B2:B6-C2:C6)/B2:B6))`: Excel 365 averages the array directly. Older versions need Ctrl+Shift+Enter; Google Sheets needs ARRAYFORMULA.

## Common mistakes

- A week with 0 actual units makes the whole formula #DIV/0!. MAPE doesn't work with zeros; leave those weeks out or use weighted MAPE.
- Divide by the actual, not the forecast. Dividing by the forecast gives a different, less standard number.

## Questions

**How do I calculate weighted MAPE (WAPE)?**

Divide the total absolute error by the total actual: `=SUMPRODUCT(ABS(B2:B6-C2:C6))/SUM(B2:B6)`.

**How do I show forecast accuracy instead?**

Use `=1-MAPE`, here about 95.2%.

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-mape