How far off was the forecast, in percent? (ABS + IFERROR)
The problem
Problem statement
Forecast and actual revenue are in B2:C2 for row 2. Put the size of the miss — actual versus forecast, as a decimal without a sign — in the Variance % cell D2.
The answer is always zero or positive: over-delivery and under-delivery are both a miss of the same size. A forecast of 0 must return 0, not #DIV/0!.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Forecast | Actual | Variance % |
| 2 | Ada | 5000 | 5500 | |
| 3 | Ben | 4000 | 3600 |
Notes
Variance is (actual − forecast) ÷ forecast. ABS removes the direction, which is what a "how far off" question asks for. When the forecast is 0 the division is undefined, so IFERROR supplies 0 — decide that on purpose rather than letting the sheet show an error in a report.
How to approach
Your answer goes in D2.
- Target cell is D2
- Actual minus forecast, divided by forecast
- A sign-free answer needs ABS
- Shape: =IFERROR(ABS(?), ?)
Function guide
FAQ
How do I calculate forecast variance?
(actual − forecast) ÷ forecast. Add ABS when the direction does not matter, and IFERROR when the forecast can be zero.
Should variance keep its sign?
It depends on the question. "By how much did we miss?" wants ABS; "did we beat or miss?" wants the sign kept.