More power with formulas

You can enter formulas in your spreadsheet to perform a calculations on numbers, text, or other formulas. When you use formulas, your data becomes dynamic. Your spreadsheet automatically recalculates a formula when you change data the formula refers to; for more information, see When and how formulas recalculate.

<A HREF="#ljuss_parts_of_a_formula" target="body">Parts of a formula describes all the parts of a formula.

<A HREF="#ljuss_types_of_formulas" target="body">Types of formulas describes the different kinds of formulas you can create.

Fixing common errors in formulas describes how to troubleshoot your formulas.

More power with @functions provides basic information about @functions, the built-in formulas.



<A NAME="ljuss_parts_of_a_formula">Parts of a formula

All formulas contain three basic elements: operands, operators, and parentheses.

Parts of a formula



Operands

Operands are the numbers or text that the formula operates on.

Operand Description Examples
Value Number 450, -92, 7.1E+12
Text Sequence of characters enclosed in double quotation marks "Budget", "TOTAL", "1955 Hill Valley Road"


Operators

Operators are mathematical symbols, text characters, or logical statements that tell your spreadsheet what to do with the operands in a formula.

Type Operators Description
Arithmetic operators +, -, *, /, ^ Symbols for addition, subtraction, multiplication, division, and so on
Text operator & Symbol for combining strings
Logical operators =, <, >, <=, >=, <> Symbols for equal to, less than, and so on
#AND#, #OR#, #NOT# Words set off with # that evaluate conditions


Parentheses

Parentheses let you combine various operations and multiple formulas in one formula. You can use parentheses in complex formulas to tell your spreadsheet the order in which you want the formulas calculated.

In the following example, the inside formula is set off from the outside formula with parentheses.

(A1+A2*(A3-A4))/A5



<A NAME="ljuss_types_of_formulas">Types of formulas

1-2-3 has three types of formulas: numeric, text, and logical.



Numeric formulas

Numeric formulas perform simple arithmetic or complex calculations involving many variables and use one or more of the arithmetic operators: + (addition), - (subtraction), * (multiplication), / (division), and ^ (exponentiation). The following table shows some examples of simple numeric formulas:

Formula Result
25+5 30
15+A1 20, if A1 contains 5
22*A1/A2 11, if A1 contains 5 and A2 contains 10


Text formulas

Text formulas automatically change or combine text entered in cells. In a text formula, you must enclose text in " " (quotation marks). Text formulas use & (ampersand) to combine strings of text. The following table shows two examples of text formulas:

Formula Result
+"New"&D6 New Totals, if D6 contains the text " Totals"
+"New"+" Totals" New Totals


Logical formulas

Logical formulas use a logical operator or a logical @function to evaluate whether a condition is true or false. The formula result is 1 if the condition is true, and 0 if the condition is false. Logical formulas use the operators listed below to evaluate various conditions.

Operator Result is true when
= Values are equal
< First value is less than second value
> First value is greater than second value
<= First value is less than or equal to second value
>= First value is greater than or equal to second value
<> Values are not equal
#AND# Both conditions are true
#OR# At least one condition is true
#NOT# Condition is false (If the condition is true, the result is false)

The following table shows some examples of logical formulas:

Formula Result
5>4 1 (True).
5>A1 1, if A1 is less than 5.
+A1=1#AND#A2=2 1, if A1 contains 1 and A2 contains 2.0, if either A1 or A2 contains another value.
+A1=1#OR#A2=2 1, if A1 contains 1 or A2 contains 2.
#NOT#A1=0 1, if A1 contains any value other than 0 (zero).


See also
Entering a formula in the worksheet
Editing a formula
Entering a range address in a formula
Entering a range name in a formula
Copying a formula
Moving a formula
Controlling how a formula refers to data