How to calculate MAPE in Excel: forecast accuracy with a worked example

The MAPE formula

MAPE = average of |actual − forecast| ÷ actual

Work out the error of every period, take its absolute value so that misses in both directions count, scale it by the actual value and average the percentages. A MAPE of 4.9% means the forecast was off by 4.9% of the actual value in a typical month. Divide by the actual, not by the forecast. Some textbooks multiply by 100 at the end, but in Excel it is simpler to format the cell as a percentage.

This guide defines the error as actual − forecast, so a positive error means the forecast was too low. Some teams use the opposite sign. It does not change MAPE, but it flips the sign of the bias, so write your convention down.

Step by step in Excel

Actuals are in B2:B13 and forecasts in C2:C13. Then:

• D2: =B2-C2, the error.
• E2: =ABS(D2)/B2, the absolute percentage error, formatted as a percentage.
• Fill both down to row 13.
• MAPE: =AVERAGE(E2:E13) returns 4.9%.

Without helper columns, =SUMPRODUCT(ABS(B2:B13-C2:C13)/B2:B13)/COUNT(B2:B13) gives the same 4.9%. SUMPRODUCT handles the arrays in every Excel version, so you do not need Ctrl+Shift+Enter. In Excel 365, =AVERAGE(ABS((B2:B13-C2:C13)/B2:B13)) works too.

Worked example: grading a 12-month sales forecast

Actuals and forecasts for 2025 are the yellow inputs. Columns D and E and every measure below them are formulas.

Worked example: grading a 12-month sales forecast

	A	B	C	D	E
1	Month 2025	Actual ($)	Forecast ($)	Error	|Error| ÷ actual
2	Jan	42,800	44,000	-1,200	2.8%
3	Feb	39,600	41,500	-1,900	4.8%
4	Mar	47,300	45,800	1,500	3.2%
5	Apr	50,100	48,200	1,900	3.8%
6	May	53,900	51,000	2,900	5.4%
7	Jun	51,200	52,400	-1,200	2.3%
8	Jul	49,800	47,600	2,200	4.4%
9	Aug	46,500	48,900	-2,400	5.2%
10	Sep	55,700	52,300	3,400	6.1%
11	Oct	58,400	55,100	3,300	5.7%
12	Nov	66,900	61,800	5,100	7.6%
13	Dec	72,300	66,500	5,800	8.0%
14
15	MAE: mean absolute error ($)				2,733
16	MAPE: mean absolute percentage error				4.9%
17	MAPE in one formula, no helper columns				4.9%
18	WAPE: sum of |errors| ÷ sum of actuals				5.2%
19	Bias: average error with its sign ($)				1,617
20	Tracking signal: sum of errors ÷ MAE				7.10

The sales figures are fictional. Error = actual − forecast, so a positive error means the forecast was too low.

MAE: the same error in dollars

The mean absolute error averages the absolute errors without dividing by the actuals: =SUMPRODUCT(ABS(D2:D13))/COUNT(D2:D13) returns $2,733 a month. MAE is in the unit of the data, which makes it the number to use for a cash buffer, staffing or safety stock. MAPE is better for comparing forecasts of different sizes, such as a large and a small product line. If large misses hurt much more than small ones, also look at the root mean squared error, =SQRT(SUMSQ(D2:D13)/COUNT(D2:D13)), which squares the errors before averaging.

Bias and tracking signal: is the forecast off in one direction?

MAPE ignores the direction of the errors. The bias keeps it: =AVERAGE(D2:D13) returns +$1,617, so on average the forecast was $1,617 too low each month.

The tracking signal asks whether that bias is still random: sum of errors ÷ MAE = $19,400 ÷ $2,733 = 7.10. A common rule of thumb is to review the model when the signal leaves the range −4 to +4. Here it is far outside. The misses grow towards the end of the year: from September to December the forecast was between $3,300 and $5,800 too low every month, because sales grew faster than the forecast assumed. The fix is to re-estimate the trend and seasonality, not to add a fixed amount.

When MAPE breaks: zero and small actuals

Because MAPE divides by the actual value, a period with zero sales returns #DIV/0!, and a period with very small sales can dominate the average. If a product sells 2 units and the forecast was 6, that single month has an error of 200%, although the miss is only 4 units.

MAPE is also asymmetric. A forecast that is too low can miss by at most 100%, but a forecast that is too high has no upper limit. A model tuned to minimize MAPE therefore tends to forecast low. Wrapping the formula in IFERROR hides the zero periods instead of solving the problem, so say clearly which periods were left out.

WAPE: the weighted alternative

WAPE = sum of |errors| ÷ sum of actuals. In Excel: =SUMPRODUCT(ABS(D2:D13))/SUM(B2:B13), which returns 5.2%. WAPE, sometimes called weighted MAPE, gives every period a weight in proportion to its size, so a busy month counts more than a quiet one. It works with zero actuals as long as the total is not zero, which makes it the usual choice for intermittent demand in retail and supply chains.

Here WAPE is higher than MAPE because the largest misses happened in the largest months, November and December. MAPE treats every month the same, WAPE follows the dollars.

What is a good MAPE?

There is no universal threshold. Accuracy depends on the level, the horizon and the product: a monthly forecast of total company sales should be far more accurate than a weekly forecast for one item in one store. The fairest benchmark is a naive forecast, such as last month's actual or the same month last year. If your model does not beat it on MAPE and MAE, it adds work without adding accuracy. And always measure on periods the model had not seen when it was made.

Common mistakes

• Dividing by the forecast instead of the actual. MAPE scales each error by what actually happened.
• Forgetting ABS. Averaging signed percentage errors lets over- and under-forecasts cancel out. That number is a bias measure, not MAPE.
• Calculating MAPE from the totals. |sum of actuals − sum of forecasts| ÷ sum of actuals nets out the monthly misses and looks far better than it is.
• Hiding zero actuals with IFERROR. The periods quietly drop out of the average. Use WAPE or report the excluded periods.
• Looking at MAPE alone. A low MAPE can hide a forecast that is always too low. Check the bias and the tracking signal too.
• Grading a forecast on the data it was fitted on. In-sample errors are always flattering. Test on later periods.