OpenOffice Calc, according to me, is the crown jewel of OpenOffice Suit. Since its launch it has been said to be the top competitor for MS Office. We can use Calc for a lot of things, and when I say a lot I mean a lot, you can replace it as a calculator, use it as your simple database, create webpage etc. But I am sure you would agree that it will not do as good a job as the other programs in the suit which are meant for those purpose will do. But Calc can do one job better than anyother and does it with perfection is to help you manipulate your data.
You can manipulate your data manually (which is very boring) or let calc do it for your with the help of built in or user defined functions (Which is fun).
Lets start with our journey towards the fun of using the functions in OpenOffice Calc. I am trying to put some humor in this, which I am definitely not good at.
An eminent journalist Mr. Bruce Byfield has given a very beautiful introduction to the funcitons and formula in Calc at his blog. Please visit his blog to get a greater understanding.
According to him a Function is a pre-defined calculation entered in a cell to help you analyze or manipulate data in a spreadsheet.
Example of functions are SUM for addition, DATE (provides an internal number for the date given), ABS(Absolute value of a number) etc.
The main use of functions is to help you do the calculations faster, and the less chance of error. Imagine if we have to write all the formula by ourselves like:
If we have to calculate the Future value of an annuity we can do this in the following way.
=A*(((1+i)^n)-1)/i
Where:
A is annuity, i is interest rate and n is number of payments.
or we can write as
=FV(i; n; A) It will give a negative result which we can convert to positive based on our calculations requirement.
I guess the second one is easier to do and to review and definitely with lesser chance of error.
A formula can consist of the following element types.
A) Operators: A symbol or function representing a mathematical operations like +, -, *
There are three main type of operators used in Calc:
A.1) Arithmetical Operators
These operators return numerical results
| Operator | Name | Example |
| + (Plus) | Addition | 1+1 |
| - (Minus) | Subtraction | 2-1 |
| - (Minus) | Negation | -5 |
| * (asterisk) | Multiplication | 2*2 |
| / (Slash) | Division | 9/3 |
| % (Percent) | Percent | 15% |
| ^ (Caret) | Exponentiation | 3^2 |
A.2) Comparative Operators
These operators return either true of flase.
| Operator | Name | Example |
| = (equal sign) | Equal | A1=B1 |
| > (Greater than) | Greater than | A1>B1 |
| < (Less than) | Less than | A1 |
| >= (Greater than or equal to) | Greater than or equal to | A1>=B1 |
| <= (Less than or equal to) | Less than or equal to | A1<=B1 |
| <> (Inequality) | Inequality | A1<>B1 |
A.3) Text Operators:
The operators combines separate texts into one text.
| Operator | Name | Example |
| & (And) | "Sun" & "day" is "Sunday" |
B) Cell References: This can be named cells and ranges and can refer to cells in the current worksheet, cell in another worksheet or even in another workbook.
C) Values or strings: 100 or “John Smith”.
D) Worksheet functions and their arguments: These include functions like SUM for addition, FV (for calculation of Future value of annuity) etc
E) Parentheses: This controls the sequence of operations.
Entering a formula into a cell:
To enter a formula into a cell we start with equal sign (=) it enables the calc understand that the coming expression is a function or formula and it calls the calculations as instructed.
Example:
=SUM(A1:A10) Calculates the sum of the values in the cells from A1 to A10.
=SUM(A1; A6; A10) Calculates the sum of the values in the cells A1, A6 and A10.
Note: A note to the people who are migrating from MS office: In MS Excel we use comma (,) and colon (:) as the reference operators, but in calc we use semi colon (;) and colon (:) as reference operators.
| Operator | Name | Example |
| : (Colon) | Range | A1:C108 |
| ; (Semi Colon) | Union | =SUM(A1;A3; B19) Combines multiple cells and ranges into one |
| ! (Exclamation point) | SUM(A1:B6!B5:C12) Calculates the sum of all cells in the intersection; in this example, the result yields the sum of cells B5 and B6. |
And yes off course as I said earlier we can use our age old learning of formula to use here. Like we can do the same as =A1+A2+A3+A4.....+A10. I will stick with the new learning, and leave the rest to you.
A good way of making the references to cells and cell ranges in formulas legible is to give the ranges names. This ensures that we commit lesser mistakes in referencing the cells.
For example, we can name the range A1: B2 Start. We can then write a formula such as “=SUM(Start)”. Even after we insert or delete rows or columns, OpenOffice still correctly assigns the ranges identified by names. Ranges must not contain any spaces.
It is much easier to read a formula for sale tax if we can write “=Amount*Tax_rate” instead of “=A5*B12”. In this case, we would name cell A5 is Amount and Cell B12 as Tax_rate.
To define name for a cell we can use the Define Names dialog for formulas or parts of formulas we need more often. In order to specify range names,
Select a cell or range of cells, then choose Insert - Names - Define. The Define Names dialog appears.
Type the name of the selected area in the Name field. Click Add. The newly defined name appears in the list below. Click OK to close the dialog.
We can also name other cell ranges in this dialog by entering the name in the field and then selecting the respective cells.
If we type the name in a formula, after the first few characters entered we will see the entire name as a tip.
Press the Enter key in order to accept the name from the tip.
If more than one name starts with the same characters, we can scroll through all the names using the Tab key.
That is all for today, I will be discussing more on what I am studying at this time very soon, ie, Cell and range referencing.
Regards,
Karti
