Error Trapping in Excel

I had a query about how to set up a spreadsheet for future rows to be added but without showing all the error messages you get when a formula expects there to be something in a cell which hasn't yet had data entered.

Here's an example spreadsheet that works out how many miles per gallon of fuel you are using. I'm old enough to not want to know how many miles per litre I am getting so the spreadsheet converts the litres I buy into gallons.

I have entered data up to row 8 but from row 9 downwards there are either errors or data showing where I don't want to see anything until data is entered into those rows.

The only data I need to enter will go in column A (the date I buy fuel), column B (the mileage shown on the odometer of my car when I refuel) and column D (the number of litres I put in the tank).

A formula in cell C9 =B9-B8 calculates how many miles since the last refuelling. Because there is no data in cell B9 it has calculated a value of -4328 ie zero minus the value in cell B8.

The display in cell E9 is even worse because there is an error message displayed due to the lack of data in cell D9 which is causing a Divide by Zero error.

The formula in cell D9 is =C9/(D9/4.546). The bit inside the brackets (D9/4.546) converts the number of litres into gallons. Then the number of miles travelled, held in cell C9, is divided by the number of gallons to give the miles per gallon figure.

There really is no error here in human terms because the data simply hasn't been entered yet. But unless we want to copy the formulae down every time we add a row of data, the spreadsheet will see a logical error and report it.

So we can use a simple =IF formula to test for the existence of a value in the critical cells before displaying anything.

In cell C9 we have the formula =IF(B9="","",B9-B8).

Let's break down the =IF syntax first then look at what it is doing.

It uses the commas to separate the different segments and takes the form =IF(you find this, then do this, otherwise do this)

If a cell contains nothing, that can be represented by placing the contents (nothing) between a set of double quote marks - "". Note that there is a big difference between nothing and a zero! So we can't use =IF(B9=0...

So the formula is saying; =IF(B9 holds nothing, do nothing, otherwise display B9-B8)

Likewise we can add a similar =IF to the formula in cell E9, in which case the existence of a value in cell D9 are the key to whether we want a value displayed or not.

=IF(D9="","",C9/(D9/4.546))

The formulae can be copied down both columns B and E and each time you refuel you just need to add data to columns A, B and D to complete the row.

Tweet