Microsoft Office Tutorials and References

In Depth Information

**Understanding Excel Formulas**

6

chapter

Building Formulas

Arithmetic Formulas

An arithmetic formula combines numeric operands —

numeric constants, functions that return numeric

results, and fields or items that contain numeric

values — with mathematical operators to perform a

calculation. Because Excel worksheets primarily deal

with numeric data, arithmetic formulas are by far the

most common formulas used in worksheet calculations.

The following table lists the seven arithmetic

operators that you can use to construct arithmetic

formulas:

Operator

Name

Example

Result

+

Addition

=10 + 5

15

–

Subtraction

=10 – 5

5

–

Negation

=–10

–10

*

Multiplication

=10 * 5

50

/

Division

=10 / 5

2

%

Percentage

=10%

0.1

^

Exponentiation

=10 ^ 5

100000

Comparison Formulas

A comparison formula combines numeric operands —

numeric constants, functions that return numeric

results, and fields or items that contain numeric

values — with special operators to compare one

operand with another. A comparison formula always

returns a logical result. This means that if the

comparison is true, then the formula returns the value

1, which is equivalent to the logical value TRUE; if the

comparison is false, then the formula returns the

value 0, which is equivalent to the logical value

FALSE.

The following table lists the six operators that you can

use to construct comparison formulas:

Operator

Name

Example

Result

=

Equal to

=10 = 5

0

<

Less than

=10 < 5

0

< =

Less than or equal to

=10 < = 5

0

>

Greater than

=10 > 5

1

> =

Greater than or equal to

=10 > = 5

1

< >

Not equal to

=10 < > 5

1

Operator Precedence

Most of your formulas include multiple operands and

operators. In many cases, the order in which Excel

performs the calculations is crucial. For example,

consider the formula =3 + 5 ^ 2. If you calculate from

left to right, the answer you get is 64 (3 + 5 equals 8,

and 8 ^ 2 equals 64). However, if you perform the

exponentiation first and then the addition, the result

is 28 (5 ^ 2 equals 25, and 3 + 25 equals 28).

Therefore, a single formula can produce multiple

answers, depending on the order in which you perform

the calculations.

To solve this problem, Excel evaluates a formula

according to a predefined order of precedence, which

is determined by the formula operators, as shown in

the following table:

Operator

Operation

Precedence

( )

Parentheses

1st

–

Negation

2nd

%

Percentage

3rd

^

Exponentiation

4th

* and /

Multiplication and division

5th

+ and –

Addition and subtraction

6th

= < < = > > = < >

Comparison

7th