Concepts

What are formulas?

As explained in another concept, the three types of data in a spreadsheet are text, numbers, and formulas (or functions). This concept defines what a formula is, and how formulas are different from the other two types of data found in a spreadsheet.

Formulas allow you to specify the value of one cell in terms of the value of other cells. For example, the following spreadsheet uses a formula to compute the distance in miles given the distance in kilometers in another cell:

The formula in cell B2 computes the distance in miles from the value in cell B1 and the constant 0.62.

There is a very precise mathematical definition for what a formula is, but in the context of spreadsheets a formula is an equation for computing a value. Formulas are composed of operands and operators. Operands are usually constant numbers and cell references, and operators are mathematical operators and builtin functoins.

If you are going to write formulas with more than one operation (a likely situation) you will need to understand the standard order of evaluation and how parentheses effect the order of evaluation. For example, given the formula =1+2*3, which operation is done first? There are three possibilities:

  1. (1+2) * 3 ==> 9
  2. 1 + (2 * 3) ==> 7
  3. random

The correct answer is #2. The rules that govern the order of evaluation are:

  1. Expressions within parentheses are evaluated first. You can have as many nested parentheses as you like. For example, (((a1/4)+(b1+2)) * 3). The innermost set of parentheses is evaluated first.

  2. Mathematical operators are assigned to a group. Each group is given a priority. Operators in the group with the highest priority are done first. See the table at the right for the groups of operators and their relative priorities.

  3. Operators at the same priority are performed left to right. For example, in the formula 4/2*2, the / and * operators have the same priority, so going left to right the / operator is performed before the * operator.
Priority Symbol Operation
First ^ Exponentiation
Second +
-		 Positive,
negative
Third *
/		 Multiplication,
division
Fourth +
-		 Addition,
subtraction
Fifth =
<>
<
>
<=
>=		 Equals,
not equals,
less than,
greater than,
less than or equal to,
greater than or equal to,
Sixth ~ NOT
Seventh |
&		 OR,
AND

Copyright 1996 by the Curators of the University of Missouri