1. Blanks, missing values, & errors
In this video, things will go wrong. Data will be missing, calculations will give the wrong answer, and errors will be thrown. Don't worry though, you'll also learn how to cope with these problems.
2. Blanks
As you saw in chapter one, if a cell has nothing in it, then it is considered to be blank. In the example in column A, you can see the first few triangular numbers, but some of the cells are blank.
You can test for blank cells using the ISBLANK() function, and count the number of blank cells in a range using COUNTBLANK(). In this case, 2 numbers are blank so COUNTBLANK() returns 2.
3. Calculating with blanks: DANGER!
One big problem with blanks is that if you try to do any calculations with them, you'll get the wrong answer.
In the example, you can see that if you try to add one to a blank cell, it pretends that the blank value is zero.
This is highly dangerous, since a blank value usually means that you don't know what value should go in that cell.
4. Calculating with blanks: make them missing
The solution to getting correct calculations is to convert the blank values to missing values.
A missing value is a special value created by calling the NA() function. The conversion process involves calling IF(). The condition calls ISBLANK() with the cell address. If that returns TRUE, then you should return a missing value; otherwise you should just return the cell value.
Notice that the N-dot-A-dot values print as hash-N-slash-A.
5. Errors
While we're on the theme of things going wrong, let's talk about errors. You can test for errors using the ISERROR() function. In column C of the example, you can see that the blank value isn't considered an error, but the missing value is.
Since many data analyses naturally involve working with missing values, there is another function called ISERR() that also tests for errors in cells, but doesn't consider missing values to be errors.
6. Types of error
As well as missing values, there are several other types of error.
Some of them are related to calculations, like divide by zero errors, values errors caused by nonsense calculations, and range errors.
There is also a reference error type caused by deleting cells that are being referred to elsewhere.
Other error types like NAME and the last one, which is just called ERROR, refer to syntax errors when typing formula.
7. Summary
You just learned that empty, or blank, cells. This is different from missing values which are created using NA().
You should always convert blank values to missing values before performing any calculations, or you'll get the wrong answer.
Finally, missing values are one of several types of error.
8. Let's practice!
Let's go break things!