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.
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.
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
| =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.|
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
Parent topic: Using formulas in calculations
Relative and absolute references
|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 |