Datasheet

Part I: Putting the Fun in Functions

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 cal-

culate 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 dif-

ference in the outcome. You must take into account the order of mathemati-

cal operators when writing formulas. The order is:

1. Parentheses

2. Exponents

3. Multiplication and division

4. Addition and subtraction

This is a key point of formulas. It is easy to just accept a returned answer.

After all, Excel is so smart. Right? Wrong! Like all computer programs, Excel

can only do what it is told. If you tell it to calculate an incorrect but structur-

ally valid formula, it will do so. So watch your p’s and q’s! Er, rather your

parentheses and mathematical operators when building formulas.

The second type of error is when there is a mistake in the formula or in the

data the formula uses that prevents Excel from calculating the result. Excel

makes your life easier by telling you when such an error occurs. To be pre-

cise, it does one of the following:

✓ Excel displays a message when you attempt to enter a formula that is

not constructed correctly.

✓ Excel returns an error message in the cell when there is something

wrong with the result of the calculation.

First, let’s see what happened when we tried to finish entering a formula that

had the wrong number of parentheses. Figure 1-19 shows this.

Excel finds an uneven number of open and closed parentheses. Therefore the

formula cannot work (it does not make sense mathematically), and Excel tells

you so. Watch for these messages; they often offer a solution.

05_568163-ch01.indd 2605_568163-ch01.indd 26 4/2/10 1:37 PM4/2/10 1:37 PM