Microsoft Office Tutorials and References

In Depth Information

**Gaining the Upper Hand on Formulas**

Figure 1-18:

Populating

cells with a

formula by

using the fill

handle.

Assembling formulas the right way

There’s a saying in the computer business: garbage in, garbage out. And that

applies to how formulas are put together. If a formula is constructed the

wrong way, it either returns an incorrect result or an error.

Two types of errors can occur in formulas. In one type, Excel can calculate

the formula, but the result is wrong. In the other type, Excel is not able to

calculate the formula. Check out both of these.

A formula can work and still produce an incorrect result. Excel does not report

an error because there is no error for it to find. Often this is the result of not

using parentheses properly in the formula. Take a look at some examples:

Formula

Result

=7 + 5 * 20 + 25 / 5

112

=(7 + 5) * 20 + 25 / 5

245

=7 + 5 *( 20 + 25) / 5

52

=(7 + 5 * 20 + 25) / 5

26.4

All of these are valid formulas, but the placement of parentheses makes a

difference in the outcome. You must take into account the order of

mathematical operators when writing formulas. The order is:

1. Parentheses

2. Exponents