Using Formulas and Functions
Formulas can be used for basic operations such as addition, subtraction, as well as more complex calculations such as
income tax calculations, averaging. The advantage of using formulas is that even if the data is changed, Calc will
automatically recalculate the answer without the need to rewrite the formula again. Another advantage of using
formulas is that they can be easily copied to a number of cells.
Precedence of Operations:
If multiple operators occur in a formula, then the calculation is performed by using the following order:
1. Operations enclosed in parenthesis.
2. Exponential
3. Multiplication and division. (If both multiplication and division occur in a formula then the calculation is
performed from left to right.)
4. Addition and Subtraction. (If both addition and subtraction occur in a formula then the calculation is
performed from left to right.)
Functions
Calc has a set of predefined formulas called functions. They differ from formulas in the sense that in a formula we
provide both the operands and the operator, however in functions we only provide operands (or arguments) as
functions have predefined operation to be performed on the arguments. Just like a formula, the function also begins
with an equal (=) sign. We specify the arguments enclosed in parenthesis () and separated by a comma (,).
For example, SUM function is used to add as shown below:
= SUM (3, 4) – This will add numbers 3 and 4.
= SUM (A3, A4, A5) – This will add numbers in cell A3, A4 and A5.
= SUM (3, 4, A3, A4) – This will add number 3, 4, contents of cell A3 and A4.
= SUM (A3:A5) – This will add numbers in cell A3, A4 and A5.
Commonly used mathematical functions in Calc are:
1. SUM (n1, n2…) – To calculate the sum of the arguments
2. PRODUCT (n1, n2…) – To calculate the product of arguments n1, n2…
3. SQRT (n) – To calculate the square root of a number n.
4. POWER (n, p) – To calculate power p of a number n.
5. LOG (n, b) – To calculate log a number n to base b.
6. ROUND (n, d) – To round a number n to digits d.
7. SIN (n), COS (n), TAN (n) – To calculate sine, cosine and tangent of n.
8. RAND BETWEEN (f, l) – Returns a random number between f and l.
9. QUOTIENT (a, b) – To calculate integer quotient of division a/b.
10. ABS (n) – To calculate the absolute value of a number n.
11. AVERAGE (n1, n2….) – To calculate average of arguments n1, n2….
Absolute and Relative Addressing:
Relative Addressing: All cell references are by default relative i.e., they adjust and change when copied or when
using AutoFill. When you enter an address A in a cell B then Calc does not store the actual address of A but instead it
stores the number of rows and columns relative to the cell containing the address B or in other words how to reach A
from B.
Absolute Addressing: Absolute addressing is used when the requirement is to retain the cell address even if it is
copied to some other cell or when using AutoFill. Dollar signs ($) are used to hold a column and/or row address
constant. When you enter an address in a cell that is prefixed with a $ sign, then Calc stores the absolute address of
the cell.
Mixed Addressing: Mixed addressing is a combination of absolute and relative addressing used as $A3, B$5, A5+$B4,
$A1+B$1 etc. In such cases, whichever part (row or column) is prefixed with $ remains unchanged and the ones not
prefixed with $ are calculated in a relative manner.
Sorting and Filtering Data
Sorting: Data can be easily sorted in Calc by using the sort command available on the Standard toolbar (Sort
Ascending, Sort Descending) and also in the Data menu which opens a dialog box for more options. You can also
select multiple criteria (up to 3) to sort with each criterion applied one after the other.
Filtering Data : Applying a filter means displaying data based on some conditions or filters. Filters can be applied by
using the Filter option in the Data menu.
Three types of filters can be applied – AutoFilter, Standard Filter and Advance Filter.
1. AutoFilter
This filter is added on the topmost row of the selected data in the form of a drop-down list from which you can select
data to be displayed or in other words apply filters.
2. Standard Filter
Standard filter is used to provide more options for filtering such as combining multiple filters by using AND and OR
operator. AND operator implies that all the filters must be satisfied for displaying the data. OR operator implies that
at least one filter should be satisfied for displaying the data.
Removing AutoFilter – Select the cells as selected while adding the filter. From the Data menu, select Filter->Remove
Filter. The filters will be removed.
Creating Charts and Graphs
In Calc, graphs and charts are used to represent the data graphically. Various types of charts can be created in Calc. It
offers a choice of 10 chart types such as Column chart, Bar chart, Pie chart, Area chart. Each of the chart types has
several sub-types.
Column Chart: A Column chart is used to compare values across categories by using vertical bars.
Bar Chart: A Bar chart is the horizontal version of a column chart. It is used if you have large text labels.
Pie Chart : A Pie chart is used to display the contribution of each value (slice) to a total (pie) i.e., it can be used to plot
various pieces of a single entity.
Line Chart : A Line chart is used to compare trends and changes in values over time.
Scatter Chart : A Scatter chart is used to display relationships between variables.
Chart elements:
1. Title of the graph
2. Subtitle of the graph
3. Titles of X-axis and Y-axis
Legends: Most commonly located on the right of the chart, legends help the readers of the chart to decode the
charted data by associating descriptive text with the colours or patterns of data in the chart.
Grids: To make the data in a chart that displays axes easier to read, you can display horizontal and vertical chart
gridlines.
Macros
A macro is a recording of each and every command and action you perform to complete a task. A macro records your
mouse clicks and keystrokes while you work and play them back later. When you run the macro, it plays those actions
back in the exact same order. Thus, if you want to repeat the actions multiple times you just need to run the macro.
Create/Record Macros: Following are the steps to record/create a macro:
1. Go to the Tools tab on the Menu bar->Macros->Record Macro. A small dialog box will appear on the
worksheet displaying Stop Recording.
2. Once the Stop recording dialog box appears, Calc will start recording the Macro. Until you stop the recording,
every Calc command and keystroke will be recorded in the macro, in the order in which they are entered.
3. After we have done all the required tasks, we will stop recording the macro by clicking on the Stop recording
option on the dialog box. A new dialog box will open (OpenOffice Basic Macros) in which you have to specify
the name of the macro and the location for saving it.
Now we have a macro that can perform in a single operation all the tasks we have performed during
recording of the macro.
Run/Use Macros created: Following are the steps to run/use a macro that we have already created:
1. Go to the Tools tab on the Menu bar->Macros->Run Macros.
2. A dialog box will open showing all the macros created. We will select the one to run and then click on Run
button.
3. The tasks performed during recording of the macro will be repeated in the same order in which they were
performed.
By default, recorded macros use absolute cell referencing, which means that exact cell locations are recorded into
the macro.
Delete Macros: Following are the steps to delete a macro that we have already created:
1. Go to the Tools tab on the Menu bar->Macros->Organize Macros and select the OpenOffice Basic option.
2. A dialog box listing the macros created will open. Select the macro you want to delete and then click on the
Delete button.
Printing Spreadsheets
Calc offers various options for printing spreadsheets. Many details can be selected for what is to be printed and what
not.
For printing, open the File tab on the Menu bar and select the Print option. A dialog box will open in which you can
select various options such as:
1. All Sheets, Selected Sheets or Selected Cells.
2. All pages or specific pages. Specific page numbers separated by comma or range of pages (such as 1:10) can
be entered in the Pages box.
3. After selecting the options, click on Print to print the spreadsheet(s).