Microsoft Office Tutorials and References
In Depth Information
Correcting Common Formula Errors
Refer to the table shown in Figure 15.11. This table is named Table1 . To calculate the sum
of all the data in the table, enter this formula into a cell outside the table:
=SUM(Table1)
This formula will always return the sum of all the data (excluding calculated Total Row
values, if any), even if rows or columns are added or deleted. And if you change the name
of Table1 , Excel will adjust formulas that refer to that table automatically. For example,
if you renamed Table1 to AnnualData (by using the Name Manager, or by choosing Table
Tools Design Properties Table Name), the preceding formula would change to:
=SUM(AnnualData)
Most of the time, a formula will refer to a specifi c column in the table. The following
formula returns the sum of the data in the Actual column:
=SUM(Table1[Actual])
Notice that the column name is enclosed in square brackets. Again, the formula adjusts
automatically if you change the text in the column heading.
Even better, Excel provides some helpful assistance when you create a formula that refers to
data within a table. Figure 15.13 shows the formula AutoComplete helping to create a
formula by showing a list of the elements in the table. Notice that, in addition to the column
headers in the table, Excel lists other table elements that you can reference: #All , #Data ,
#Headers , #Totals , and @ - This Row .
FIGURE 15.13
The formula AutoComplete feature is useful when creating a formula that refers
to data in a table.
Correcting Common Formula Errors
Sometimes, when you enter a formula, Excel displays a value that begins with a hash mark or
pound sign ( # ). This is a signal that the formula is returning an error value. You have to
correct the formula (or correct a cell that the formula references) to get rid of the error display.
 
Search JabSto ::




Custom Search