Flag over- and under-budget departments in a quarterly variance report
SIGN reduces a variance down to which direction it went, ABS reduces it down to only how big it was — nesting SIGN's result inside IF turns that direction into the label a report actually wants.
Task:
You're pulling together the quarterly budget variance report for the exec team. Four departments' budgeted and actual spend for the quarter are listed below. In column D, work out the size of each department's variance in dollars, regardless of whether they came in over or under. In column E, label each department "Over budget", "Under budget" or "On budget". In column F, work out the variance as a percentage of budget, rounded to one decimal place.
Learning Objectives:
- Use ABS to report the size of a difference without its sign getting in the way
- Nest SIGN inside IF to turn a number's direction into a readable label
- Choose the right denominator when turning a variance into a percentage
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Budget | Actual | Abs Variance | Status | % Variance |
| 2 | Marketing | 42000 | 45800 | |||
| 3 | Operations | 68000 | 61200 | |||
| 4 | IT | 25000 | 25000 | |||
| 5 | Sales | 51000 | 53400 |
What this exercise teaches (contains the answer)
ABS(C2-B2) strips the sign off Operations' variance, so its $6,800 underspend and Marketing's $3,800 overspend both read as a plain size in column D, rather than one of them printing as a negative number in a column meant only to show how big the gap is. SIGN(C2-B2) throws away that size entirely and keeps only the direction: 1 when actual spend beat budget, -1 when it fell short, and exactly 0 for IT, which spent its $25,000 budget to the dollar — nesting that inside IF turns those three numbers into the words "Over budget", "Under budget" and "On budget" instead of leaving a reader to interpret a -1 themselves. Column F divides the raw variance by the budget rather than by actual spend, because the budget is the fixed figure every department is being measured against — a $3,800 overspend means something different against a $42,000 budget than the same $3,800 would against a $10,000 one. ROUND(...,1) then trims Sales' 4.70588...% down to the reportable 4.7 without pretending the extra decimals carried any meaning.