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