3.1.2 Calculations in Calc. Creating and Coping Formulas

Functions and Formulas are powerful tools in spreadsheets which can save you hours of work.

Functions are routines that are built into the Calc spreadsheet—in other words, there’s not much you need to do to set them up and make them work.

Formulas, on the other hand, are defined by the user—in other words, you have to know what you’re doing and set everything up yourself. Formulas will often include the built-in functions as part of the formula.

Both functions and formulas accomplish the same task: they tell Calc to do some kind of more or less complex or time-consuming calculation for you.

The "=" symbol at the beginning is a clue to the system that what follows is a function or formula.

ALL SPREADSHEET FORMULAS BEGIN WITH THE EQUALS = SIGN.

Then the expression is written. To write down arithmetical expression use numeric constants, cell’s addresses, intrinsic functions, and arithmetic operations + , - , * , / , ^ . For example,

=A1*2,5 =B2+C2+D2 =C2*10%/5 =SIN(2*F4)+1 =SUM(B2:B9)

The formula =SUM(D10:H10) tells Calc to sum (add together) the scores entered in cells D10 through H10 and store the result in cell J10 (cell J10 is the cell in which you want the formula to put the result of doing its calculation).

SUM is one of many Calc built-in functions. It ADDS UP or TOTALS the series of additions to produce the required result.

Calc has such Category of function: Database, Date&Time, Information, Financial, Logical, Mathematical, Statistical, Spreadsheet, and Text sets of built-in Functions.

The addresses of cells or ranges can be the arguments of functions. The range is continuous rectangular area of cells, the address of a range is set by the indication of the right top cell and through a colon left bottom cell. For example, C2:D4 is range that consists of 6 cells.

Table 3.1. Mathematical functions.

Function(engl)	Description
ABS(x)	x - absolute value of x
ACOS(x)	Arccos(x)
ASIN(x)	Arcsin(x)
ATAN(x)	Arctg(x)
COS(x)	Cos(x)
EXP(x)	e^x
LN(x)	Ln(x)
LOG(x;a)	Loga x
SIN(x)	Sin(x)
TAN(x)	Tg(x)
SQRT(x)	Square root value x -
Power(x,a)
MINVERSE(m)	Formula array. Returns the inverse matrix, the argument m is range
MDETERM(m)	Returns the determinant of given matrix from range m
MMULT(m1;m2)	Formula array. Returns the matrix – production of matrixes from ranges m1 and m2
PI()	Returns the value of number 
SUM(d)	Returns the sum of all numbers from range d

Table 3.2. Ststistical functions

AVERAGE(d)	Returns the average of all numbers from range d
MAX(d)	Returns the maximum value of all numbers from range d
MIN(d)	Returns the minimum value of all numbers from range d

Table 3.3. Logical functions

IF(condition; expr1; expr2)	Returns expr1 if condition is true, and expr2 if condition is false
AND(expr1; expr2)	Returns True if both expr1 and expr2 are true and False if one of expr1 or expr2 are false
OR(expr1; expr2)	Returns True if one of or both expr1 and expr2 are true and False if both expr1 and expr2 are false

You can copy values and formulas from one cell to another. Use for copy buffer (tools Copy, Cut and Paste) or marker of auto filling (little black square in the bottom right corner of a cell), drug it though cells for copy.

There exist two kinds of addresses in the formulas: relative address of a cell and absolute address of a cell. The relative address changes at copying, and absolute one does not. Calc automatically adjusts the range of cells when you copy formulas. This is called Relative referencing. The attribute of the absolute address is served by a mark $. For instance, in A2 cell formula =A1+$D$2 is written and you copy it to A3 cell, you receive =A2+$D$2 formula. In next cell will be formula =A3+$D$2 and so on.

Relative references

You might find this a bit tricky to follow, so put on your thinking cap, OK? The system is copying the formula in cell J10 to cell J14 in relation to ("relative to") cell J10. In other words, just as the formula in J10 sums the values stored in cells D10 through H10, so the formula copied to cell J14 will sum the values relative to cells D14 through H14.

Does that make sense? If so, give yourself a pat on the back! If not, don't despair. Read it over a couple of times. The alternative to a Relative Reference, by the way, is an Absolute Reference. You will need to use an Absolute Reference shortly, at which time you will more easily understand what it means in the context of the exercise.

Adding functions to the spreadsheet

Calc comes with many built-in functions for the spreadsheet. Let’s look at some of the built-in functions so that you know how to find them when you need them. In the Formula Bar, click on the Function Wizard tool.

Clicking on the Function Wizard tool will bring up the Function Wizard dialog box (Fig. 3.3) In the Category section, click on the down arrow to bring up the various Categories of Functions available in LibreOffice (Fig. 3.3) and select the category you need, then, in the Function section of the dialog box, check out the several dozen functions available to you there.

Fig. 3.3 The Function Wizard dialog box

When Automatic Calculation is switched on, Calc immediately carries out any calculations that are necessary when you make any changes to the data in the spreadsheet. With automatic calculation, you don't have to remember to do this yourself. If Automatic Calculation is not already on by default, it will be good for you to turn it on, so here’s how you do it. In the Tools menu > Cell Contents…, check to see, in the Cell Contents sub menu, that there is a check mark next to AutoCalculate.

Example: Lets calculate the average value of numbers from range D14:D23. Select the cell D25, then click on the Function Wizard (fx) icon in the data entry bar and, in the Function Wizard dialog box, from the list of Categories, select the Statistical category (Fig. 3.4).

From the list of Statistical functions, double click on the AVERAGE function to select it. Calc pastes the function into the Formula area of the dialog box, followed by a set of empty brackets. The brackets are for the set of cells that contain the scores that are to be averaged. Notice that the cursor is waiting between the brackets for you to select that set of cells. Type D14:D23 (Fig. 3.4) then click on OK.

Alternatively, you could use the mouse to drag down the set of cells from D14 to D23 with the same result. Look at the data entry bar at the top of the spreadsheet and notice that the formula =AVERAGE (D14:D23) is copied there, too.

Fig. 3.4 The Insert Function dialog box showing the Statistical category of Functions