Page 91 - Excel Workbook for Dummies

10_798452 ch05.qxp 3/13/06 7:44 PM Page 74
74 Part II: Using Formulas and Functions
#NAME? appears when your formula contains a text reference that Excel doesn’t
recognize (such as a reference to a range name that no longer exists in the
workbook)
#NUM! appears when your formula contains invalid numeric values (such as a
text entry where a number is required)
#N/A appears when your formula refers to a value that is not available to it

Building formulas by hand

To build a formula by hand, all you have to do is type an = (equal to) sign and then
designate the string of operands and operators that the formula should use in making
its calculation(s). Operands can be constants that you type into the formula (such as
5.5 or 100), or they can be cell references (such as B5 or A10:J17) that you point
directly to in the worksheet or type.

Table 5-1 shows you a list of all the operators, including their type, character, and
operation.

Table 5-1 The Different Types of Operators in Excel Formulas
Type Character Operation Example
Arithmetic

+ (plus sign) Addition =A2+B3
- (minus sign) Subtraction or negation =A3-A2 or -C4
* (asterisk) Multiplication =A2*B3
/ Division =B3/A2
% Percent (dividing by 100) =B3%

^ Exponentiation =A2^3
Comparison
= Equal to =A2=B3

> Greater than =B3>A2
< Less than =A2<B3
>= Greater than or equal to =B3>=A2
<= Less than or equal to =A2<=B3
<> Not equal to =A2<>B3

Text
& Concatenates (connects) entries =A2&” “&B3
to produce one continuous entry

Reference
: (colon) Range operator that includes all =SUM(C4:D17)
cells between the colon