Forecast Accuracy in Excel: MAPE, MAE and Bias

Forecast accuracy in Excel is measured by comparing each forecast with what really happened, then summarising the misses. Three numbers cover most needs: MAE (how far off you were, in units), MAPE (how far off, as a percentage) and bias (whether you tend to forecast too high or too low). This guide calculates all three on one small table, with formulas that work in any version of Excel, and explains how to read them.

The example data#

All formulas use six months of actual sales in B2:B7 and the forecasts made for them in C2:C7:

Columns D to F are helper columns. You can build them to see each month, or skip them and use the one-cell formulas below.

Step 1: calculate the error for each period#

The error is the gap between the forecast and the actual. In this guide, error means forecast minus actual, so a positive error is an over-forecast. Some teams use actual minus forecast instead; either is fine, as long as you say which.

In D2, type =C2-B2 and fill down. In E2, wrap it in ABS to drop the sign: =ABS(C2-B2). ABS returns the absolute value, so -10 becomes 10. See how ABS works in Excel for more uses.

In F2, divide the absolute error by the actual: =E2/B2, formatted as a percentage. January's forecast missed by 10 on an actual of 120, which is 8.33%.

MAE: the average miss in units#

MAE (mean absolute error) is the average of the absolute errors. With the helper column, it's simply:

=AVERAGE(E2:E7)

Without helper columns, SUMPRODUCT does the ABS and the sum in one cell:

=SUMPRODUCT(ABS(B2:B7-C2:C7))/COUNT(B2:B7)

Both return 8.33: on average, the forecast was off by about 8 units a month. The AVERAGE function guide and the guide to AVERAGE, MIN and MAX cover the basics.

Why SUMPRODUCT? It accepts a calculation on whole ranges, like ABS(B2:B7-C2:C7), without special entry. In Microsoft 365 or Excel 2021, =AVERAGE(ABS(B2:B7-C2:C7)) also works. In older versions, that formula needs Ctrl+Shift+Enter (⌃⇧Return on a Mac), or it returns a wrong value or #VALUE!.

MAPE: the average miss as a percentage#

MAPE (mean absolute percentage error) averages the percentage errors, so you can compare products or regions of different sizes. With the helper column, it's =AVERAGE(F2:F7). In one cell:

=SUMPRODUCT(ABS(B2:B7-C2:C7)/B2:B7)/COUNT(B2:B7)

The result is 6.49%. A common way to report forecast accuracy is 1 minus MAPE:

=1-SUMPRODUCT(ABS(B2:B7-C2:C7)/B2:B7)/COUNT(B2:B7)

That gives 93.51%. Be careful with this number: if MAPE goes above 100%, "accuracy" turns negative, which confuses readers. The SUMPRODUCT function guide and the article on SUMPRODUCT in Excel explain how it handles arrays like these.

WAPE: when actuals can be zero#

MAPE divides by each actual, so a single month with an actual of 0 gives #DIV/0!, and very small actuals create huge percentages. WAPE (weighted absolute percentage error) divides the total error by the total actual instead:

=SUMPRODUCT(ABS(B2:B7-C2:C7))/SUM(B2:B7)

The total absolute error is 50 and the total actual is 780, so WAPE is 6.41%. WAPE gives big months more weight than small ones, which is often what a business cares about. It's the safer choice for items with slow or patchy sales, which are common in supply chain work in Excel.

Bias: do you forecast too high or too low?#

MAE and MAPE ignore the direction of the error. Bias keeps it, so over- and under-forecasts cancel out:

=SUMPRODUCT(C2:C7-B2:B7)/COUNT(B2:B7)

The result is 1.67: on average, the forecast was about 1.7 units too high. As a percentage of total actuals, =(SUM(C2:C7)-SUM(B2:B7))/SUM(B2:B7) gives 10 ÷ 780 = 1.28%.

A forecast can have low bias and still be inaccurate, if big misses in both directions cancel out. That's why you report bias alongside MAE or MAPE, not instead of them. A bias that stays positive or negative month after month suggests the forecasting method needs a change, such as a different moving average window.

Use absolute references for a tracking sheet#

When you add a column that compares each month with a target, lock the target cell. If H1 holds an acceptable error of 8%, this formula in G2 flags each month:

=IF(F2>$H$1,"Review","OK")

Fill it down. F2 changes to F3, F4 and so on, but $H$1 stays fixed. Without the $ signs, the formula would look at H2, H3 and other empty cells, and every month would show OK. In this example, January (8.33%) and March (9.09%) exceed 8% and show Review; the other months show OK.

Which measure should you use?#

Each measure answers a different question, so most reports show two or three:

Use MAE for one item in known units, MAPE or WAPE to compare items of different sizes, and bias to check for a steady lean in one direction.

Practise it#

Every measure here is an average of errors, with locked references to keep it right when you fill down. Practise AVERAGE, MIN and MAX in a live spreadsheet, then use the absolute references lesson to master $ signs. Each lesson checks your formulas instantly. Then try the MAPE exercise on this page.