Page 284 - Excel Data Analysis

16 537547 AppD.qxd 3/4/03 11:55 AM Page 270

APPENDIX

FORMULA BASICS IN EXCEL

ormulas are the building blocks of data analysis in Excel. combination of functions, cell references, named ranges,
They enable you to automatically calculate and compare constants, and operators to create the formula statement.
Fvalues within different cells so that you can interpret The only real limitation is that you cannot make your formula
them. This section serves as a reference for users not familiar longer than 1,024 characters in length. If Excel cannot
with Excel. properly evaluate your formula, an error message displays.
Formulas contain two basic elements: an equal sign, and an
expression, which tells Excel what to do. You can use any

EXCEL OPERATORS
Operators combine or compare the values that you want to analyze. Excel
provides a large variety of different operators for creating formulas, most
of which are the arithmetic operators. Excel also provides comparison
operators, a text operator, and reference operators.

Arithmetic Operators Comparison Operators
You use arithmetic operators to perform numeric You use comparison operators between two
calculations, such as addition or subtraction. Obviously, expressions to determine if they are equal (=), greater
using them on text results in an error message. Excel than (>), greater than or equal to (>=), less than (<), less
provides addition (+), subtraction (-), multiplication (*), than or equal to (<=), or not equal to (<>) each other.
division (/), percent (%), and exponential (^) operators. When you use comparison operators in a formula, Excel
Placing the subtraction operator in front of a number returns a logical value of either True or False.
makes it a negative number.
Text Operator When you use the Text operator to combine values in
cells, the end result becomes a text value. This is true
You can use the text operator to join or concatenate
two or more strings of text together to form one string. even if the combined values are numeric. For example,
You concatenate strings together using the Ampersand if you combine the values 14 and 92 with the Text
(&). Excel merges the two strings together without operator, Excel creates the value 1492. Although it is a
adding any extra spacing. If you want spaces between number, Excel treats 1492 as a text string because you
the strings, you must insert them yourself. For example, created it using the Text operator. If you attempt to use
if you want to leave a space between the two strings, the value in a mathematical calculation, Excel ignores
where APPLE is in cell A1, and SAUCE is in cell A2, you the value and treats it as zero, because it is text. In
type: some instances, Excel may return an error message.
For example, if you use the value of a concatenated
Example: string as the denominator of a division formula, Excel
=A1&" "&A2 returns a !DIV/0 error. This indicates that you divided by
zero because Excel interprets a text string as zero.

270