Understanding Formula Errors
It’s not always smooth sailing when you’re working with formulas. Sometimes a formula returns an error value instead of the value you were expecting. Excel helps you identify what the problem may be by returning one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, explained in the following list:
#DIV/0!: This error value means that the formula is attempting to divide a value by zero. There is mathematically no way to divide a number by zero. You will also see this error if the formula is trying to divide a value by an empty cell.
#N/A: This error value means that the formula cannot return a legitimate result. You would typically see this error when you use an inappropriate argument in a function. You will also see this error when a lookup function does not return a match.
#NAME?: This error value means Excel doesn't recognize a name you used in a formula as a valid object. This error could be a result of a misspelled function, a misspelled sheet name, a mistyped cell reference, or some other syntax error.
#NULL!: This error value means the formula uses an intersection of two ranges that don’t intersect.
#NUM!: This error value means there is a problem with a number in your formula; typically an invalid argument in a math or trig function. For example, you entered a negative number where a positive number was expected.
#REF!: This error value means that your formula contains an invalid cell reference. This is typically caused by deleting a row or column to which the formula refers. This could also mean that the formula uses a cell reference that doesn’t exist (A2000000, for instance).
#VALUE: This error value means that your formula uses the wrong data type for the operation it’s trying to do. For example, this formula will return a #VALUE error (=100+”dog”).
Using Named Ranges in Formulas
A named range is nothing more than a cell or range of cells that has been given a friendly, descriptive name. Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. For instance, say that you have line- item sales in cells A1:A25 and you have a percent tax in cell B1. You could calculate a total sale amount with tax using this formula:
=SUM(A1:A25)*(1+B1)
Now imagine that you gave your ranges descriptive names, calling cells A1:25 Sale_Items, and calling cell B1 Tax_Percent. You could then calculate the total sale amount with tax by using this formula:
=SUM(Sales_Items)*(1+Tax_Percent)
Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren’t familiar with the workbook.
Another benefit to naming these ranges is that creating new formulas with these named ranges becomes easier because you can simply use the easily remembered descriptive name instead of trying to remember that line-item sales live in cells A1:A25.
Creating a named range
Follow these steps to create a named range:
Select the cell or range of cells you want to name.
Choose Define Name from the Formulas tab. This activates the New Name dialog box, shown in Figure 1-7.
In the Name input box, enter a friendly, descriptive name for your range.
In the Scope drop-down box, select whether you want your named range to be available for use throughout the workbook or just on a specific sheet.
Press the OK button to create your named range.
It’s not always smooth sailing when you’re working with formulas. Sometimes a formula returns an error value instead of the value you were expecting. Excel helps you identify what the problem may be by returning one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, explained in the following list:
#DIV/0!: This error value means that the formula is attempting to divide a value by zero. There is mathematically no way to divide a number by zero. You will also see this error if the formula is trying to divide a value by an empty cell.
#N/A: This error value means that the formula cannot return a legitimate result. You would typically see this error when you use an inappropriate argument in a function. You will also see this error when a lookup function does not return a match.
#NAME?: This error value means Excel doesn't recognize a name you used in a formula as a valid object. This error could be a result of a misspelled function, a misspelled sheet name, a mistyped cell reference, or some other syntax error.
#NULL!: This error value means the formula uses an intersection of two ranges that don’t intersect.
#NUM!: This error value means there is a problem with a number in your formula; typically an invalid argument in a math or trig function. For example, you entered a negative number where a positive number was expected.
#REF!: This error value means that your formula contains an invalid cell reference. This is typically caused by deleting a row or column to which the formula refers. This could also mean that the formula uses a cell reference that doesn’t exist (A2000000, for instance).
#VALUE: This error value means that your formula uses the wrong data type for the operation it’s trying to do. For example, this formula will return a #VALUE error (=100+”dog”).
Using Named Ranges in Formulas
A named range is nothing more than a cell or range of cells that has been given a friendly, descriptive name. Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. For instance, say that you have line- item sales in cells A1:A25 and you have a percent tax in cell B1. You could calculate a total sale amount with tax using this formula:
=SUM(A1:A25)*(1+B1)
Now imagine that you gave your ranges descriptive names, calling cells A1:25 Sale_Items, and calling cell B1 Tax_Percent. You could then calculate the total sale amount with tax by using this formula:
=SUM(Sales_Items)*(1+Tax_Percent)
Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren’t familiar with the workbook.
Another benefit to naming these ranges is that creating new formulas with these named ranges becomes easier because you can simply use the easily remembered descriptive name instead of trying to remember that line-item sales live in cells A1:A25.
Creating a named range
Follow these steps to create a named range:
Select the cell or range of cells you want to name.
Choose Define Name from the Formulas tab. This activates the New Name dialog box, shown in Figure 1-7.
In the Name input box, enter a friendly, descriptive name for your range.
In the Scope drop-down box, select whether you want your named range to be available for use throughout the workbook or just on a specific sheet.
Press the OK button to create your named range.