OpenOffice.org/Calc

OpenOffice User's Manual:
OpenOffice.org Calc

Adapted under license from OOoAuthors

What is Calc?

Calc is the spreadsheet component of OpenOffice.org (OOo). You can enter data, usually numerical data, in a spreadsheet and then manipulate this data to produce certain results.

Alternatively you can enter data and then use Calc in a ‘What If...’ manner by changing some of the data and observing the results without having to retype the entire spreadsheet or sheet.

Spreadsheets, sheets and cells

Calc works with elements called spreadsheets. Spreadsheets consist of a number of individual sheets, each containing a block of cells arranged in rows and columns.

These cells hold the individual elements—text, numbers, formulas etc.—which make up the data to be displayed and manipulated.

Each spreadsheet can have many sheets and each sheet can have many individual cells. In version 3.0 of OOo, each sheet can have a maximum of 65,536 rows and a maximum of 1024 columns.

You can enter data into Calc in several ways: using the keyboard, the mouse (dragging and dropping), the Fill tool, and selection lists. Calc also provides the ability to input information into multiple sheets of the same document at one time.

After entering data, you can format and display it in various ways. A function is a pre-defined calculation entered in a cell to help you analyze or manipulate data in a spreadsheet. All you have to do is add the arguments, and the calculation is automatically made for you. Functions are the main reason for spreadsheets. If you understand functions, then you can start to use the real power of a spreadsheet.

Understanding functions

Calc includes over 350 functions to help you analyze and reference data. Many of these functions are for use with numbers, but many others are used with dates and times, or even text. A function may be as simple as adding two numbers together, or finding the average of a list of numbers. Alternatively, it may be as complex as calculating the standard deviation of a sample, or a hyperbolic tangent of a number.

Typically, the name of a function is an abbreviated description of what the function does. For instance, the FV function gives the future value of an investment, while BIN2HEX converts a binary number to a hexadecimal number. By tradition, functions are entered entirely in upper case letters, although Calc will read them correctly if they are in lower or mixed case, too.

A few basic functions are also represented by symbols. For instance, SUM, which adds arguments, can also be entered as + while PRODUCTION, which multiplies arguments, can also be entered as *.

Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your job is to enter the arguments needed to run the function. In some cases, the arguments have pre-defined choices, and you may need to refer to the online help or Appendix B (Description of Functions) in this book to understand them. More often, however, an argument is a value that you enter manually, or one already entered in a cell or range of cells on the spreadsheet. In Calc, you can enter values from other cells by typing in their name or range, or—unlike the case in some spreadsheets—by selecting cells with the mouse. Should the values in the cells change, then the result of the function is automatically updated.

Strictly speaking, when all the arguments are entered and a function is ready to run, it becomes a formula. These terms are sometimes used interchangeably, but the distinction is worth preserving, because a formula can use functions as an argument.

For compatibility, functions and their arguments in Calc have almost identical names to their counterparts in Microsoft Excel. However, both Excel and Calc have functions that the other lacks. Occasionally, too, functions with the same names in Calc and Excel have different arguments, or slightly different names for the same argument—neither of which can be imported to the other. However, perhaps nine-tenths of functions can be imported between Calc and Excel without any problems.

Understanding the structure of functions

Except for simple functions such as + or *, all functions have a similar structure. If you use the right tool for entering a function, you can escape learning this structure, but it is still worth knowing for troubleshooting.

To give a typical example, the structure of a function to find cells that match entered search criteria is:

= DCOUNT (Database;Database field;Search_criteria)

Like most functions, this one starts with an equal sign. It is followed by DCOUNT, the name of the function. After the name of the function comes its arguments. All arguments are required, unless specifically listed as optional.

Arguments are added within the brackets (parentheses) and separated by semicolons, with no space between the arguments and the semicolons. Many arguments are a number. A Calc function can take up to thirty numbers as an argument. That may not sound like much at first. However, when you realize that the number can be not only a number or a single cell, but also an array or range of cells that contain several or even hundreds of cells, then the apparent limitation vanishes.

Other arguments may be a column label, a mathematical constant, or a value unique to that function.

Depending on the function, arguments may have to be entered with straight quotation marks. However, this requirement is not consistent. Otherwise similar formulas may differ only in this requirement, and no simple rule tells you which is which. You simply have to know or check the requirements in the online help.

The only exception to these structural rules are basic arithmetical functions entered with symbols. For example, instead of entering =SUM(2;3), you can enter =2+3.

Advanced structure

As well as being used on its own, a function can be an argument in a larger formula. A formula, however, is limited by the fact that it can only do one function at a time. You need to make sure that functions are done in the right order if the formula is going to work.

To help set the order for functions in a multiple-function formula, you use parentheses within parentheses. When the formula is run, Calc does the innermost function first, then works outwards. For example, in the simple calculation =2+(5*7), Calc multiples 5 by 7 first. Only then is 2 added to the result to get 37.

The placement of functions within sets of parentheses is called nesting. Basically, nesting reduces a function that could run on its own to an argument in the formula. For example, in =2+(5*7), the formula (5*7) is nested within the larger formula of =2+(5*7). In other words, the nested function becomes an argument of another function.

This relation is more obvious when doing a calculation using a function with a name. For all purposes,

=SUM(2;PRODUCT(5;7))

is the same formula as =2+(5*7). However, when SUM and PRODUCT are used, then the relation is clearer. The fact that the PRODUCT function comes after a semicolon and in a set of parentheses for the SUM function makes it clear that PRODUCT is an argument for SUM. In addition, the fact that the inner pair of parentheses is around (5;7) makes clear that this operation is done before the one defined by the outer pair of parentheses.

To get an idea of what nested functions can do, imagine that you are designing a self-directed learning module. During the module, students do three quizzes, and enter the results in cells A1, A2, and A3. In A4, you can create a nested formula that begins by averaging the results of the quizzes with the formula =AVERAGE(A1:A3). The formula then uses the IF function to give the student feedback that depends upon the average grade on the quizzes. The entire formula would read:

=IF(AVERAGE(A1:A3) >85; "Congratulations! You are ready to advance to the next module";
 "Failed. Please review the material again. If necessary, contact your instructor for help")

Depending on the average, the student would receive the message for either congratulations or failure.

Notice that the nested formula for the average does not require its own equal sign. The one at the start of the equation is enough for both formulas.

If you are new to spreadsheets, the best way to think of functions is as a scripting language. We've used simple examples to explain more clearly, but, through nesting of functions, a Calc formula can quickly become complex.

Formulas

See the more specific section: Formulas.

The database (preconditions)

The basis for work with the DataPilot is always a list with your data in raw format. Such a list is comparable to a database table. The table consists of rows (data sets) and columns (data fields). The field names are in the first row above the list.

Later we will explain that the data source could be an external file or database. The simplest use case is that your data is contained in a Calc spreadsheet. For such a list, Calc offers sorting functions that do not depend on the DataPilot.

For processing data in lists, the program must know in which area of the spreadsheet the table is. A Calc table can be anywhere in the sheet, in any position. It is also possible, that a spreadsheet contains several unrelated tables.

Calc recognizes your lists automatically. It uses the following logic:

Starting from the cell you’ve selected (which must be within your list), Calc checks the surrounding cells in all 4 directions (left, right, above, below). The border is recognized if the program discovers an empty row or column, or if it hits the left or upper border of the spreadsheet.

This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.

If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected. This might be useful in only very few cases.

A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.

In addition to these formal aspects, the logical structure of your table is very important for using the DataPilot.

When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example. This will give you a list of bad ideas, that you can find very often among spreadsheet users who are not informed about the possibilities of processing lists within a spreadsheet.

First bad idea: You could have made several sheets. For example, you could have made a sheet for each group of articles. Analyses are then only possible within each group. Analyses for several groups would then be a lot of hassle.

Second bad idea: In the turnover list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.

Third bad idea: You could have entered the amounts in chronological order. At the end of each month you could have made a sum. In this case, a sorting of the list for different criteria is not possible, and you could not use the DataPilot. The rows with the sums would be treated by the DataPilot like any other amount you have entered. Getting monthly results is one of the very fast and easy features of the DataPilot.

You start the DataPilot with Data > DataPilot > Start. If the list to be analyzed is in a spreadsheet table, select one cell within this list. Calc recognizes and selects the list automatically for use with the DataPilot.

Data Analysis

Once you are familiar with functions and formulas, the next step is to learn how to automate the processes and perform useful analyses of the data.

Calc includes several tools to help you manipulate functions and formulas, ranging from features for copying and reusing data, to creating subtotals automatically, to varying information to help you find the answers you need. These tools are divided between the Tools and Data menus.

If you are a newcomer to spreadsheets, these tools can be overwhelming at first. However, they become simpler if you remember that they all depend on input from either a cell or a range of cells that contain the data with which you are working.

You can always enter the cells or range manually, but in many cases, you can also use the Shrink/Maximize icon beside a field to change temporarily the size of the tool’s window while you select the cells with the mouse. Sometimes, you may have to experiment with which data goes into one field, but, once you have found out, the rest is simply setting a selection of options, many of which can be ignored in any given case. Just keep the basic purpose of each tool in mind, and you should have little trouble with Calc’s function tools.

You don’t need to learn them, especially if your spreadsheet use is simple, but as your manipulation of data becomes more sophisticated, they can save time in making calculations, especially as you start to deal with hypothetical situations. Just as importantly, they can allow you to preserve your work and to share it with other people—or yourself at a later session.

Subtotal

SUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of its usefulness, the function has a graphical interface accessible from Data > Subtotals.

As the name suggests, SUBTOTAL totals data arranged in a array—that is, a group of cells with labels for columns and/or rows. Using the Subtotals dialog, you can select arrays, then choose a statistical function to apply to them. For efficiency, you can choose up to three groups of arrays to which to apply a function. When you click OK, Calc adds subtotals and grand totals to the selected arrays, using the Result and Result2 cell styles for them.

To insert subtotal values into a sheet:

Ensure that the columns have labels.
Select the range of cells that you want to calculate subtotals for, and then choose Data > Subtotals.
In the Subtotals dialog, in the Group by box, select the column that you want to add the subtotals to. If the contents of the selected column change, the subtotals are automatically recalculated.
In the Calculate subtotals for box, select the columns containing the values that you want to subtotal.
In the Use function box, select the function that you want to use to calculate the subtotals.
Click OK.

If you use more than one group, then you can also arrange the subtotals according to choices made on the dialog’s Options page, including ascending and descending order or using one of the predefined custom sorts defined in Tools > Options > OpenOffice.org Calc > Sort Lists.

Scenarios

Scenarios are a tool to test “what-if” questions. Use Tools > Scenarios to enter variable contents—scenarios—in the same cell. Each scenario is named, and can be edited and formatted separately, and chosen from a drop-down list in the Navigator and the title bar of the scenario. When you print the spreadsheet, only the contents of the currently active scenario is printed.

By adding a scenario, you can quickly change the arguments of a formula and view the new results. For example, if you wanted to calculate different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. If you had another formula that calculated your yearly income and included the result of the interest rate formula as an argument, it would also be updated. If all your sources of incomes used scenarios, you could efficiently build a complex model of your possible income.

Creating scenarios

To create a scenario, select all the cells that provide the data for the scenario.

Select the cells that contain the values that will change between scenarios. To select multiple cells, hold down the Ctrl key as you click each cell.
Choose Tools > Scenarios.
On the Create Scenario dialog, enter a name for the new scenario. It’s best to use a name that clearly identifies the scenario, not the default name as shown in the illustration. This name is displayed in the Navigator and on the title bar of the scenario on the sheet itself.
Optionally add some information to the Comment box. The example shows the default comment. This information is displayed in the Navigator when you click the Scenarios icon and select the desired scenario.
Optionally select or deselect the options in the Settings section. See below for more information about these options.
Click OK to close the dialog. The new scenario is automatically activated.

Settings

The lower portion of the Create Scenario dialog contains several options. In most cases the default settings (shown selected in the example) are suitable.

Display border

Highlights the scenario in your table with a border. The color for the border is specified in the field to the right of this option. The border has a title bar displaying the name of the last scenario. The button on the right of the scenario border offers you an overview of all the scenarios in this area, if several have been defined. You can choose any of the scenarios from this list without restrictions.

Copy back

Copies the values of cells that you change into the active scenario. If you do not select this option, the scenario is not changed when you change cell values. The behavior of the Copy back setting depends on the cell protection, the sheet protection, and the Prevent changes settings.

Copy entire sheet

Copies the entire sheet into an additional scenario sheet.

Prevent changes

Prevents changes to the active scenario.

You can only change the scenario properties if the Prevent changes option is not selected and if the sheet is not protected.
You can only edit cell values if the Prevent changes option is selected, if the Copy back option is not selected, and if the cells are not protected.
You can only change scenario cell values and write them back into the scenario if the Prevent changes option is not selected, if the Copy back option is selected, and if the cells are not protected.

Working with scenarios using the Navigator

After scenarios are added to a spreadsheet, you can jump to a particular scenario by using the Navigator, then selecting a scenario from the list. You can also color code scenarios to make them easier to distinguish from one another.

To select a scenario in the Navigator, click the Scenarios icon in the Navigator. The defined scenarios are listed, with the comments that were entered when the scenarios were created.

Double-click a scenario name in the Navigator to apply that scenario to the current sheet.

To delete a scenario, right-click the name in the Navigator and choose Delete.

To edit a scenario, including its name and comments, right-click the name in the Navigator and choose Properties. The Edit Properties dialog is the same as the Create Scenario dialog.

To learn which values in the scenario affect other values, choose Tools > Detective > Trace Dependents. Arrows point to the cells that are directly dependent on the current cell.

Goalseeking

Tools > Goal Seek reverses the usual order for a formula. Usually, you run a formula to get the result when certain arguments are entered. By contrast, with Goal Seek, you work with a completed formula to see what values you need in an argument to get the results that you want.

To take a simple example, imagine that the Chief Financial Officer of a company is developing sales projections for each quarter of the forthcoming year. She knows what the company’s total income must be for the year to satisfy stockholders. She also has a good idea of the company’s income in the first three quarters, because of the contracts that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? To answer, the CFO enters the projected earnings for each of the other three quarters and the projection for the entire year. Then she runs a goal seek on the cell for Q4 sales, and receives her answer.

Other uses of goal seek may be more complicated, but the method remains the same. To run a goal seek, at least one of the values for an argument must be a referenced cell or range. Only one argument can be altered in a single goal seek. After you get the result of a goal seek, you can replace the original value in the referenced cell with the result, or record the result elsewhere for later use, possibly as a scenario.

With the help of Goal Seek you can calculate a value that, as part of a formula, leads to the result you specify for the formula. You thus define the formula with several fixed values and one variable value and the result of the formula.

Goal Seek example

To calculate annual interest (I), create a table with the values for the capital (C), number of years (n), and interest rate (i). The formula is I = C*n*i.

Let us assume that the interest rate i of 7.5% and the number of years n (1) will remain constant. However, you want to know how much the investment capital C would have to be modified in order to attain a particular return I. For this example, calculate how much capital C would be required if you want an annual return of $15,000.

Enter each of the values for Capital C (an arbitrary value like $100,000), number of years n (1), and interest rate i (7.5%) in one cell each. Enter the formula to calculate the interest I in another cell. Instead of C, n, and i use the reference to the cell with the corresponding value. In our example, these are B1, B2, and B3.

Solver

Tools > Solver amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables. It is specifically designed to minimize or maximize the result according to a set of rules that you define.

Each of these rules sets up whether an argument in the formula should be greater than, lesser than, or equal to the figure you enter. If you want the argument to remain unchanged, you enter a rule that the cell that contains it should be equal to its current entry. For arguments that you would like to change, you need to add two rules to define a range of possible values: the limiting conditions. For example, you can set the constraint that one of the variables or cells must not be bigger than another variable, or not bigger than a given value. You can also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed).

Once you have finished setting up the rules, you can adjust the argument and the results by clicking the Solve button.

Place the cursor in the formula cell (the cell containing the interest I), and choose Tools > Goal Seek.
On the Goal Seek dialog, the correct cell is already entered in the Formula cell field.
Place the cursor in the Variable cell field. In the sheet, click in the cell that contains the value to be changed, in this example it is the cell with the capital value C.
Enter the desired result of the formula in the Target value field. In this example, the value is 15000. The figure below shows the cells and fields.
Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the result in the cell with the variable value. The result is shown below.

You can use several methods to keep track of changes you or others made to a document.

Use change marks to show added or deleted material and changed formatting. Later, you or another person can review and accept or reject each change.
Make changes to a copy of the document (stored in a different folder, under a different name, or both), then use Calc to compare the files and show the changes.
Save versions that are stored as part of the original file.

Reviewers can leave notes in the document or make comments attached to specific changes.

Preparing a document for review (optional)

When you send a document to someone else to review or edit, you may want to prepare it first so that the editor or reviewer does not have to remember to turn on the revision marks. After you have protected the document, any user must enter the correct password in order to turn off the function or accept or reject changes.

Open the document and make sure that the Edit > Changes > Record menu item has a check mark next to it, indicating that change recording is active.
(Optional) Click Edit > Changes > Protect Records. On the Protect Records dialog, type a password (twice) and click OK.

Identifying copies of spreadsheets

When sharing documents, it is important to keep track of the different copies of the document. This can be done either in the file name or in the file title. If you have not provided a file title in the spreadsheet’s properties, the spreadsheet’s filename is displayed in the title bar. To set the title of the spreadsheet, select File > Properties > Description.

Locking

Cells

You have to protect the sheet first.To protect cells go to Format > Cells > Cell protection.

Macros

A macro is a saved sequence of commands or keystrokes that are stored for later use. An example of a simple macro is one that “types” your address. The OpenOffice.org (OOo) macro language is very flexible, allowing automation of both simple and complex tasks. Macros are especially useful to repeat a task the same way over and over again. Macros can be in many different languages, but the default is BASIC.^[1] This section briefly discusses common problems related to macro programming using Calc.

The following steps create a macro that performs paste special with multiply.

Open a new spreadsheet.
Enter numbers into a sheet.
Select cell A3, which contains the number 3, and copy the value to the clipboard.
Select the range A1:C3.
Use Tools > Macros > Record Macro to start the macro recorder. The Record Macro dialog is displayed with a stop recording button.
Use Edit > Paste Special to open the Paste Special dialog.
Set the operation to Multiply and click OK. The cells are now multiplied by 3.
Click Stop Recording to stop the macro recorder and save the macro.
Select the current document. For this example, the current Calc document is Untitled. Click on the + next to the document to view the contained libraries. Prior to OOo version 3.0, new documents were created with a standard library; this is no longer true. In OOo version 3.0, the standard library is not created until the document is saved, or the library is needed. If desired, create a new library to contain the macro (but this is not necessary).
Click New Module to create a module in the Standard library. If no libraries exist, then the Standard library is automatically created and used.
Click OK to create a module named Module1.
Select the newly created Module1, enter the macro name PasteMultiply and click Save. The created macro is saved in Module1 of the Standard library in the Untitled2 document (see Listing 1).

Listing 1. Paste special with multiply.

 sub PasteMultiply
 
  rem --------------------------------------------------------------
  rem define variables
  dim document   as object
  dim dispatcher as object
  rem --------------------------------------------------------------
  rem get access to the document
  document   <nowiki>=</nowiki> ThisComponent.CurrentController.Frame
  dispatcher <nowiki>=</nowiki> createUnoService("com.sun.star.frame.DispatchHelper")
 
  rem --------------------------------------------------------------
  dim args1(5) as new com.sun.star.beans.PropertyValue
  args1(0).Name <nowiki>=</nowiki> "Flags"
  args1(0).Value <nowiki>=</nowiki> "A"
  args1(1).Name <nowiki>=</nowiki> "FormulaCommand"
  args1(1).Value <nowiki>=</nowiki> 3
  args1(2).Name <nowiki>=</nowiki> "SkipEmptyCells"
  args1(2).Value <nowiki>=</nowiki> false
  args1(3).Name <nowiki>=</nowiki> "Transpose"
  args1(3).Value <nowiki>=</nowiki> false
  args1(4).Name <nowiki>=</nowiki> "AsLink"
  args1(4).Value <nowiki>=</nowiki> false
  args1(5).Name <nowiki>=</nowiki> "MoveMode"
  args1(5).Value <nowiki>=</nowiki> 4
 
  dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())
  end sub

Calc can call macros as Calc functions. Use the following steps to create a simple macro:

Create a new Calc document named CalcTestMacros.ods.
Use Tools > Macros > Organize Macros > OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog. The Macro from box lists available macro library containers. My Macros contains macros that you write or add to OOo. OpenOffice.org Macros contains macros included with OOo and should not be changed. All other library containers are currently open OOo documents.
Click Organizer to open the OpenOffice.org Basic Macro Organizer dialog.
Click the Libraries tab.
Select the document to contain the macro.
Click New to open the New Library dialog.
Enter a descriptive library name (such as AuthorsCalcMacros) and click OK to create the library. The new library name is shown the library list, but the dialog may show only a portion of the name.
Select AuthorsCalcMacros and click Edit to edit the library. OOo automatically creates a module named Module1 and a macro named Main.
Modify the code so that it is the same as that shown in Listing 2. The important addition is the creation of the NumberFive function, which returns the number five. The statement Option Explicit forces all variables to be declared before they are used. If Option Explicit is omitted, variables are automatically defined at first use as type Variant.

Listing 2. Function that returns five.

  REM  *****  BASIC  *****
  Option Explicit
 
  Sub Main
 
  End Sub
 
  Function NumberFive()
    NumberFive = 5
  End Function

Getting Help

Spreadsheets with OpenOffice.org 3.x Calc - OpenOffice.org Wiki

General Spreadsheet help

↑ OpenOffice.org BASIC Programming Guide

[1] OpenOffice.org BASIC Programming Guide

[1]