Logical FunctionsBeginner

Report Year-on-Year Growth When a Line Is New with IFERROR

Growth is this year divided by last year, minus one, and that division has nothing to divide by for a product line that did not exist last year. IFERROR catches the #DIV/0! for that one row and puts a label a reader understands in its place, while every real answer, including a fall of 100%, passes through untouched.

Task:

You prepare the autumn trading review for a garden centre. Sales for each product line, January to October, are in A2:C7: last year in column B and this year in column C. Grow-your-own kits were launched this spring, so they sold nothing last year, and pond supplies were dropped at the end of last season. In D2, work out each line's growth as a decimal, so a rise of 9% reads 0.09, and show the text "New" instead of an error for a line that had no sales last year. Copy it down to D7.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

7 rows × 4 columns6 cells you fill in
ABCD
1Product lineJan–Oct 2025 ($)Jan–Oct 2026 ($)Growth
2Bedding plants4820052538
3Compost & mulch3150029610
4Garden furniture6400070400
5Grow-your-own kits08650
6Pond supplies124000
7Barbecues2250025875
What this exercise teachesMay contain the answer

Growth compares this year with last year, so last year is the divisor, and a line that sold nothing last year leaves nothing to divide by. Excel answers C5/B5 with #DIV/0!, which is arithmetically honest and useless in a trading review: the kits have not shrunk or grown by any percentage, they are simply new. IFERROR(value, value_if_error) works the calculation out first and hands back its result untouched unless it is an error, in which case it returns the second argument instead, so =IFERROR(C2/B2-1,"New") gives 0.09 for bedding plants and "New" for the kits from one formula filled down. Pond supplies is the row worth looking at twice. This year's 0 is on top of the fraction, not underneath it, so 0/12400-1 is a perfectly good -1, a fall of 100%, and IFERROR lets it straight through. That is the answer the review needs: the line is gone, and a reader should see it gone, not see it blanked or labelled as something else. Dividing and then subtracting 1, rather than writing (C2-B2)/B2, is the same sum in fewer steps. The thing to keep in mind is that IFERROR hides every error, not only this one, so a typing mistake in the formula would also turn into "New"; it is safe here because the only error the data can cause is the empty year it is meant to catch.