Formulas
Formulas
In a spreadsheet often the contents of one cell depend on the contents of other cells. For example in a list of item prices, the total depends on each individual item price.
To handle this situation, we use a third type of data: the formula. Formulas are equations using numbers and variables to get a result. In a spreadsheet, the variables are cell locations that hold the data needed for the equation to be completed. Using formulas we can write the total in the previous example as =C3+C4+C5. When you type the formula and press enter the result again will be 30. Νow every time we update the value of one of the items, the total is dynamically calculated.
Creating and editing formulas
To enter a formula, type directly the formula into the cell or into the input line of the formula bar. A formula must always begin with an = symbol. When you finish typing the formula press the Enter key.
The cell containing the formula normally displays the result of the formula. To display and edit the actual formula double click inside the cell or go to the input line. When a formula is in edit mode, references to other cells are highlighted. To exit formula edit mode and display the result or cancel the changes you have made to the formula press the ESCAPE key.
Using point and click
When in formula edit mode, instead of manually typing a cell reference, you can just click on a cell and the cell's reference will be filled into the formula. This point and click technique can help you type long and complex equations.
Operators in formulas
Formulas in Calc are like mathematical equations. You can use a combination of numbers, cell references and operators to perform from simple to complex calculations. Here we list the basic operator types supported in LibreOffice Calc.
Arithmetic
| Operator | Name | Example |
| + | Addition | = 1 + 1 |
| - | Subtraction | = 5 - 2 |
| - | Negation | = -12 |
| * | Multiplication | = 5 * 3 |
| / | Division | = 6 / 2 |
| % | Percent | 15% |
| ^ | Exponentiation | 2^3 |
Order or operations
The order of operations in Calc follows the same rules you learned in math class. A common technique for remembering the order of operations is the abbreviation (or, more properly, the "acronym") "PEMDAS":
P Operations enclosed in parentheses
E Exponential calculations (3^2, for example)
MD Multiplication and division, whichever comes first
AS Addition and subtraction, whichever comes first
For example the formula 3^2*2+1 is evaluated in the following order.
3^2*2+1 # 3^2
9*2+1 # 9*2
18+1
19
To avoid mistakes in formulas it's a good practice to use parenthesis when you are not certain about the order of operations.
Concatenation operator &
It is common for users to place text in spreadsheets. A lot of times we need to join pieces of text found in different cells. For this purpose Calc has the concatenation operator &. In the following example text in the arguments columns is concatenated to the result column.
Notice that when you use text in formulas you must surround it with quotation marks " ".
Later you will learn to perform more text manipulations using Calc predefined functions. In fact Calc has a CONCATENATE function which performs the same operation.
Autofilling formulas
Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. In the following example the line total column displays the total for each line using the formula Quantity * Price per Unit. You can repeat this process for the remaining lines but manually filling the formula becomes very tedious (if not impossible) as the number of lines increases.
The solution to this problem is to use the Autofill feature of Calc. Place the mouse cursor on the bottom left corner of the formula cell. When the fill handle 
appears drag the mouse to fill the adjacent cells with the formula.
The formulas are now filled with the correct cell references.
