#DIV/0! is not a comic book version of someone cursing. It's the error message that you receive in an Excel worksheet whenever you attempt to divide by zero. If you remember grade school math class, zero divided by any number is zero. However, unless you were born on Gallifrey, dividing any number by zero is a mathematical impossibility. Since Excel cannot solve the formula, it returns #DIV/0! as shown in the following screen shot.

As you can see from this image, the #DIV/0! error cascades throughout the spreadsheet. Cells D8 and D10 return the same error message because one of the cells that is being summed and averaged contains the error condition. The error in cell D10 causes cell D12 to return an error. Although you cannot prevent a zero from appearing as a divisor in a formula, you can prevent the error condition from cascading by wrapping all of your division formulas inside of the IF function.

The basic structure of the IF function is: =IF(condition to test,return if true,return if false). In this example we want to test whether the numbers in column C equal zero. If they do, return the number zero. If they do not, perform the original division formula. As shown in the following screen shot, the division error messages have disappeared and the formulas in D8,D10, and D12 return an answer.

Notice that cells D5 and D6 both return zero, but for different reasons. One way to stop the error messages but yet not return the number zero is to enter some text as the true result from IF function. In the following example the IF function has been changed to display a dash (-) whenever the divisor is zero.

WARNING - although the SUM function in cell D8 returned the same answer in both of the two previous screen shots, the AVERAGE funtion in cell D10, and the formula in cell D12 have different answers, as highlighted in the red box. This is caused by the way Excel functions work. The SUM function treats text as a zero. Thus summing a cell containing a zero or a cell containing text results in the same answer. However, the AVERAGE function ignores any cells containing non-numerical data. Therefore the first IF example is averaging five cells, two of which contain zero, while the second IF example is averaging four cells, one of which contains zero. Depending on your application this could have a significant impact. [In these instances you may want to use the AVERAGEA function. This function treats text as a zero, similar to the SUM function.]

Copyright 2010 - VBA Consultants Ltd

Corporate users of Microsoft Excel utilize large spreadsheets encompassing many rows and columns. Regardless of whether the spreadsheet is being used for budgeting, cash projections, depreciation, or acquisition analysis, there is usually a few cells containing some critical piece of information. Most users constantly scroll left and right, or up and down, to see the effect of changes on these critical cells.

Excel contains a feature known as the Watch Window. The Watch Window is a floating dialog box that displays the value of your ‘critical cells’ even when those cells are not currently displayed. Using this feature will not only save time, but also carpal tunnel syndrome.

To display the Watch Window in Excel 2007 or 2010, click on the Watch Window icon contained on the Formulas ribbon. [For Excel 2003 select Tools → Formula Auditing → Show Watch Window on the menu bar.]

This will display the Watch Window.

Click Add Watch on the menu bar. This displays a pop-up allowing you to either type a cell (or range) address or by clicking the icon, selecet the cell (or range) with your mouse.

When the correct cell (or range) is shown in the pop-up, click the Add button.

The Watch Window will then display the book name, sheet name, optional range name, cell (or range) address, current value of the cell, and the formula contained in the cell.

You can continue to add more additional items to the Watch Window by clicking on Add Watch on the menu bar.

The Watch Window will remain ‘in your face’ as you move around the spreadsheet. However, you also have the option of docking the Watch Window below the ribbon by dragging the window up towards the ribbon until it snaps into place.

You can turn off the display of the Watch Window by either clicking the Watch Window icon on the Formula ribbon or by clicking the ‘x’ in the upper right corner of the Watch Window.

Copyright 2010 - VBA Consultants Ltd