Excel guide · 6 min read
How to calculate a tracking signal in Excel
=SUM($D$2:Dn)/AVERAGE($F$2:Fn). Values beyond +/-4 mean the forecast is consistently too low (positive) or too high (negative).Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Forecast | Actual | Error | Running sum | Abs error | MAD | Tracking signal | Flag | Upper limit | 4 | |
| 2 | Jan | 500 | 520 | 20 | 20 | 20 | 20 | 1 | OK | Lower limit | -4 | |
| 3 | Feb | 500 | 470 | -30 | -10 | 30 | 25 | -0.4 | OK | |||
| 4 | Mar | 500 | 515 | 15 | 5 | 15 | 21.666666667 | 0.23076923077 | OK | |||
| 5 | Apr | 500 | 490 | -10 | -5 | 10 | 18.75 | -0.26666666667 | OK | |||
| 6 | May | 500 | 530 | 30 | 25 | 30 | 21 | 1.1904761905 | OK | |||
| 7 | Jun | 500 | 480 | -20 | 5 | 20 | 20.833333333 | 0.24 | OK | |||
| 8 | Jul | 510 | 540 | 30 | 35 | 30 | 22.142857143 | 1.5806451613 | OK | |||
| 9 | Aug | 510 | 550 | 40 | 75 | 40 | 24.375 | 3.0769230769 | OK | |||
| 10 | Sep | 510 | 545 | 35 | 110 | 35 | 25.555555556 | 4.3043478261 | Too low | |||
| 11 | Oct | 510 | 565 | 55 | 165 | 55 | 28.5 | 5.7894736842 | Too low | |||
| 12 | Nov | 520 | 570 | 50 | 215 | 50 | 30.454545455 | 7.0597014925 | Too low | |||
| 13 | Dec | 520 | 585 | 65 | 280 | 65 | 33.333333333 | 8.4 | Too low |
Download this sheet as an Excel practice file (.xlsx)
Set up the error, running sum and MAD columns
The sample is a year of monthly sales for one product. The forecast was set at 500 a month and nudged up to 510 and 520 later, while real demand started climbing in July. Four helper columns turn that into a tracking signal, and each one is a formula you fill down from row 2:
- D, error:
=C2-B2(actual minus forecast). Positive means you sold more than forecast. - E, running sum of forecast errors (RSFE):
=SUM($D$2:D2). The first reference is locked, the second moves, so each row adds up every error so far. - F, absolute error:
=ABS(D2). This is the size of the miss, whichever way it went. - G, MAD so far:
=AVERAGE($F$2:F2), the average size of a miss up to that month. H is the tracking signal,=E2/G2.
Result: 280
280: across the year the forecast fell 280 units short, and 275 of that came from July to December.
The tracking signal and the +/-4 limits
The tracking signal measures the running error in units of MAD. A good forecast misses in both directions, the errors cancel, and the signal wanders around zero. A biased forecast keeps missing the same way, the running sum grows faster than MAD, and the signal climbs.
Put the limits in input cells (L1 = 4, L2 = -4) instead of typing 4 into formulas. Plus or minus 4 is a common choice; planners also use tighter limits for important items or wider ones for noisy, low-volume items, so pick a value per item group and write it down.
In the sample, the signal sits between -0.4 and 1.6 for the first seven months, then jumps to 3.1 in August and 4.3 in September. That's the month it crosses the limit.
Result: spills 12 rows × 2 columns
| Jan | 1 |
| Feb | -0.4 |
| Mar | 0.23 |
| Apr | -0.27 |
| May | 1.19 |
| Jun | 0.24 |
| Jul | 1.58 |
| Aug | 3.08 |
| Sep | 4.3 |
| Oct | 5.79 |
| Nov | 7.06 |
| Dec | 8.4 |
Jan 1, Feb -0.4, Mar 0.23, Apr -0.27, May 1.19, Jun 0.24, Jul 1.58, Aug 3.08, Sep 4.3, Oct 5.79, Nov 7.06, Dec 8.4.
Flag biased months and find the first one
Column I labels each month with =IF(H2>$L$1,"Too low",IF(H2<$L$2,"Too high","OK")). With error defined as actual minus forecast, a signal above the upper limit means the forecast is too low and you risk stockouts; below the lower limit it's too high and you're building stock.
To report the first month that tripped either limit, search the signal column for the first absolute value over the limit. XLOOKUP (Excel 365 and 2021) returns the month, or "never" if it stayed inside.
If your team defines error the other way round (forecast minus actual, as the forecast accuracy guide does), the signs flip: a positive signal then means the forecast is too high. Nothing else changes, but label the flag column so nobody reads it backwards.
Result: Sep
Sep: the first month the forecast was biased enough to trip the limit.
Why it catches drift that MAPE misses
MAPE averages the size of each miss as a percentage of actual. For this product it's only 6.2%, so the forecast looks about 94% accurate and nobody would look twice.
But accuracy ignores direction. From July on, every single month was under-forecast, by 30 to 65 units. That's a forecast that hasn't caught up with demand, and it's the kind of error you can fix by changing the model or the inputs. The tracking signal is built to see it: a 6% miss that's random is fine, a 6% miss that's always the same way is a problem.
Use both. MAPE or weighted MAPE tells you how big the misses are; the tracking signal tells you whether they lean one way.
Result: 0.061564558056 shows as 6.2% with Percent format
0.0616: an average miss of 6.2%, which hides the six months of one-way error.
Cumulative or rolling? The signal can't exceed the number of months
Two maths facts change how you read the number. First, the absolute running sum can never be bigger than the sum of absolute errors, so the tracking signal can never be bigger than the number of months counted. After one month it's always 1 or -1; after three months it's at most 3. A +/-4 limit cannot trip before month 5, so don't judge a new forecast from its first few months.
Second, a cumulative signal keeps old history. If the forecast was fixed in October, the big running sum from earlier months would keep the signal above 4 for a long time. Many planners reset the sums after a model change, or use a rolling window of the last 6 months instead.
For the last 6 months, the window's error sum divided by the window's MAD gives 6.0: all six errors were positive, so it hits the maximum possible for a 6-month window. In Excel 365, =SUM(TAKE(D2:D13,-6))/AVERAGE(TAKE(F2:F13,-6)) always uses the latest 6 rows as you add months.
Result: 6
6: every one of the last six errors went the same way, so the rolling signal is at its ceiling of 6.
Divide-by-zero and other things that break it
If the first month's forecast is exactly right, MAD is 0 and E2/G2 returns #DIV/0!. The sample uses =IF(G2=0,0,E2/G2) so a perfect month shows a signal of 0 instead of an error.
Keep blank months out. If a month has no actual yet, don't fill the error formula into it: =C14-B14 with an empty C14 gives a negative error equal to the whole forecast, and the signal dives. Either stop the formulas at the last month with data or wrap them in IF(C14="","",...).
And compare like with like. Track the forecast you actually used for planning (for example the one locked a month ahead), not the latest re-forecast. A forecast that's overwritten every month will always look unbiased.
Common mistakes
Dividing by the average error instead of the average absolute error
Signed errors cancel out, so the average error can be near zero and blow the signal up. MAD must use ABS: =AVERAGE($F$2:F2) where F = ABS(error).
Forgetting the $ in the running sum
=SUM(D2:D2) filled down becomes a one-cell sum. Lock the start: =SUM($D$2:D2).
Reading the sign backwards
With error = actual - forecast, positive means under-forecasting. With forecast - actual it's the opposite. Write the convention next to the limits.
Judging the signal after two or three months
The signal can't exceed the number of months counted, so early values say nothing. Wait until there are more months than your limit.
FAQ
What is a good tracking signal value?
Close to zero, moving up and down. That means misses go both ways. A value that keeps growing in one direction, and especially one outside about +/-4, means the forecast is biased.
What is the formula for tracking signal?
Tracking signal = running sum of forecast errors (RSFE) / mean absolute deviation (MAD). In Excel: =SUM($D$2:D13)/AVERAGE($F$2:F13) with errors in D and absolute errors in F.
What does a negative tracking signal mean?
With error defined as actual minus forecast, negative means the forecast has been too high on balance. If your sheet defines error as forecast minus actual, negative means too low.
How do I calculate a tracking signal for many products?
Keep one row per product and month in a table, then calculate per product with SUMIFS for the running error and AVERAGEIFS for MAD, both limited to that product and to months up to the current one. Keep the limits in input cells so every product is judged by the same rule.
Functions used
SUMAdds numbersABSAbsolute valueAVERAGEMean of numbersIFPicks a result from a testXLOOKUPModern lookup in any directionHSTACKJoins arrays side by sideROUNDRounds to digitsTAKEFirst or last rows, spills
Related guides
- How to do an exponential smoothing forecast in Excel
- How to calculate forecast accuracy in Excel
- How to make a running total (cumulative sum) in Excel
- How to build a rolling forecast in Excel
- How to make negative numbers positive in Excel
- How to calculate a project budget burn rate in Excel
- #NAME? error in Excel
Updated