
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 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.

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)

=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.
0 Komentar untuk "Error Trapping in Excel"