Formulas and Functions
Recognize good practice in formula creation: refer to cell references rather than type numbers into formulas.
We have been entering either text or numbers into the cells till now, but in case the data in a cell is dependent on the value of data of other cells, we use formulas. Formulas use numbers and variables to get the required values. These variables are the cell references of the cells from where we need to get the data to be used in the formulas.
Whenever a formula is created in Calc, one thing should always be taken into consideration. We should always try and use cell references in the formulas instead of directly using numbers. This helps in decreasing the efforts in changing the formulas every time a value needs to be changed. If we keep such values directly in a formula, we’ll have to change the formula each time the value is changed. But if we keep such values in a separate cell and use the cell reference in the formula, then we’ll have to simply change the value in one cell and all the formulas will be updated. This saves time and effort.
Create formulas using cell references and arithmetic operators (addition, subtraction, multiplication, division).
There are four types of arithmetic operators; addition, subtraction, division and multiplication. These return numerical results.
Any formula must always begin with an ‘=’ symbol. The formulas can be entered by either using the function wizard or by typing directly into the cell or the input line.
‘=b4+b6’ gives the sum of the values stored in the cells ‘B4’ and ‘B6’. ‘B4’ and ‘B6’ are the cell references here and ‘+’ is the arithmetic operator.
To create this formula, double click on the cell where this formula needs to be entered. Then type in the formula in the cell and press ‘Enter’ from the keyboard.
This will showcase the result of the formula in that cell.
The subtraction, multiplication and division operators can be used in the same way.
Identify and understand standard error values associated with using formulas: #NAME?, #DIV/0!, #REF!.
While using formulas, it is very common to get errors. These can occur due to a variety of reasons, but the important part is to identify these, so that we can correct them. Error messages or values are the simplest tools which help us in identifying these errors. Some of the most common error-values associated with using formulas are given below,
#Name? : This error value is displayed in place of the error code Err:525. This code signifies that no valid reference exists for the argument. This means that if we provide some argument to the function incorrectly, say ‘b’ in place of ‘b6’ or any such mistake, then this error value will get displayed.
#DIV/0! : This error value is displayed in place of the error code Err:532. This signifies division by zero. This means that if we write a formula of the type, ‘=b4/b6’ and b6 here contains 0, then this error value will get displayed as division by zero is not a proper mathematical operation.
#REF! : This error value is displayed in place of the error code Err:524. This signifies that the column, row, or sheet for the referenced cell is missing. This means that if we are referring a sheet in a formula or a function, which has been deleted, then this error value will get displayed.
Understand and use relative, absolute cell referencing in formulas.
Referencing is the way by which we refer to the location of any cell in Calc. There are two types of references, absolute and relative.
In relative referencing, when we use a formula to refer to two or more cells, and then when we copy this formula to a new location, then the new formula does not refer to the same cells. It refers to new cells which have the same relative position to the formula as was the case with the original referencing.
To understand in a better way, let us take an example. As shown below, we have put the formula ‘=b4+b6’ in the cell ‘b9’. Thus the answer of this calculation, 11, is being displayed in this cell. When we copy the formula and paste it in ‘c9’, then the result will be 10 and not 11. This is because, the formula copied will have new references ‘c4’ and ‘c6’ as these have the same relative locations to ‘c9’, as was the case with ‘b4’, ‘b6’ and ‘b9’. Thus the sum of the values stored in ‘c6’ and ‘c9’ will get displayed, which is 10. This is how relative referencing works.
In absolute referencing, the references do not change with the formula and the same reference is copied when the formula is copied to another cell. This is achieved by writing the formula as, ‘=b4+$b$6’. This will change the ‘b4’ reference when the formula is copied, but the ‘b6’ reference will remain as it is. This has been shown in the diagram given below.