All formulas begin with an equals sign. The formulas can contain numbers, text, arithmetic operators, logic operators, or functions.
Remember that the basic arithmetic operators (+, -, *, /) can be used in formulas using the "Multiplication and Division before Addition and Subtraction" rule. Instead of writing =SUM(A1:B1) you can write =A1+B1.
Parentheses can also be used. The result of the formula =(1+2)*3 produces a different result than =1+2*3.
Here are a few examples of LibreOffice Calc formulas:
|
=A1+10 |
Displays the contents of cell A1 plus 10. |
|
=A1*16% |
Displays 16% of the contents of A1. |
|
=A1 * A2 |
Displays the result of the multiplication of A1 and A2. |
|
=ROUND(A1;1) |
Displays the contents of cell A1 rounded to one decimal place. |
|
=EFFECTIVE(5%;12) |
Calculates the effective interest for 5% annual nominal interest with 12 payments a year. |
|
=B8-SUM(B10:B14) |
Calculates B8 minus the sum of the cells B10 to B14. |
|
=SUM(B8;SUM(B10:B14)) |
Calculates the sum of cells B10 to B14 and adds the value to B8. |
It is also possible to nest functions in formulas, as shown in the example. You can also nest functions within functions. The Function Wizard assists you with nested functions.
There are various ways to copy a formula. One suggested method is:
Select the cell containing the formula.
Choose Edit - Copy, or press Ctrl+C to copy it.
Select the cell into which you want the formula to be copied.
Choose Edit - Paste, or press Ctrl+V. The formula will be positioned in the new cell.
If you want to copy a formula into multiple cells, there is a quick and easy way to copy into adjacent cell areas:
Select the cell containing the formula.
Position the mouse on the bottom right of the highlighted border of the cell, and continue holding down the mouse button until the pointer changes to a cross-hair symbol.
With the mouse button pressed, drag it down or to the right over all the cells into which you want to copy the formula.
When you release the mouse button, the formula will be copied into the cells and automatically adjusted.
If you do not want values and texts to be automatically adjusted, then hold down the Ctrl key when dragging. Formulas, however, are always adjusted accordingly.
You can enter formulas in several ways: using the icons, or by typing on the keyboard, or by a mixture of both methods.
Click the cell in which you want to enter the formula.
Click the Function icon on the Formula Bar.
You will now see an equals sign in the input line and you can begin to input the formula.
After entering the required values, press Enter or click Accept to insert the result in the active cell. If you want to clear your entry in the input line, press Escape or click Cancel.
You can also enter the values and the formulas directly into the cells, even if you cannot see an input cursor. Formulas must always begin with an equals sign.
You can also press the + or - key on the numerical keyboard to start a formula. NumLock must be "on". For example, press the following keys in succession:
+ 5 0 - 8 Enter
You see the result 42 in the cell. The cell contains the formula =+50-8.
If you are editing a formula with references, the references and the associated cells will be highlighted with the same color. You can now resize the reference border using the mouse, and the reference in the formula displayed in the input line also changes. Show references in color can be deactivated under Tools - Options - Trine Office Calc - View.
If you would like to view the calculation of individual elements of a formula, select the respective elements and press F9. For example, in the formula =SUM(A1:B12)*SUM(C1:D12) select the section SUM(C1:D12) and press F9 to view the subtotal for this area.
If you want to display the formulas in the cells, for example in the form =SUM(A1:B5), proceed as follows:
Choose Tools - Options - Trine Office Calc - View.
In the Display area mark the Formulas box. Click OK.
If you want to view the calculation results instead of the formula, do not mark the Formulas box.
The following is an example of a calculation in Trine Office Calc.
Click in a cell, and type a number
Press Enter.
The cursor moves down to the next cell.
Enter another number.
Press the Tab key.
The cursor moves to the right into the next cell.
Type in a formula, for example, =A3 * A4 / 100.
Press Enter.
The result of the formula appears in the cell. If you want, you can edit the formula in the input line of the Formula bar.
When you edit a formula, the new result is calculated automatically.
You can automatically fill cells with data with the AutoFill command or the Series command.
AutoFill automatically generates a data series based on a defined pattern.
On a sheet, click in a cell, and type a number.
Click in another cell and then click back in the cell where you typed the number.
Drag the fill handle in the bottom right corner of the cell across the cells that you want to fill, and release the mouse button.
The cells are filled with ascending numbers.
To quickly create a list of consecutive days, enter Monday in a cell, and drag the fill handle.
Hold down Ctrl if you do not want to fill the cells with different values.
If you select two or more adjacent cells that contain different numbers, and drag, the remaining cells are filled with the arithmetic pattern that is recognized in the numbers. The AutoFill function also recognizes customized lists that are defined under .
You can double-click the fill handle to automatically fill all empty columns of the current data block. For example, first enter Jan into A1 and drag the fill handle down to A12 to get the twelve months in the first column. Now enter some values into B1 and C1. Select those two cells, and double-click the fill handle. This fills automatically the data block B1:C12.
Select the cell range in the sheet that you want to fill.
Choose .
Select the parameters for the series.
If you select a linear series, the increment that you enter is added to each consecutive number in the series to create the next value.
If you select a growth series, the increment that you enter is multiplied by each consecutive number to create the next value.
If you select a date series, the increment that you enter is added to the time unit that you specify.
