About formulas and functions

You enter a formula into a sheet to perform a calculation on numbers, text, or other formulas. When you use formulas, your data becomes dynamic. Functions are built-in formulas that perform specialized calculations automatically. You can use a function by itself as a formula, or combine it with other functions and formulas. Use operators to indicate how the parts of a formula are related.

You enter a formula in a cell as you enter any data into a sheet. All formulas begin with an equal sign (=). You can reference a range of cells on the same sheet or a different sheet by dragging to select the range and pressing Enter. The range address is automatically added to the formula.

After you enter the formula, its result is displayed in the cell. To see the formula, look in the formula bar. You can always type the numbers or text that you want to calculate directly into the formula. You can also enter the data in other cells, and then use cell addresses in the formula.

Top section of spreadsheet showing how the formula =A1+A2 in cell A3 displays the formula in the formula bar, but the sum in cell A3.

Use an exclamation point (!) to separate a sheet name from a cell or range address, for example Sheet2!A1 or Sheet2!A1:A4. Use a comma (,) to separate one argument from another in a function, for example =SUM(Sheet1!A1,5,1234). To prevent an argument that contains a comma, for example 1,234, from being recognized as two arguments, you must add quotation marks around the argument, for example "1, 234", or omit the comma, for example, 1234.

Some locales, such as German, use a semicolon (;) to separate arguments–in these cases a message instructs you if you try to enter a comma between arguments.

Note: If you imported your spreadsheet from IBM Lotus Symphony, which uses a period (.) to separate a sheet from cells and a semicolon (;) to separate arguments, they will be converted to ! and , when you edit the spreadsheet.
Table 1. Examples of formulas
Formula Result
=A1+10 Shows the contents of cell A1 plus 10.
=A1*16% Shows 16% of the value of A1.
=A1*A2 Shows the result of the multiplication of A1 and A2.
=ROUND(A1,1) Shows the contents of cell A1 rounded to one decimal place.
=B8-SUM(B10:B14) Calculates B8 minus the sum of the cells B10 to B14.
=SUM(B8,SUM(B10:B14)) Calculates the value of the sum of cells B10 to B14 and adds the value to B8.
=SUM(B1:B65536) Sums all numbers in column B.
=IF(trim(A1)="","Cell should not be empty",A1) Corrects invalid input.
Tip: The basic arithmetic operators (+, -, *, /) can be used in formulas using the "Multiplication and Division before Addition and Subtraction" rule. Instead of using the function =SUM(A1:B1) you can type =A1+B1. Parentheses can also be used. The result of the formula =(1+2)*3 produces a different result than =1+2*3.

The following operators are supported.

Table 2. Operators by type
Type Operators Description
Arithmetic operators + - * / % ^ Symbols for addition, subtraction, multiplication, division, percent, and exponentiation
Text operator & Symbol for combining strings
Logical operators = < > <= >= <> Symbols for equal to, less than, and so on
Reference operators : , space Symbols for calculations involving cells. A colon indicates a cell range; a comma, a concatenation, or union, of cells; and a space, an intersection of cells

Trademarks | IBM Connections wiki