Microsoft Office Tutorials and References

In Depth Information

**Calculating Formulas**

Don’t hard-code values

When you create a formula, think twice before using a literal value in the formula. For example,

if your formula calculates a 7.5 percent sales tax, you may be tempted to enter a formula such as

=A1*.075

A better approach is to insert the sales tax rate into a cell and use the cell reference in place of

the literal value. This makes it easier to modify and maintain your worksheet. For example, if the

sales tax range changes to 7.75 percent, you need to modify every formula that uses the old

value. If the tax rate is stored in a cell, you simply change one cell and all the formulas

recalculate using the new value.

Simply accepting the correction proposed in the dialog box is tempting, but be careful.

In many cases, the proposed formula, although syntactically correct, isn’t the formula

that you want. In the following example, I omitted the closing parenthesis after

January. In Figure 2-2, Excel proposed this correction:

=SUM(January/SUM(Total))

In fact, the correct formula is

=SUM(January)/SUM(Total)

Figure 2-2:
Excel’s Formula AutoCorrect feature often suggests a correction to an erroneous formula.

Calculating Formulas

You’ve probably noticed that the formulas in your worksheet get calculated immediately. If you

change any cells that the formula uses, the formula displays a new result with no effort on your

part. This occurs when Excel’s Calculation mode is set to Automatic. In this mode (the default

mode), Excel follows certain rules when calculating your worksheet:

h
When you make a change (enter or edit data or formulas, for example), Excel calculates

immediately those formulas that depend on new or edited data.