Budget variance, with the divide-by-zero guarded (IFERROR)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
Budget and actual spend for row 2 are in B2:C2. Put the variance — actual minus budget, over budget, as a decimal — in the Variance cell D2.
A department with a zero budget must return 0, not #DIV/0!, because reports are printed as they are.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Budget | Actual | Variance |
| 2 | Marketing | 5000 | 5500 | |
| 3 | Ops | 8000 | 7600 |
Notes
Sign convention matters more than the arithmetic: for costs, a positive variance is overspend. A zero budget breaks the division, so IFERROR supplies 0 and the report stays printable.
How to approach
Your answer goes in D2.
- Target cell is D2
- (Actual − Budget) / Budget
- Guard the zero budget
- Shape: =IFERROR(ROUND(?, 4), ?)
Function guide
FAQ
How do I calculate budget variance?
(actual − budget) / budget. Decide the sign convention first: for costs, positive usually means overspend.
Why does my variance show #DIV/0!?
The budget is zero. Wrap the division in IFERROR, or test the denominator before dividing.