Software

Use error handling to enhance your Excel spreadsheets

If you're tired of seeing #N/A errors in your spreadsheets, we've got the solution for you. Find out how built-in Excel error-handling functions can hide these errors.

In my two previous articles "Use this sample spreadsheet to make the most of Data Validation drop-down lists" and "See how our sample spreadsheet uses absolute cell references and drop-down lists," I demonstrated how to build a simple invoice using Data Validation drop-down lists and absolute cell references. This article will discuss how error handling can make our invoice more user-friendly. I'll also complete the invoice by adding both sales tax and total formulas. Before you proceed, I suggest that you read my previous articles and download our sample invoice spreadsheet to see these examples in action.

Before we begin
To use readily available data, I imported the Customers And Products list from the Northwind sample database that Microsoft ships with MS Access. If you do not have MS Access, you can obtain this file from Microsoft's Download Center.

Error handling
At the end of my last article, I had just added the formulas for the Product ID, Description, Price, and Subtotal columns. These columns are automatically filled when a product is chosen from the drop-down lists in the Product Name column. Unfortunately, if the Product Name column is blank, an error message is displayed in the Product ID, Description, Price, and Subtotal cells (see Figure A).

Figure A


The "#N/A" occurs when a value is not available to a function or formula. Formulas that refer to those cells without data will return #N/A instead of attempting to calculate a value. To combat this, we use the ISNA function in conjunction with the IF function to display clear cells on our spreadsheet.

Replace the #N/A error with clear cells
Select cell C14, the first cell in the Description column, and enter the formula:
=IF(ISNA(VLOOKUP(B14,$CA$2:$CJ$68,5)),"",VLOOKUP(B14,$CA$2:$CJ$68,5))

The ISNA() function tests for the "#N/A" result. If the “#N/A” error exists in that cell, this function will insert a blank space; otherwise, it will insert the desired function: VLOOKUP(B14,$CA$2:$CJ$68,5).

We will use these same error-handling functions, with a few minor adjustments, to return the Product ID and Price for the selected product. Select cell B14 and enter the formula:
=IF(ISNA(VLOOKUP(B14,$CA$2:$CJ$68,2)),"",VLOOKUP(B14,$CA$2:$CJ$68,2))

For the Price column, select cell F14 and enter the formula:
=IF(ISNA(VLOOKUP(B14,$CA$2:$CJ$68,6)),"",VLOOKUP(B14,$CA$2:$CJ$68,6))

Once these formulas have been added to cells B14, C14, and F14, you will need to copy them into the Product ID, Description, and Price columns’ remaining cells.

Solving the Subtotal error
You will note that there are still errors in the Subtotal column. To correct this problem, we must use the ISERROR function. Select cell G14 and enter the formula:
=IF(ISERROR(F14*E14),"",F14*E14)

Again, this is testing for the presence of an error and utilizing the IF function to enter a blank space.

Taxes and totals
To complete our invoice, we need to sum the cost of our selected products and add taxes. To complete the Subtotal cell, select cell G23 and enter the formula =SUM(G14:G22). To add the Sales Tax cell, select cell G24 and enter the formula =G23*AA1. I placed the current tax rate, 6 percent, in cell AA1. Finally, select cell G25 and enter the formula =SUM(G23:G24). Our sample invoice is now complete.

Using our sample invoice
To use our sample invoice, select cell B6 and click on the down arrow to see the list of customers (see Figure B).

Figure B


Select a customer, and the information for that customer is automatically populated. Next, select cell B14 and click on the down arrow to choose the desired product. Again, the Product ID, Description, and Price are automatically populated. Enter the quantity in cell E14, and the total for that product is calculated and displayed in cell G14. The Subtotal, Sales Tax, and Total are also calculated as you go.
0 comments