5. Mistakes in formulae

Formula with function

The picture above shows a formula that multiplies D1 by D2 and then adds the result to E1 + E2.

What happens to the formula if you then delete Row 1? You get this...

Reference error

A #REF error appears as the formula cannot work. The spreadsheet will no longer calculate the formula and it is up to you to sort out the problem.

With large spreadsheets containing tens of thousands of formulas, deleting a single cell location can result in thousands of #REF errors appearing

A sea of REF errors

Another way of making a mistake is to include a named variable that does not / no longer exists. Like this

Missing Variable

There is no 'ALL' named variable in this spreadsheet so the #NAME message appears in the cell containing the formula.

So formulas are very powerful, but you do need to make sure that it can actually carry out the calculation sensibly.

 

Challenge see if you can find out one extra fact on this topic that we haven't already told you

Click on this link: Formulae