Microsoft Office Tutorials and References
In Depth Information
Working with Formulas
Working with Formulas
So far you have entered only text, numbers, and dates in the worksheet. However, the
main reason for using Excel is to display values calculated from data. For example,
Amanda wants the workbook to determine the number of DVDs she has to create for
her customers and how much revenue these orders will generate. Such calculations are
added to a worksheet using formulas and functions.
Entering a Formula
A formula is an expression that returns a value. In most cases, this is a numeric value
though it could also be a text string, a yes/no value, or a date. Every Excel formula begins
with an equal sign (=) followed by an expression describing the operation that returns the
value. Note that if you don’t begin the formula with the equal sign, Excel assumes that
you are entering text and will not treat the cell contents as a formula.
A formula is written using operators that combine different values, resulting in a
single value that is then displayed within the cell. The most commonly used operators
are arithmetic operators that perform addition, subtraction, multiplication, division, and
exponentiation. For example, the following formula adds 5 and 7, returning a value of 12:
=5+7
Most Excel formulas contain references to cells rather than speciﬁ c values. This allows
you to change the values being used in the calculation without having to modify the
formula itself. For example, the following formula returns the result of adding the values
stored in cells A1 and B2:
=A1+B2
If the value 5 is stored in cell A1 and the value 7 is stored in cell B2, this formula
would also return a value of 12. If you then changed the value in cell A1 to 10, the
formula returns a value of 17. Figure 1-19 describes the different arithmetic operators and
provides examples of formulas.
Figure 1-19
Excel arithmetic operators
Arithmetic
Operator
Operation
Example
Description
Addition
+
=10+A1
=B1+B2+B3
Adds 10 to the value in cell A1
Adds the values in cells B1, B2, and B3
Subtraction
=C9–B2
=1–D2
Subtracts the value in cell B2 from the value in cell C9
Subtracts the value in cell D2 from 1
Multiplication
*
=C9*B9
=E5*0.06
Multiplies the values in cells C9 and B9
Multiplies the value in cell E5 by 0.06
Division
/
=C9/B9
=D15/12
Divides the value in cell C9 by the value in cell B9
Divides the value in cell D15 by 12
Exponentiation
^
=B5^3
=3^B5
Raises the value of cell B5 to the third power
Raises 3 to the value in cell B5
If a formula contains more than one arithmetic operator, Excel performs the
calculation using the same order of precedence you might have already seen in math classes.
The order of precedence is a set of predeﬁ ned rules used to determine the sequence
in which operators are applied in a calculation. Excel ﬁ rst calculates the value of any
operation within parentheses, then it applies exponentiation (^), multiplication (*), and         Search JabSto ::

Custom Search