Sometimes, even when a formula is working correctly, it will return an error, like #N/A or #VALUE!.  If you’re sharing your worksheet with someone else, or if you just like a tidy worksheet, you may not like seeing these errors. 

Fortunately, nesting ISNUMBER into an IF function can eliminate them.

• The ISNUMBER function checks to see if a value is a number.  If it is, it returns TRUE, if not, it returns FALSE.  The syntax is:
  =ISNUMBER(value)
• The IF function tests to see if something is true or false, then returns one value if the test was true and another value if it was false.  The syntax is:
  =IF(logical test, value if true, value if false)

 To nest these functions together to eliminate things like #N/A or #VALUE!, the syntax is:
=IF(ISNUMBER(value), value if true, value if false)

Lets carry over the example from my last two posts about taking sales data and finding the date of a customer’s 1st, 2nd, 3rd, etc purchase. At the end of Part 2, we had a table that looks like this.  I’d like to replace the #N/A with “No Purchase”.

I’ll start another table with the same column and row headers.

Then, in cell B9, I’ll enter the formula:
=IF(ISNUMBER(B3),B3,”No Purchase”)

This formula will check to see if B3 is a number. If it is, it will return the contents of B3.  If not, it will return “No Purchase”, getting rid of the #N/A.  Then I’ll copy the formula across and down the table.  This is what I get.

Truthfully, I could have avoided using two tables by nesting the VLOOKUP function I used to make the first table.  In the example above, instead of using B3 as the value in ISNUMBER and as the value if true, could enter the VLOOKUP.  I decided not to use this method because the formula was very long, had too many places I could make an error, and frankly, made my head hurt. 

Now I want to find the number of days between someone’s first and second purchase, and between their second and third purchase. To find out the number of days between two dates, I would subtract the first date from the second from the other.  The picture below shows what it would look like.  I’ve displayed the formula in B15.

But because customer 2222 didn’t make a third purchase, the formula returns #VALUE!.  To avoid this, I can use the formula:

=IF(ISNUMBER(C9-B9),C9-B9,”No purchase”)

This formula will check to see if C9-B9 would return a number.  If so, it returns the results of C9-B9.  If not, it returns “No purchase”.  Look below to see what it will look like.  The formula in B15 is displayed.