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

Search JabSto ::

Custom Search