|
All the formulas you build in an Excel spreadsheet regardless of their function and degree of complexity have one thing in common: They all begin with one simple character, = (the equal to sign). Typing an equal to sign activates the Insert Function, Enter, and Cancel buttons on the Formula bar. It also changes the nature of the Name Box drop-down box so that its list displays commonly used functions rather than the range names assigned to the workbook.
 | If you forget to type this as your initial character when creating formulas by hand (Excel is always sure to put one in for you when you build formulas with the Insert Function button), the program inserts the string of operands and operators you enter as a text reference. |
The value of an error
If you build a legitimate formula, Excel either computes the answer and displays it in the current cell in the worksheet or, if unable to successfully calculate the answer, the program displays one of the following error values in the cell:
- #NULL! appears when your formula specifies an intersection of two ranges that do not, in fact, intersect.
- #DIV/0! appears when your formula attempts to divide by zero.
- #VALUE! appears when your formula contains some sort of improper argument type or operand (such as a text entry when the operator requires a value).
- #REF! appears when your formula contains an improper cell reference.
- #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
Hello, operator?
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 1 shows you a list of all the operators, including their type, character, and operation.
Table 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 to produce one continuous entry
| =A2&" "&B3
|
Reference
| | | |
| : (colon)
| Range operator that includes all cells between the colon
| =SUM(C4:D17)
|
| , (comma)
| Union operator that combines multiple references into one reference
| =SUM(A2,C4:D17,B3)
|
| (space)
| Intersection operator that produces one reference to cells in common with two references
| =SUM(C3:C6C3:E6)
|
|
