Level 1Core FunctionsProject, 6 min
A budget that flags overspending
The task
Here's a month of spending against a budget. Build the columns that tell you, at a glance, where the money went over.
- What's left in each categoryIn D2, subtract the whole Spent column from the whole Budget column in one go. Math on whole columns goes inside
ARRAYFORMULA:=ARRAYFORMULA(B2:B7-C2:C7). - How much of each budget you usedIn E2, divide Spent by Budget for every row, inside ARRAYFORMULA again. The column is formatted as a percent.
- Flag the overspendsIn F2, label each row:
=ARRAYFORMULA(IF(C2:C7>B2:B7, "Over", "OK")). IF checks a condition and returns one of two answers. - Find the worst offenderIn B9, return the category with the biggest overspend: the most negative number in Left.
=XLOOKUP(MIN(D2:D7), D2:D7, A2:A7)
The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Budget | Spent | Left | Used | Status |
| 2 | Rent | $1,600 | $1,600 | |||
| 3 | Groceries | $600 | $684 | |||
| 4 | Dining out | $250 | $312 | |||
| 5 | Transport | $220 | $175 | |||
| 6 | Utilities | $180 | $166 | |||
| 7 | Fun | $200 | $240 | |||
| 8 | ||||||
| 9 | Biggest overspend |