Do the following:


Selecting areas and multiple areas

The StarOffice cell-related functions, formatting and other commands generally require that cells are first selected and then the desired command is activated. To select several cells, press the mouse button and drag the mouse across the cells, e.g. from the cell in the upper-left of the desired area to the cell in the lower left of the area. The selected cells are displayed in reverse. When you release the mouse button the selection remains intact, and you can now activate a command for these selected cells.

If you would like to select entire rows or columns, click the column or row headers at the edge of the table. The upper-left field at the intersection of the column and row names is the "Select All" field, which you can use to select the entire table. Click on an individual cell to remove selection of multiple cells.

If you want to select an individual line, you first have to drag the selection over this and a neighboring cell and then reduce it again to one cell by dragging it back.


Selecting ranges

Several StarOffice dialogs require you to name a range reference. This reference can either be entered using the keyboard or you can set the cursor in the input field of the dialog and select the desired table range with the mouse. A red frame is drawn around the range, and StarOffice enters its reference in the input field using correct and complete notation.

The reference entered stays highlighted, i.e. it can be changed immediately by selecting a different table area with the mouse. To finish entering the reference and continue editing the input field you must first remove the highlighting in the input field, e.g. by setting the cursor a little further to the right in the input by using the Right Arrow key.


To access this function...




Edit Menu


Menu: Edit - Headers & Footers...

Menu: Edit - Headers & Footers... - Header/Footer tabs

Menu: Edit - Fill

Menu: Edit - Fill - Down

Menu: Edit - Fill - Right

Menu: Edit - Fill - Up

Menu: Edit - Fill - Left

Menu: Edit - Fill - Sheet

Menu: Edit - Fill - Series...

Menu: Edit - Delete Contents...
(Delete) key

Menu: Edit - Delete Cells...

Menu: Edit - Delete Sheet...
Context menu in the sheet tabs

Menu: Edit - Copy/Move Sheet...
Context menu in the sheet tabs

Menu: Edit - Remove Manual Break

Menu: Edit - Remove Manual Break - Row Break

Menu: Edit - Remove Manual Break - Column Break


View Menu


Menu: View - Column & Row Headers

Menu: View - Value Highlighting

Menu: View - Toolbars - Formula Bar

Menu: View - Page Break Preview


Insert Menu


Menu: Insert - Manual Break - Row Break

Menu: Insert - Manual Break - Column Break

Menu: Insert - Cells...
Insert Cells floating toolbar on the Main Toolbar:



Insert cells



Insert Cells Down



Insert Cells Right



Insert Rows



Insert Columns

Menu: Insert - Rows

Menu: Insert - Columns

Menu: Insert - Sheet...

Menu: Insert - Function...
(Command)(Ctrl)(F2) key
Icon on the Formula bar:



Function Autopilot

Function Autopilot - Category: Database

Function Autopilot - Category: Date&Time

Function Autopilot - Category: Financial

Function Autopilot - Category: Information

Function Autopilot - Category: Logical

Function Autopilot - Category: Mathematical

Function Autopilot - Category: Matrix

Function Autopilot - Category: Statistical

Function Autopilot - Category: Text

Function Autopilot - Category: Spreadsheet

Function Autopilot - Category: Add-In

Menu: Insert - Function List

Menu: Insert - Names

Menu: Insert - Names - Define...
(Command)(Ctrl)(F3) key

Menu: Insert - Names - Paste...

Menu: Insert - Names - Create...

Menu: Insert - Names - Labels...


Format Menu


Menu: Format - Cells...

Menu: Format - Cells... - Cell Protection tab

Menu: Format - Row

Menu: Format - Row - Optimal Height...

Menu: Format - Row - Hide
Menu: Format - Column - Hide
Menu: Format - Sheet - Hide

Menu: Format - Row - Show
Menu: Format - Column - Show

Menu: Format - Column

Menu: Format - Column - Optimal Width...
Icons in the object bar:
Double-click the right column separator in the column headers



Optimal Column Width

Menu: Format - Sheet

Menu: Format - Sheet - Rename...

Menu: Format - Sheet - Show

Menu: Format - Merge Cells

Menu: Format - Merge Cells - Define

Menu: Format - Merge Cells - Split

Menu: Format - Page
Double-click the field in the status bar
Icon in the page view bar:



Page Format

Menu: Format - Page - Sheet tab

Menu: Format - Print Range

Menu: Format - Print Range - Set

Menu: Format - Print Range - Add

Menu: Format - Print Range - Delete

Menu: Format - Print Range - Edit...

Menu: Format - Style Catalog...
(Command)(Ctrl)(Y) key

Menu: Format - AutoFormat...
Icon in the main toolbar:



AutoFormat

Menu: Format - Conditional Formatting...


Tools Menu


Menu: Tools - Detective

Menu: Tools - Detective - Trace Precedents
(Shift)(F6) key

Menu: Tools - Detective - Remove Precedents

Menu: Tools - Detective - Trace Dependents
(Shift)(F5) key

Menu: Tools - Detective - Remove Dependents

Menu: Tools - Detective - Remove All Traces

Menu: Tools - Detective - Trace Error

Menu: Tools - Detective - Fill Mode

Menu: Tools - Detective - Mark Invalid Data

Menu: Tools - Detective - Refresh Traces

Menu: Tools - Detective - Auto Refresh

Menu: Tools - Goal Seek...

Menu: Tools - Scenarios...

Menu: Tools - Protect Document

Menu: Tools - Protect Document - Sheet...

Menu: Tools - Protect Document - Document...

Menu: Tools - Cell Contents

Menu: Tools - Cell Contents - Recalculate
(F9) key

Menu: Tools - Cell Contents - Auto Calculate

Menu: Tools - Cell Contents - AutoComplete


Window Menu


Menu: Window - Split

Menu: Window - Freeze


Data Menu


Menu: Data - Define Area...

Menu: Data - Select Area...

Menu: Data - Sort...

Menu: Data - Sort - Sort Criteria tab
Icons in the main toolbar:



Sort Ascending



Sort Descending

Menu: Data - Sort - Options tab

Menu: Data - Filter

Menu: Data - Filter - AutoFilter
Icons in the main toolbar and database bar:



AutoFilter





Menu: Data - Filter - Advanced Filter...

Menu: Data - Filter - Standard Filter... - Options button
Menu: Data - Filter - Advanced Filter... - Options button

Menu: Data - Filter - Remove Filter
Database Bar - Remove Filter/Sort icon



Remove Filter/Sort

Menu: Data - Filter - Hide AutoFilter

Menu: Data - Subtotals...

Menu: Data - Subtotals - 1st, 2nd, 3rd Group tabs

Menu: Data - Subtotals - Options tab

Menu: Data - Validity...

Menu: Data - Validity... - Values tab

Menu: Data - Validity... - Input help tab

Menu: Data - Validity... - Error Alert tab

Menu: Data - Multiple Operations...

Menu: Data - Consolidate...

Menu: Data - Outline

Menu: Data - Outline - Hide Details

Menu: Data - Outline - Show Details

Menu: Data - Outline - Group...
(F12) key
Icons in the main toolbar:



Insert Group

Menu: Data - Outline - Ungroup...
(Command)(Ctrl)(F12) key
Icons in the main toolbar:



Ungroup

Menu: Data - Outline - Auto Outline...

Menu: Data - Outline - Clear Outline...

Menu: Data - DataPilot

Menu: Data - DataPilot - Start...

Menu Data - DataPilot - Start... Select Source dialog. Option Data source registered in StarOffice.

Menu: Data - DataPilot - Start... - Current selection option
Menu Data - DataPilot - Start... Select Source dialog. Option Data source registered in StarOffice - Select Data Source dialog

Menu: Data - DataPilot - Refresh...

Menu: Data - DataPilot - Delete...

Menu: Data - Refresh Area...


Navigator


This command switches the Navigator display on or off. The Navigator is especially useful for working with large documents, as it enables you to jump directly to specific cells or cell areas. The Navigator is a dockable window.

You have various options for displaying the Navigator on your screen. Open a document, go to the main menu and select Edit - Navigator. You can also click the respective icon in the Function bar or press the F5 key. You can position the Navigator's window anywhere in your screen by clicking the Navigator's title bar and then dragging the window to the desired position. You can also dock this window by pressing the Control Crtl key while dragging.

Column

Enter the letter of the column header in this spin box. To move the cursor to the specified column, press Enter.

Row

Enter a row number in this spin box. To move the cursor to the specified row, press Enter.

Data Range

Use this command to select the current data range (depending on the cursor's position).


Data Area


Contents

Click here to hide or show the contents of the Navigator if it is displayed as a floating window.


Contents


Start

Click here to jump to the beginning of the current data area (dependent on the cursor position).


Start


End

Click here to jump to the end of the current data area (dependent on the cursor position).


End


Toggle

Click here to toggle the content view. The selected element in the Navigator becomes the root, and only this element and its subelements will be displayed. Clicking on the button again restores the view of all elements.


Toggle


Scenarios

All existing scenario names are displayed here; double-click an entry to apply that scenario. The result of the selected scenario is displayed in a table. For more information, refer to the menu command Tools - Scenarios.


Scenarios


Drag Mode

Click here to activate the Drag mode. After clicking this icon, you will see a submenu with the following modes:


Drag mode


Insert As Hyperlink

In the Hyperlink mode you can drag and drop a hyperlink from the Navigator into a document.

Insert As Link

In the Link mode you can drag and drop a link from the Navigator into a document. If you use this option, the Drag & Drop mode icon changes.

Insert as Copy

In the Copy mode you can drag and drop a copy from the Navigator into a document. If this option is active, the Drag & Drop mode icon changes.

Existing objects

In this area you can see a list of all existing objects in your document.

Document list

This list displays the names of all open documents. To switch the Navigator display to another open document, click on the document name. After each document name, you will see an indication in brackets whether the document is active or inactive. Select the active document in the Window menu.

Open the context menu of the Navigator by right clicking the Navigator's window. This context menu shows the Display command whose sub menu has the same entries as the Documents List combo box. Select the document's information to be displayed in the Navigator here. Select the Active Window command to update the displayed information as you switch to another document. You also have the Drag Mode command, which enables you to determine if you want to insert a hyperlink, link or copy using Drag&Drop. The Outline Level command enables you to define the outline level to be displayed in the Navigator.


Headers & Footers


Use this command to define and format the headers and footers in your document.

In this dialog you see the tabs for defining headers and footers. If you have specified in the Page Layout for headers and footers that the content on left and right pages should not be the same there will be split tabs for the left and right pages of your document.




Header/Footer


Define or format a header or footer for a page style here. The application allows you to use various measurement units.

Left Area

Enter the text to be displayed on the left side of the header or footer.

Center Area

Enter the text to be displayed on the center of the header or footer

Right Area

Enter the text to be displayed on the right side of the header or footer.

Text Attributes

Use this dialog to assign formats to selected text, or before you enter new text. This dialog is similar to the Format - Cells - Character tabpage. Only the Language list box is not available here.


Text attributes


Filename

Insert a placeholder for the filename in the selected header/footer area, which will be replaced by the name of the file in the header/footer of the current document. A short click inserts the title. By a little longer click you can select in a submenu if to insert a title, file name or path. If you do not assign a title (in File - Properties), the file name will be inserted.


Filename


Sheet Name

Insert a placeholder for the name of the sheet in the selected header/footer area, which will be replaced by the name of the sheet in the header/footer of the actual document.


Sheet Name


Page

Insert a placeholder for the current page number in the selected header/footer area to activate continuous page numbering for a document.


Page


Total Pages

Insert a placeholder for the total pages in the selected header/footer area to display the total number of pages in the document.


Total Pages


Date

Insert a placeholder for the current date in the selected header/footer area. The date will be repeated in the header/footer on each page of the document.


Date


Time

Insert a placeholder for the current time in the selected header/footer area. The time will be repeated in the header/footer on each page of the document.


Time


Help

Click here for additional help regarding this dialog.


Fill


Select this command to automatically fill cell contents.


In addition to the procedures called up in following submenus, the StarOffice Calc context menus have other options for filling the cells.






Spreadsheets....

Rows....

Thus, you can fill the cells using the context menu:


Down


Fills the selected area (minimum of 2 rows) with the contents of the upper area cell.

If a range with only a column is selected, the content of the topmost cell will be copied into all other cells in the selected range. If several columns are selected, the topmost cell in each column will then be copied into the cells located underneath it


Right


Fills the selected area (minimum of 2 columns) with the contents of the first cell, on the left.

If the range with only one line is selected, the content of the cell at the far left will be copied to all other cells in the selected area. If several rows are selected, the cell at the far left in each line will be copied into the cells to the right.


Up


Fills the selected area (minimum of 2 rows) with the contents of the below area cell.

If an area is selected in only one column, the content of the lowest cell will be copied into the other cells of the selected range. If several columns are selected, the lowest cell will be copied into all the cells above it.


Left


Fills the selected area (minimum of 2 columns) with the first cell on the right.

If only rows are selected, the contents of the cells will be copied into the rightmost part of the other cells of the selected range. If several rows are selected, the rightmost cells in each row will be copied into the leftmost parts of the stationary cells.


Fill Sheet


Select the options for transfering spreadsheets or ranges of a certain spreadsheet here.

In contrast to copying an area via the clipboard, you can filter certain information and calculate values. This command is only visible if the document contains at least two sheets, both of which must be selected. To select multiple sheets, click each sheet tab while pressing (Command)(Ctrt) or (Shift).

Filling a Spreadsheet

This dialog is similar to the Paste Contents dialog, where you can find additional tips.


Fill Series


Use the options in this dialog to create a data series. You can define a direction, an increment, a time unit or a series type.


Before you can fill in a series, the cell area that is to be filled in must be selected.


StarOffice can also automatically continue series that have been started based on the assumed rule for forming the series, if you use the option Series... with Autofill

Direction

In this module you can set the direction of automatic creation.

Down

Generate a series from the active cell towards the top of the column, with the specified increment and stop value.

Right

Generate a series of data based on the specified increment and stop value, to be inserted into the cells to the right of the active cell.

Up

Generate a series of data based on the specified increment and stop value, to be inserted into the cells in the column above the active cell.

Left

Generate a series of data based on the specified increment and stop value, to be inserted into the cells to the left of the active cell.

Series type

These radio buttons are used to define the series type, you can choose from the following: Linear, Growth, Date and AutoFill .

Linear

Choose this option to generate a linear series with the specified increment and stop value.

Growth

Click here to generate a growth (geometric) series with the specified increment and stop value.

Date

Choose this option to generate a series of dates with the specified increment and stop value.

AutoFill

You can generate a series of data directly from the values of the selected cells using the AutoFill function. The AutoFill function considers the customized lists. If you enter, for example, Jan in the first cell, the corresponding defined list (Tools - Options - General - Custom lists) will be automatically filled.

Autofill attempts to continue the series of values according to the established pattern. For example, the series 1, 3, 5 would automatically be continued as 7, 9, 11, 13 etc. The series 1, 3, 6 on the other hand, would be continued with 2, 4, 7, 3, 5, 8, 4, 6, 9 etc. Date and time series are continued in a similar way; after 01.01.97 and 15.01.97, the program would continue counting at 14-day intervals.

Unit of time

This area lets you select the time units Day, Weekday, Month and Year. The area is active only if the Date option has been selected in the Type of Series area.

Day

With Date series type, you can generate a series increasing by days (seven days including weekends).

Weekday

With Date series type, you can generate a series increasing by weekdays (five days excluding weekends).

Month

With Date series type, you can generate a series from the names of the months or abbreviations.

Year

With Date series type, you can generate a series increasing by years.

Start Value

Specify a start value for the series of data here. The start value can be entered as a number, date or time.

End Value

Specify an end value for the series of data here. The end value can be entered as a number, date or time.

Increment

In the mathematics, increment means an amount increased each time in one value. Enter a step value (increment) for the series of data that you want to generate. An entry is only possible if you selected a series type before: linear, growth or date.


Delete Contents


Specify the contents of a cell or cell range to be deleted. Before deleting, you should first select the cell or range. Only the selected cells or the current cell can be deleted. If you selected several spreadsheets, this action will only affect the current table.


If the cell cursor is activated in the table, you can also display this dialog by pressing the (Del) key.
Pressing the Delete key (above the Enter key) deletes the contents without calling a dialog or changing the formats.
The Cut icon on the Function Bar deletes the contents and the formats without a dialog.


Selection

This area gives you the different options for deleting contents.

Delete All

If you check this box, all contents of the selected cell area will be deleted.

Character Strings

If you check this box, only strings (no numbers, dates, etc.) will be deleted. Formats and formulas are kept.

Numbers

Check this box to delete only numbers. Formats and formulas are kept.

Date & Time

Check this box to delete only date and time values. Formats, text, numbers and formulas will be kept.

Formulas

Check this box to delete only formulas. Text, numbers, formats, date and time are kept.

Notes

Check this box to delete only notes. All other elements are kept.

Formats

Check this box to delete only applied format attributes of cells. All the cell contents will be kept.

Objects

Check this box to delete only objects in cells. All the cell contents will be kept.


Delete Cells


Use this dialog to completely delete selected cells, columns or rows. The cells below or to the right of the deleted cells will move to the deleted space. Note that the next time you call up this dialog, your previous selected option will be activated.

Selection

Specify here how to shift the remaining cells that have not been deleted.

Shift cells up

Click here if you want the remaining cells to be shifted up.

Shift cells left

Click here if you want the remaining cells (to the right) to be shifted left.

Delete entire row

Click here to delete the entire row (at least one cell must be selected).

Delete entire column

Click here to delete the entire column where only one cell has been selected.


Delete Sheet


Deletes the entire sheet (after a confirmation dialog).


N.B.: You cannot delete a spreadsheet if changes to the current document have been saved or if a menu command under Edit-Changes-Record is active.


Yes

Click this button to permanently delete the active sheet.

No

Click this button if you do not want to delete the current sheet.


Copy/Move Sheet...


In this dialog you can specify whether to move or copy the sheet. You can move or copy your spreadsheet within the document or to another document.

To document

Select the document where you want to copy or move the selected sheet. Select the New Document option if you want to create a new document for the sheet to be shifted or copied.

Insert before

Here you will see a list of all sheets in the document. The current sheet will be moved or copied before the selected sheet. To position the sheet after all other sheets, choose the option Move to end position.

Copy

This option specifies if a sheet should be moved or copied. If you select this option the current sheet will be copied; if not the sheet will be moved. By default, sheets are moved.


Manually Deleting Breaks


Here is the options submenu for manually deleting row and column breaks.




Row break


Removes the manual row break above the active cell. Set the cursor in a cell directly below of the row break indicated by a horizontal line. Activate this command. The manual row break will be deleted.


Column Break


Removes a manual column break at the left of the active cell. Place the cursor on the cell at the right of the column break (indicated by a vertical line). Call this command and the column break will be immediately removed.


Column & Row Headers


Select this command to show or hide the column and row headers. To hide the column and row headers deselect this menu entry.

You can also set the view of the column/row headers under Tools - Options - Table Document... - Layout.


Value Highlighting


Select this command to highlight certain values in your sheet. To clear the highlighting, simply deactivate this menu point.

Text cells are formatted in black, cells containing numbers are blue, and cells containing other information (formulas, logical values, dates, etc.) are green.


When this function is active, any colors you define in the document will not be displayed. However, when you deactivate the command, the colors will appear.



Formula Bar


Check this menu item to show or hide the Formula bar. This is a very important tool to entry and edit formulas and for your work with spreadsheets. To hide the formula bar, you just have to click the corresponding menu entry.

You can also edit the contents of the current cell while the formula bar is hidden. To do this, change to Edit mode by using function key (F2). Edit the contents or immediately replace them (without having to select them first) with new contents. Then, press the Enter key to accept the changes, or press the (Esc) key to reject them. This also exits you from the Edit mode.


Page Break Preview


If this menu item is selected, the page breaks within the sheet will be displayed.

In the page break preview, the following settings apply:

You can use your mouse to shift the indicated print areas and page breaks.

In the page break preview, the context menu provides the most important functions for editing the page breaks, including the following options:

Remove All Manual Breaks

Deletes all manual breaks in the current sheet.

Add Print Range

Adds the selected cells to the existing print areas of the current sheet.


Manual Break


Select which breaks are to be inserted in the current sheet. The sheet areas after a row or column break will be broken to the following page. For further information about how to remove the break again refer to the Edit - Delete Manual Break menu.




Row Break


Use this command to insert a manual row break above the active cell. You recognize a row break by a horizontal line in the sheet.


Column Break


Choose this command to insert a manual column break to the left of the active cell. You recognize a manual column break by a vertical line in the sheet.


Insert Cells


With this dialog you can insert cells or a selected area of cells at the position of the active cell. The program recognizes the inserting option. If you open the dialog again, this option will be checked. For further information about how to delete cells, refer to the Edit - Delete Cellsmenu.

Selection

Under Selection, you can specify how to insert the cells.

Shift cells down

Click here if you want the cells to be shifted down.

Shift cells right

Click here if you want the cells to be shifted to the right.

Entire row

Choose this option to insert an entire row.

Entire column

Choose this option to insert an entire column. The number of the columns to be inserted is determined by the selected number of columns. The contents of the original columns are shifted to to right when you insert new columns.



Rows


Choose this command to insert a new row above the active cell. The rows to be inserted depend on the number of selected rows when you activate this command. The existing rows are shifted down.


Columns


Use this command to insert a new column to the left of the active cell. The columns to be inserted depend on the number of selected columns when you activate this command. The existing columns are shifted to the right.


Insert Sheet


Define here the options for the inserting of a new sheet. You can create a new sheet, or insert an existing sheet from a file.

Position

In this area you can specify the position at which a table will be inserted into your document.

Before current sheet

Click here to insert a new sheet directly before the current sheet.

After current sheet

Click here to insert a new sheet directly after the current sheet.

Table

In this area you can specify whether new or already existing tables will be inserted into your document.

Create new

Select this option to create a new sheet, and to assign a name for it.

No. of sheets

In this spin box you can specify the number of sheets to be inserted.

Name

Enter a sheet name, which may contain both letters and numbers.

Create from file

Select this option to insert a new sheet from an existing file into the active sheet.

Browse

Click this button to open a dialog where you can select a file. This dialog is similar to the Open dialog in the File menu.

Selected sheets

If you selected a file by using the Browse button, the sheets of the selected file are displayed in the list box. The file path is displayed below this box. Select the sheet to be inserted in the document from this list box.

Link

Click here to link the current document with the selected sheet.


AutoPilot: Functions


Use the AutoPilot for Functions to create and insert interactive functions into your document. The AutoPilot: Functions allows you to search for a function, enter the required arguments and calculate the result of the formula. Before you open the AutoPilot: Function, select a cell or range of cells in which you want the result to appear.

You will find two tabs in the AutoPilot for Functions: the Functions tab is used to set a formula, and the Structure tab to check the layout of the formula.

Functions Tab

After calling the AutoPilot for Functions, the AutoPilot: Functions dialog will appear containing the Functions tab. Select and accept a category (for example, Mathematical) and a function (for example, Rounddown) to activate a further input area.

List of Categories and Functions

Category

A list of all categories is displayed in this combo box. Select a category (e.g. Mathematical) to view a list of corresponding functions. Select "All" to view all functions in alphabetical order (independent from the category). In addition, you can select "Last used" to view all the functions already used in the document.

Function

In this list you can see the names of the different functions and additional information about the selected function. Double-click to select a function.By a simple click, the AutoPilot: Functions displays a short function description until you double-click the function to be inserted into the formula field.

Matrix

If you check this box, a matrix is inserted in the sheet along with the selected function. Each cell in the matrix contains the formula, not as a copy, but as a shared formula for all matrix cells.

The Matrix check box has the same function as the key sequence CommandCtrl + Shift + Enter for entering and confirming a formula in the table: The formula is inserted as a matrix formula designated by two braces (i.e. { }).

Contiguous cell areas form a matrix and matrix formulas process the entire cell areas with only a single formula. It is used mainly for functions of the Matrix category.


The maximum size of a matrix area is 128 x 128 cells; in the 16-bit Windows versions the area is 64 x 64 cells.


Arguments Input Field

In Step 2 of 2 of the AutoPilot: Functions, you can enter the arguments for the selected function. To select a cell reference as an argument, click on the cell or, keeping the mouse button pressed down, select a range of cells in the spreadsheet. Numerical and other values and references can also be entered directly in the relevant fields of this dialog. Once you've defined the function by entering all its arguments, click End to insert the result in the spreadsheet.

This field displays additional information about the selected function and the text fields for the argument input. An important information is whether the input is required or optional for the calculation.

Function Result

As soon as you've entered the function arguments, the calculation will be executed. This preview shows you if the function can be calculated with the arguments you've entered. If the arguments result in an error, an error code will be displayed.

The description and the number of buttons depend on the selected function for the calculation. The required entries are displayed in bold before each button.

f(x) (depending on selected function)

Clicking on one of these buttons will bring you a level deeper in the AutoPilot: Functions. You can then enter a function rather than a value or reference in the field next to the button.

Argument/Parameter/Cell Reference (depending on selected function)

The number of visible text fields depends on the selected function for the calculation. You can enter arguments directly in the relevant fields, or you may click on a cell to insert the content as a cell reference in the function. The cell coordinates appear in the first list box and the cell value in the other.

Result

The result of the calculation appears in this field before you confirm with OK.

Formula

This field displays the created formula. You can also enter information directly in the field or create the formula with the input help of the AutoPilot: Functions.

<<Back

Click this button to view the elements of the formula. If the formula consists of two functions, you can go back two steps. This is especially useful if you want to replace the last inserted function in a formula with another. This button is used in combination with the Next button.



To select a single function from a complex formula consisting of several functions, simply double-click the function in the formula window. The desired function will be highlighted.


Next>>

This button toggles in the formula window to the right via the formula components and restores the <<Back button to the former status. This button allows you too to assign functions to the formula. Select a function and click the button. The selection will be then displayed in the formula window. For example: to replace the two last entries by a new function, just double-click the <<Back button. The selected formula area can be then replaced by a new function if you click the Next>> button.



You can also double-click a function in the selection window to transfer it to your formula in the formula window.


Cancel

Click this button to exit the Function Autopilot, without calculating any entered functions.

End

Click here to close the AutoPilot for Functions and calculate the selected function. The result of the function will be entered in the selected cell of the active sheet.

List of Categories and Functions

Structure Tab

This tab enables you to view the hierarchical structure of the function. This can be particularly useful when searching for a mistake.


If you start the AutoPilot: Functions when the cell cursor is positioned in a cell containing a function, the Structure tab will open automatically showing you the structure of the current formula. Use this feature as a "Function Inspector" for checking the accuracy of a formula.


Structure

Displays the function with all the nested files structured hierarchically. The type of the display is similar to the Explorer window: each formula is described by the corresponding entry; this one on its turn containing subordinated entries for the corresponding arguments. You can open or close the formula entries by clicking the + or - signs in order to display or hide the arguments.



The individual arguments are displayed with a blue point if they have been entered correctly. A red point indicates that the data type is incorrect. For example if you enter text as the argument for the SUM function, the argument will be highlighted in red, since this function permits only numbers as arguments.



Categories and Functions


This section describes the StarOffice Calc functions. The various StarOffice Calc functions in the Function AutoPilot are summarized and organized by categories.

In addition, you can choose among various operators.


During data exchange with earlier versions of StarOffice, please see the Notes about changes in the program's arithmetic functionality.


Database

Date & Time

Financial

Information

Logical

Mathematical

Matrix

Statistical

Spreadsheet

Text

Add In


Category Database


This area includes the StarOffice Calc functions referring to data merged in row-by-row records. These include the following functions:DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DAVERAGE, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP. They are described below in the order indicated. For a comparison of the functions described, call page 2 of the AutoPilot function.


The name of the Database category may be confused with the StarOffice Base database that displays your address database, among other information. However, there is no connection between the StarOffice Base database and the Database category in StarOffice Calc.


Examine the sample table, which lists the children in area A1:E10 who are invited to Joe's birthday party. The following information is shown for each child: name in column A, grade in column B, then age in years, distance to school in meters, and weight in kilograms.


A

B

C

D

E

1

Name

Grade

Age

Distance to School

Weight

2

Andy

3

9

150

40

3

Betty

4

10

1000

42

4

Charles

3

10

300

51

5

Daniel

5

11

1200

48

6

Eva

2

8

650

33

7

Frank

2

7

300

42

8

Greta

1

7

200

36

9

Harry

3

9

1200

44

10

Irene

2

8

1000

42

11






12






13

Name

Grade

Age

Distance to School

Weight

14




>600


15






16

DCOUNT

5





The formula in cell B16 is =DCOUNT(A1:E10;A1:E10;A13:E14)

The parameters of all database functions have the following meaning:

Database is the cell area defining the database.

Database Field indicates which database field is used for further reference if such a reference is possible for the database function. To reference a column by a name in the column header put the name in quotation marks.

Search Criteria is the cell area containing search criteria. If you write several criteria in one row they are connected by AND. If you write the criteria in different rows they are connected by OR. Empty cells in the search criteria area will be ignored.

DCOUNT

DCOUNT counts the number of rows (records) that match the specified search conditions.

Syntax

DCOUNT(Database; Database Field; Search Criteria)

For the median parameter you can enter the entire database or the number zero, but the parameter cannot be empty.

Example

In the example above, we want to know how many children have to travel more than 600 meters to school. The result is to be stored in cell B16. Set the cursor in cell B16. Enter the formula =DCOUNT(A1:E10;A1:E10;A13:E14) in B16. You can also enter the formula =DCOUNT(A1:E10;0;A13:E14) (see syntax note above). You can also use the AutoPilot function to help you with the entry.

Database is the area of the data to be evaluated, including its headers: in this case A1:E10. Database Field specifies the column for the search criteria: in this case, Distance to School (meters). Search Criteria is the area where you can enter the search parameters: in this case, A13:E14.

For example, to learn how many children in second grade are over 7 years of age, delete the entry >600 in cell D14 and enter "2" in cell B14 under Grade, and enter >7 in cell C14 to the right. The result is 2. Two children are in second grade and are over 7 years of age. Because both criteria are in the same row they are connected by AND.

COUNTBLANK, COUNTIF.

DCOUNT2

DCOUNT2 counts the number of rows (records) that match the specified search conditions, and contain numeric or alphanumeric values.

Syntax

DCOUNT2(Database; Database Field; Search Criteria)

Example

In the example above, you can search for the number of children whose name starts with E or A higher letter. Edit the formula in B16 by adding the missing 2 to the function name DCOUNT. Delete the old search criteria and enter >=E under Name in field A14. The result is 5. If you now delete all number values for Greta in row 8, the result changes to 4. Row 8 is no longer included in the count because it does not contain any values (the name Greta is text, not a value).

COUNTBLANK, COUNTIF.

DGET

DGET returns the contents of a cell which matches the specified search criteria in a database field. In case of an error, the function returns #VALUE!, if there was no row founded, or Err502, if there was more than one cell founded.

Syntax

DGET(Database; Database Field; Search Criteria)

Example

In the example above we want to learn which grade contains a child whose name we entered under Name in cell A14. The formula is again entered in cell B16 and differs slightly from the earlier examples because we can enter only one column (one database field) for Database Field. Enter the following formula:

=DGET(A1:E10;Grade;A13:E14)

Now enter the name Frank in A14, and you will see the result 2. Frank is in second grade. You can also enter "Age" instead of "Grade" and will get Frank's age.

Or, in row 14, enter a value in cell C14 only (i.e. 11) and delete the other entries in this row. Edit the formula in B16 as follows:

=DGET(A1:E10;"Name";A13:E14)

Instead of the grade, the name is searched. You will see the answer immediately: Daniel is the only child age 11.

DMAX

DMAX returns the maximum content of a cell (field) in a database (all records) that matches the specified search conditions.

Syntax

DBMAX(Database; Database Field; Search Criteria)

Example

How much does the heaviest child in each grade weigh? Enter the following formula in B16:

=DBMAX(A1:E10;"Weight";A13:E14)

In row 14, under Grade, enter 1, 2, 3, etc. in this order. For each instance, you will see the weight of the heaviest child in each grade.

DMIN

DMIN returns the minimum content of a cell (field) in a database (all records) that matches the specified search criteria.

Syntax

DBMIN(Database; Database Field; Search Criteria)

Example

What is the shortest distance to school for the children in each grade? Enter the following formula in B16:

=DBMIN(A1:E10;"Distance to School";A13:E14)

In row 14, under Grade, enter 1, 2, 3, etc. in this order. For each instance, you will see as a result the distance to school for the child with the shortest distance to school in this grade.

DAVERAGE

DAVERAGE returns the average of the values of all cells (fields) in all rows (database records) that match the specified search criteria.

Syntax

DAVERAGE(Database; Database Field; Search Criteria)

Example

What is the average weight of all children of the same age? Enter the following formula in B16:

=DAVERAGE(A1:E10;"Weight";A13:E14)

In row 14, under Age, enter 7, 8, 9, etc. in this order. In each instance, you will see as a result the average weight of all children of this age.

DPRODUCT

DPRODUCT multiplies the values of all cells in a database field in all rows (records), that match the specified search criteria.

Syntax

DPRODUCT(Database; Database Field; Search Criteria)

Example

For the birthday party example above, there is no meaningful application of this function.

DSTDEV

DSTDEV estimates the standard deviation of a population based on a sample, using the numbers in a column in a database that match conditions you specify. The records are treated as a sample of data.

Syntax

DSTDEV(Database; Database Field; Search Criteria)

Example

What is the standard deviation of the weight for all children of the same age? Enter the following formula in B16:

=DSTDEV(A1:E10;"Weight";A13:E14)

In row 14, under Age, enter 7, 8, 9, etc. in this order. In each instance, you will see the standard deviation of the weight for all children of this age.

DSTDEV

DSTDEVP calculates the standard deviation of a population based on all cells of a data range which match the search criteria.The records are treated as a sample of data.

Syntax

DSTDEVP(Database; Database Field; Search Criteria)

Example

What is the standard deviation of the weight for all children of the same age at Joe's birthday party? Enter the following formula in B16:

=DSTDEVP(A1:E10;"Weight";A13:E14)

In row 14, under Age, enter 7, 8, 9, etc. in this order. For each instance, you will see as a result the standard deviation of the weight for all children of this age whose weight we checked.

DSUM

DSUM adds the sum of all cells in a database field in all rows (records) that match the specified search criteria.

Syntax

DSUM(Database; Database Field; Search Criteria)

Example

How long is the combined distance to school of all children at Joe's birthday party who are in second grade? Enter the following formula in B16:

=DSUM(A1:E10;"Way to School";A13:E14)

In row 14, under Grade, enter "2". As a result, you will see the sum of the distances to school of all the children who are in second grade, i.e. 1950.

SUMIF.

DVAR

DVAR estimates the variance of all values in the cells of a database field in all records which match the specified search criteria. The records are treated as a sample of data.

Syntax

DVAR(Database; Database Field; Search Criteria)

Example

What is the variance of the weight of all children of the same age? Enter the following formula in B16:

=DVAR(A1:E10;"Weight";A13:E14)

In row 14, under Age, enter 7, 8, 9, etc. in this order. For each instance, you will see as a result the variance of the weight values for all children of this age.

DVARP

DVARP calculates the variance of all values in the cells of a database field in all records which match the specified search criteria. The records are treated as a sample of data.

Syntax

DVARP(Database; Database Field; Search Criteria)

Example

What is the variance of the weight for all children of the same age at Joe's birthday party? Enter the following formula in B16:

=DVARP(A1:E10;"Weight";A13:E14)

In row 14, under Age, enter 7, 8, 9, etc. in this order. For each instance, you will see as a result the variance of the weight values for all children of this age attending Joe's birthday party.


Category: Date & Time


The StarOffice Calc functions are contained here. Use these functions for inserting and editing date and time. These are: DATE, DATVALUE, TODAY, YEAR, NOW, WEEKNUM, MINUTE, MONTH, EASTERSUNDAY, SECOND, HOUR, DAY, DAYS, DAYS360, WEEKDAY, TIME and TIMEVALUE. In the following, they will be described. Call the 2nd. page of the Function AutoPilot for comparing the described function.


StarOffice internally handles a date/time value as a numerical value. If you assign the numbering format "Number" to a date/time value, 01-01-2000 12:00 Noon for example, will be converted to 36526,50. The value preceding the decimal point corresponds to the date; the value following the decimal point corresponds to the time. If you are confronted with these type of numerical date or time representations unintentionally, make sure to set up the correct number format (date or time). To do this, highlight the cell containing the date/time value, call its context menu and select the Format Cells... command. The Numbers tab contains the functions for defining the number format.


DATE

This function converts a date as year, month, or day to an internal number and displays it in the cell. The prescribed cell format containing the DATE function is the date formatting, but you can format the cells, for example, with the 0 number format which displays the internal number of date as a number.

Syntax

DATE(year; month; day)

Year is an integer between 1600 and 3000, between 0 and 29 (-> 2000 to 2029) or 30 and 99 (-> 1930 - 1999).

The 29/30 limit on entry of double-digit date numbers is valid for new documents only. The old 18/19 rule continues to apply if you load documents from older StarOffice versions. Choose Tools - Options... - General - Other to set the maximum double-digit year number that will be detected as 20xx.

Month is an integer between 1 and 12 indicating the number of the month.

Day is a number between 1 and 31 indicating the day of the month.

If the values for month and day are higher, they are carried over to the next digit. If you enter =DATE(00;12;31) the result will be "31.12.2000." If, on the other hand, you enter =DATE(00;13;31) the result will be "31.01.2001."

As arguments dates can be entered directly in the DATE function, or range references can be entered.

Example

DATE(00;1;1) yields 01/01/2000

DATEVALUE, TODAY, YEAR, NOW, MONTH, DAY, TIMEVALUE.

DATEVALUE

DATEVALUE returns the serial number of the date represented by date_text (in quotes); you can use this function to convert a date represented by text to a serial date number.

The internal number, which is indicated as a natural number, is a result of the date system used by StarOffice to calculate dates.

Syntax

DATEVALUE(Text)

Text is a valid date expression and must be entered in quotation marks.

Example

DATEVALUE(6.1. 99) yields 36312

TODAY, NOW, TIMEVALUE.

TODAY

Returns the serial number of the current date. The serial number is the date-time code used for date and time calculations. It will be updated when reopening the document or if you perform modifications.

Syntax

TODAY()

Today is a function without parameters.

Example

TODAY() returns 2.02.2000 (in any case, the actual date from your computer's system time).

DATE, NOW, DAY.

YEAR

Returns the year corresponding to the internal serial number of the year.

Syntax

YEAR(number)

Number is the internal date value for which to return the year.

Examples

Year(1) returns 1899

YEAR(2) returns 1900

YEAR(33333,33) returns 1991

TODAY, NOW, MINUTE, MONTH, SECOND, HOUR, DAY, WEEKDAY.

NOW

Returns the serial number of the current date and time (the current system setting). The value is updated when you recalculate the document or each time a cell value is modified.

Syntax

NOW()

Example

NOW () returns 2.02.2000 17:38.

DATE, YEAR, MINUTE, MONTH, HOUR, DAY, WEEKDAY.

WEEKNUM

WEEKNUM calculates the number of the week in the year corresponding to a given internal date.

Syntax

WEEKNUM(value; mode)

Value is the internal serial date number.

Mode determines the beginning of the week and the type of calculation.


1 = Sunday


2 = Monday


Examples

WEEKNUM(1.1.95;2) returns 52 (if the week starts on Monday, the following Sunday belongs to the last week number of the previous year).

WEEKNUM(1.1.95;1) returns 1 (1.1.95 was a Sunday)

MINUTE

MINUTE calculates the minute for an internal time value The minute is returned as a number between 0 and 59.

Syntax

MINUTE(serial_number)

Serial_number is a decimal number where the number of the minute is to be returned.

Examples

MINUTE(8.999) returns 58

MINUTE(8.9999) returns 59

MINUTE(NOW()) returns the current minute value.

YEAR, NOW, MONTH, SECOND, HOUR, DAY, WEEKDAY.

MONTH

Returns the month corresponding to a given serial date. The month is returned as an integer, ranging from 1 (Jan.) to 12 (Dec.).

Syntax

MONTH(serial_number)

Number is a time value and a decimal, for which the month must be determined.

Examples

MONTH(NOW()) returns the current month

MONTH (C4) returns 7 if contents of C4 = 07.07.2000.

YEAR, NOW, MINUTE, HOUR, DAY, DAY OF THE WEEK.

EASTERSUNDAY

Returns the date of Easter Sunday for the entered year. Year is an integer between 1583 and 9956 or 0 and 99 (19xx or 20xx depending on the defined option). The function returns the internal date number for the Easter Sunday of the entered year. You can also easily calculate other holidays by simple addition with this date.

Easter Monday = EASTER SUNDAY() + 1
Good Friday = EASTER SUNDAY() - 2
Pentecost Sunday = EASTER SUNDAY() + 49
Pentecost Monday = EASTER SUNDAY() + 50

Example

EASTERSUNDAY(2000) returns 23rd. April 2000

SECOND

Returns the second corresponding to a given serial time. The second is given as an integer in the range 0 (zero) to 59.

Syntax

SECOND(Number)

Number is a time value and a decimal, for which the second must be determined.

Examples

SECOND(NOW()) returns the current second

SECOND(C4) returns 17 if contents of C4 = 12:20:17.

DATE, YEAR, NOW, MINUTE, MONTH, HOUR, DAY, DAY OF THE WEEK.

HOUR

Returns the hour corresponding to a given serial time. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.) .

Syntax

HOUR(Number)

Number is a time value and a decimal, for which the hour must be determined.

Examples

HOUR(NOW()) returns the current hour

HOUR(C4) returns 17 if the contents of C4 = 17:20:00.

YEAR, NOW, MINUTE, MONTH, DAY, DAY OF THE WEEK.

DAY

Returns the day of the month corresponding to a serial date. The day is given as an integer ranging from 1 to 31. You can also enter a negative date/time value.

Syntax

DAY(Number)

Number is a time value and a decimal, for which the day must be determined.

Examples

DAY(1) returns 31 (because StarOffice starts counting at 31.12.1899 at zero)

DAY(NOW()) returns the current day (today).

DAY(C4) returns 5 if the contents of C4 = 5.8.1901.

TODAY, YEAR, NOW, MINUTE, MONTH, SECOND, HOUR, DAY OF THE WEEK.

DAYS

Calculates the difference between two date values. The result is an integer representing the number of days.

Syntax

DAYS(Date2;Date1)

Date1 is the later date, Date 2 is the earlier date. If you enter the date values in reverse order the function will return a negative number.

Examples

DAYS(10; 1) returns 9

DAYS(1.1.2010; NOW()) returns the number of days from today until January 1, 2010.

DAYS(10.10.1990; 10.10.1980) returns 3652.

DAYS360

Returns the number of days between two dates based on a standard 360-day year. The result is an integer.

Syntax

DAYS360(Date1;Date2;Type)

Date1 is the later date, Date2 the earlier date. If you enter the date values in reverse order the function will return a negative number.

The optional Type parameter determines the type of difference calculation. If Type = 0 or if the parameter is missing, the US method (NASD, National Association of Securities Dealers) is used. If Type <> 0, the European method is used. For more information on DAYS360, look here.

Examples

DAYS360(10, 1) returns 9

DAYS360(1.1.2000; NOW()) returns the number of interest days from the first day of the year until today.

DAY

WEEKDAY

Returns the day of the week corresponding to a serial date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday) if no type or return_type = 1 is specified. If return_type=2, numbering begins at Monday=1; and if return_type=3 numbering begins at Monday=0.

Syntax

DAY OF THE WEEK(Number; Type)

Number is a date value and a decimal, for which the day of the week must be determined.

Type determines the type of calculation. For Type =1 the days of the week starting with Sunday are counted inclusively (this is the default if the Type parameter is missing). For Type =2 the days of the week are counted starting with Monday =1 inclusively. For Type = 3 the days of the week are counted starting with Monday = 0.


These values are only valid for the standard date format, which you can select under Tools - Options - Table document... - Calculate.


Examples

DAY OF THE WEEK(24.7.96) returns 4 (since the Type parameter is missing the standard count is used. The standard count starts with Sunday as day number 1. 24.7.96 is a Wednesday and therefore number 4).

DAY OF THE WEEK(24.7.96; 2) returns 3 (the Type parameter is 2, therefore Monday is day number 1. 24.7.96 is a Wednesday and therefore number 3).

DAY OF THE WEEK(24.7.96; 1) returns 4 (the Type parameter is 1, therefore Sunday is day number 1. 24.7.96 is a Wednesday and therefore number 4).

DAY OF THE WEEK(NOW()) returns the number of the current day (today).

To obtain a function indicating whether a day in A1 is a Business day, use the IF and DAY OF THE WEEK functions as follows: IF(DAY OF THE WEEK(A1;2)<6;"Business day";"Weekend")

NOW, TAG, TEXT, TODAY.

TIME

Returns the serial number of a particular time value in hours, minutes and seconds. This function can be used to convert a time based on these three elements to a decimal time value.

Syntax

TIME(Hour; Minute; Second)

Hour must be set using an integer.

Minute must be set using an integer.

Second must be set using an integer.

Examples

TIME(0;0;0) returns 00:00:00

TIME(100;20;4) returns 04:20:04

HOUR, MINUTE, NOW, SECOND.

TIMEVALUE

Returns the serial number of the time represented by a text string enclosed by quotes.

The internal number indicated as a decimal results from the date system, which is used by StarOffice to calculate dates.

Syntax

TIMEVALUE(Text)

Text is a valid time expression and must be entered in quotation marks.

Examples

TIME VALUE(4pm) returns 0.67 (if time format HH:MM:SS is used you will see 16:00:00)

TIME VALUE(12:00) returns 0.5 (12:00 Noon = half a day).

DATEVALUE, HOUR, MINUTE, NOW, SECOND, TIME.


Category: Financial


In this category, you will find the financial calculation functions of StarOffice Calc. These include the following functions: PV, SYD, EFFECTIVE, DB, DB2, IRR, PPMT, CUMPRINC, CUMIPMT, DURATION, SLN, NPV, NOMINAL, PMT, VDB, ZGZ, RATE, IPMT, FV and NPER.

PV

Returns the present value of an investment, which is the total amount that a series of future payments is worth now.

You can use these functions to calculate how much money you must invest at a fixed rate today to receive regular payments for a certain number of periods (annuity). You also have the option of entering how much money you want to have left over at the end of these periods. You can also specify if the paid amount should be paid at the beginning or end of each period.

You can enter these values as numbers, expressions or references. For example, if you receive an annual interest rate of 8 % but you want to enter the month as the period, enter 8%/12 in the Rate field. StarOffice Calc automatically calculates the correct factor.

Syntax

PV(Rate; DPER; PMT; FV; F)

Rate defines the interest rate per period.

DPER is the total number of periods (payment period).

PMT is the regular payment made per period.

FV (optional) defines the future value to be left over after payment of the final installment.

F (optional) is the due date for payments. F = 1 means indicates a due date at the end of the period.

Example

What is the cash value of an investment if $500 are paid monthly and the annual interest rate is 8%? The payment period totals 48 months and $20,000 should be left over at the end of the payment period.

PV(8%/12;48;500;20000) = $ -35,019.37. Under the conditions above, you must deposit $35,019.37 today if you want to have $20,000 left over at the end and you receive $500 per month for 48 months. Cross-checking shows that 48 * $500 + $20,000 = $44,000. The difference between this amount and the $35,000 deposited represent the interest paid.

If you do not enter these values directly but rather as a reference in the formula, you can conveniently let the system calculate any number of "If-then" scenarios. Please note that the references to constants must be defined as absolute references. Examples for this type of application can be found in the depreciation functions.

PPMT, PMT, RATE, IPMT, FV, NPER.

SYD

Returns the sum-of-years' digits depreciation of an asset for a specified period.

Use this function to calculate the depreciation amount for a period during the total depreciation period of an object. The digital depreciation reduces the depreciation amount from period to period by a fixed amount

Syntax

SYD(COST;SALVAGE;LIFE;NPER)

COST is the initial cost of an asset.

SALVAGE is the value of an asset at the end of the depreciation.

LIFE is the depreciation period determining the number of periods in the depreciation of the asset.

NPER is the period for which to calculate the depreciation.

Example

A video system with an initial cost of $50,000 is to be depreciated annually for the next 5 years. The asset value at the end of the depreciation is to be $10,000. You want to calculate depreciation for the first year.

COST(50000; 5; 10000; 1) = $13,333.33. The depreciation amount after one year is $13,333.33.

It is best to define a depreciation schedule so that you can easily see the depreciation rates per depreciation period. If you enter the different depreciation formulas in StarOffice Calc side-by-side you can also see which form of depreciation is best in this case. Enter the schedule as follows:


A

B

C

D

E

1

Initial Cost

Salvage Value

Periods

Period

Deprec. SYD

2

$50,000

$10,000

5

1

$13,333.33

3




2

$10,666.67

4




3

$8,000.00

5




4

$5,333.33

6




5

$2,666.67

7




6

$0.00

8




7

-

9




8

-

10




9

-

11




10

-

12






13

>0



Total

$40,000.00


The formula in E2 is as follows:

=SYD($A$2;$B$2;$C$2;D2)

This formula is duplicated in columns E to E10 (highlight E2, then drag down the lower right corner using the mouse).

Cell E13 contains the formula totaling all depreciation amounts for checking. It uses the SUMIF function because the negative values in E8:E11 must not be considered. The condition >0 is contained in cell A13. E13 contains the following formula:

=SUMIF(E2:E11;A13)

You can now view the depreciation over 10 years, or at a value of $1 at the end of the depreciation period, or you can enter other initial cost values, etc.

DB, SLN, VDB.

EFFECTIVE

Returns the effective annual interest rate, based on the nominal annual interest rate.

Since the nominal interest rate refers to a interest due date at the end of the calculation period but interest rates are often due monthly, quarterly, or at a different date prior to the end of the calculation period, i.e. are paid in advance, the effective rate increases with the number of partial interest payments.

Syntax

EFFECTIVE(NOM;P)

NOM is the nominal interest.

P is the number of payment periods per year.

Example

If the annual nominal interest rate is 9.75% and four interest calculation periods are defined, what is the actual interest rate (effective rate)?

EFFECTIVE(9.75%;4) = 10.11% The annual effective rate is therefore 10.11%.

NOMINAL.

DDB

Returns the depreciation of an asset for a specified period using the double-declining balance method.

This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the depreciation already deducted from the initial cost. This form of depreciation is typically used for assets that have the highest loss of value immediately after purchase (automobiles, computers). Please note that for this type of calculation, the book value can never become zero.

Syntax

DDB(COST;SALVAGE;LIFE;P;FACTOR)

COST determines the initial cost of an asset.

SALVAGE is the value of the asset at the end of the depreciation.

LIFE is the number of periods defining the depreciation time of an asset.

P is the length of the period. The length must be entered in the same date unit as the depreciation period.

F (optional) is the factor for reduction of the depreciation. If no value is entered, the default is factor 2.

Example

A computer system with an initial cost of $75,000 is to be depreciated monthly over 5 years. The value at the end of the depreciation is to be $1. The factor is 2.

DDB(75000;1;60;12;2) = $1,721.81. Therefore, the double-declining depreciation during the first month after purchase is $1,721.81.

SYD, SLN, VDB.

DB

Returns the depreciation of an asset for a specified period using the double-declining balance method.

This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the depreciation already deducted from the initial cost.

Syntax

DDB2(COST;SALVAGE;LIFE;P;MONTH)

COST is the initial cost of an asset.

SALVAGE is the value of an asset at the end of the depreciation.

LIFE The life defines the number of periods during depreciation of the asset.

P is the length of each period. The length must be entered in the same date unit as the depreciation period.

MONTH (optional) is the number of months during the first year of depreciation. For none of the entries, the number 12 is assumed as the default.

Example

A computer system with an initial cost of $75,000 is to be depreciated monthly over three years. The value at the end of the depreciation is to be $1.00. One period is 30 days.

DDB2(25000;1000;36;1;6) = $1,075.00

The fixed-declining depreciation of the computer system is $1,075.00.

DDB, VDB, SYD.

IRR

Returns the internal rate of return for a series of cash flows. It allows you to verify the profitability of an investment.

Syntax

IRR(Values;GUESS)

Values represents a cell reference or a matrix containing the values of the payment amounts.

GUESS (optional) is the estimated value. The start value of the internal rate.

Example

Using an estimated cell content of cells A1=-10000,A2=13500,A3=7600, and A4=1000 the result is displayed as 80.24 %.

NPV, RATE.

NPER

Returns the interest payment on the principal for a given period of an investment, based on regular payments and a constant interest rate.

Syntax

PPMT(Rate;P;NPER;PV;FV;F)

Rate is the periodic interest rate.

P is the payoff period. P=1 for the first and P=NPER for the last period.

NPER is the total number of periods during which annuity is paid.

PV is the current value in the sequence of payments.

FV (optional) is the desired (future) value.

F (optional) is the due date. F=1 for payment at the beginning of a period and F=0 for payment at the end of a period.

Example

What is the periodic payoff with an interest rate of 8.75% and a payment period of 3 years? The cash value is $5,000. Payment is to be made at the beginning of the period. The value at the end of the depreciation is $8,000.

PPMT(8.75%;1;36;5000;8000;1) = $ -4,487.18.

PV, PMT, RATE, IPMT, FV, NPER.

CUMIPMT

Returns the cumulative interest paid for an investment period with a constant interest rate.

Syntax

CUMPRINC(Rate;NPER;PV;START_PERIOD;END_PERIOD;F)

Rate is the periodic interest rate.

NEPER is the payment period with the total number of periods. NPER can also be a non-integer value.

FV is the current value in the sequence of payments.

START_PERIOD is the first period.

END_PERIOD is the last period.

F is the due date of the payment at the beginning or end of each period.

Example

What are the payoff amounts if the interest rate is 5.5% for 36 periods. The cash value is $15,000. The payoff amount is calculated between the 10th and 18th period. The due date is at the end of the period.

CUMPRINC(5.5%;36;15000;10;18;0) = $ -2560.52 $ The payoff amount between the 10th and 18th period is $2560.52.

CUMIPMT.

CUMPPMT

Calculates the cumulative interest payments for an investment based on a constant interest rate.

Syntax

CUMIPMT(Rate;NPER;FV;START_PERIOD;END_PERIOD;F)

Rate is the periodic interest rate.

NEPER is the payment period with the total number of periods. NPER can also be a non-integer value.

FV is the current value in the sequence of payments.

START_PERIOD is the first period.

END_PERIOD is the last period.

F is the due date of the payment at the beginning or end of each period.

Example

What are the interest payments at a periodic interest rate of 5.5 %, a periodic period of 2 years and a current cash value of $5,000? The start period is the 4th and the end period is the 6th period. The periodic payment is due at the beginning of each period.

CUMIPMT(5.5%;24;5000;4;6;1) = $ -710.21 $ The interest payments for between the 4th and 6th period are $710.21.

CUMPRINC

DURATION

DURATION is a function belonging to the financial mathematics. Returns the annual duration of an investment with periodic interest payments.

Syntax

DURATION(Rate;PV;FV)

Rate is a constant. The interest rate is to be calculated for the entire duration (duration period). The interest rate per period is calculated by dividing the interest rate by the calculated duration. The internal rate for an annuity is to be entered as Internal Rate/12.

PV is the present (current) value. The cash value is the deposit of cash or the current cash value of an allowance in kind. As a deposit value a positive value must be entered; the deposit must not be 0 or <0.

FV is the expected value. The future value determines the desired (future) value of the deposit.

Example

At an interest rate of 4.75%, a cash value of $25,000 and a future value of $1,000,000 a duration of 79.49 payment periods is returned. The periodic payment results from the future value divided by the duration, i.e.: 1,000,000/79.49=12,850.20.

ZGZ.

SLN

Returns the straight-line depreciation of an asset for one period.The amount of the depreciation is constant during the depreciation period.

Syntax

SLN(COST; SALVAGE; LIFE)

COST is the initial cost of an asset.

SALVAGE is the value of an asset at the end of the depreciation.

LIFE is the depreciation period determining the number of periods in the depreciation of the asset.

Example

Office equipment with an initial cost of $50,000 is to be depreciated over 7 years. The value at the end of the depreciation is to be $3,500.

SLN(50000;3.500;84) = $553.57 The periodic monthly depreciation of the office equipment is $553.57.

SYD, DDB, VDB.

NPV

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.

Syntax

NPV(RATE;Value 1;Value 2;...)

RATE is the discount rate for a period.

Value1;... are up to 30 values, which represent deposits or withdrawals.

Example

What is the net present value of periodic payments in hundreds of $345, 276.25 and -145 with a discount rate of 8.75 %.

NPV(8.75%;345;276.25;-145) = $438.09 The net present value is therefore $438.09.

PV, IRR, FV.

NOMINAL

Calculates the yearly nominal interest rate, given the effective rate and the number of compounding periods per year.

Syntax

NOMINAL(EFFECT_RATE;NPERY)

EFFECT_RATE is the effective interest rate

NPERY is the number of periodic interest payments per year.

Example

What is the nominal interest per year for an effective interest rate of 13.5% if twelve payments are made per year.

NOMINAL(13.5%;12) = 12.73%. The nominal interest rate per year is 12.73%.

EFFECTIVE.

PMT

Returns the periodic payment for an annuity with constant interest rates.

Syntax

PMT(Rate; DPER; PV; FV; F)

Rate is the periodic interest rate.

NPER is the number of periods in which annuity is paid.

PV is the present value (cash value) in a sequence of payments.

FV (optional) is the desired value (future value) to be reached at the end of the periodic payments.

F (optional) is the due date for the periodic payments. F=1 is payment at the beginning and F=0 is payment at the end of each period.

Example

What are the periodic payments at an interest rate of 1.99% if the payment period is 3 years and the cash value is $25,000.

PMT(1.99%;36;25000) = $-979.25. The periodic monthly payment is therefore $979.25.

PV, PPMT, RATE, IPMT, FV, DPER.

VDB

Returns the depreciation of an asset for a specified or partial period using a variable declining balance method.

Syntax

VDB(COST;SALVAGE;DURATION;START;END;FA;Mode)

COST is the initial value of an asset.

SALVAGE is the value of an asset at the end of the depreciation.

DURATION is the depreciation duration of the asset.

START is the start of the depreciation. A must be entered in the same date unit as the duration.

END is the end of the depreciation.

FA (optional) is the depreciation factor. F=2 is double rate depreciation.

Mode (optional) Mode=1 means that linear depreciation is not used. In Mode=0 it is used.

Example

What is the declining-balance double-rate depreciation for a period if the initial cost is $35,000 and the value at the end of the depreciation is $7500. The depreciation period is 3 years. The depreciation from the 10th to the 20th period is calculated.

VDB(35000;7500;36;10;20;2) = $8603.80 The depreciation during the period between the 10th and the 20th period is $8,603.80.

SYD, DDB, SLN.

-

Calculates the interest rate resulting from the profit (return) of an investment.

Syntax:

ZGZ(P;PV;FV)

P is the number of periods needed for calculating the interest rate.

PV is the present (current) value. The cash value is the deposit of cash or the current cash value of an allowance in kind. As a deposit value a positive value must be entered; the deposit must not be 0 or <0.

FV determines what is desired as the cash value of the deposit.

Example

For four periods (years) and a cash value of $7,500 the interest rate of the return is to be calculated if the future value is $10,000.

ZGZ(4;7500;10000) = 7.46 %

The interest rate must be 7.46 % so that $7,500 will become $10,000.

DURATION.

RATE

Returns the constant interest rate per period of an annuity.

Syntax

RATE(DPER;PMT;PV;FV;F;GUESS)

DEPER is the total number of periods, during which payments are made (payment period).

PMT is the constant payment (annuity) paid during each period.

PV is the cash value in the sequence of payments.

FV (optional) is the future value, which is reached at the end of the periodic payments.

F (optional) is the due date of the periodic payment, either at the beginning or at the end of a period.

GUESS (optional) determines the estimated value of the interest with iterative calculation.

Example

What is the constant interest rate for a payment period of 3 periods if $10 are paid regularly and the present cash value is $900.

RATE(3;10;900) = -1.21% The internal rate is therefore 1.21%.

PV, PPMT, PMT, IPMT, FV, DPER.

PPMT

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax

IPMT(Rate;P;DPER;PV;FV;F)

Rate is the periodic interest rate.

P is the period, for which the compound interest is calculated. P=DPER if compound interest for the last period is calculated.

DPER is the total number of periods, during which annuity is paid.

PV is the present cash value in sequence of payments.

FV (optional) is the desired value (future value) at the end of the periods.

F is the due date for the periodic payments.

Example

What is the interest rate during the fifth period (year) if the constant interest rate is 5% and the cash value is $15,000? The periodic payment is seven years.

IPMT(5%;5;7;15000) = $-352.97 The compound interest during the fifth period (year) is $352.97.

PV, PPMT, CUMPRINC, PMT, RATE.

FV

Returns the future value of an investment based on periodic, constant payments and a constant interest rate (Future Value).

Syntax

FV(Rate; DPER; PMT; PV; F)

Rate is the periodic interest rate.

DPER is the total number of periods (payment period).

PMT is the annuity paid regularly per period.

PV (optional) is the (present) cash value of an investment.

Example

What is the value at the end of an investment if the interest rate is 4% and the payment period is two years, with a periodic payment of $750. The investment has a present value of $2,500.

FV(4%;2;750;2500) = $-4234.00 The value at the end of the investment is $4234.00.

PV, IPMT, PMT, RATE, IPMT, DPER.

NPER

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Syntax

DPER(Rate;PMT;PV;FV)

Rate is the periodic interest rate.

PMT is the constant annuity paid in each period.

PV is the present value (cash value) in a sequence of payments.

FV (optional) is the future value, which is reached at the end of the last period.

F (optional) is the due date of the payment at the beginning or at the end of the period.

Example

How many payment periods does a payment period cover with a periodic interest rate of 6%, a periodic payment of $153.75 and a present cash value of $2.600.

DPER(6%;153.75;2600) = -12,02. The payment period covers 12.02 periods.

PV, PPMT, PMT, RATE, IPMT, FV.


Category: Information


The functions available for the category Information are displayed here with the help of an example. The functions include CURRENT, ISREF, ISERR, ISERROR, ISFORMULA, ISNONTEXT, ISEMPTY, ISLOGICAL, ISNA, ISTEXT, ISNUMBER, N, NV and TYPE. These will be described below.

A number of examples can only be explained by referring to a table. If this is the case, the following table will serve as a basis for the explanation.


C

D

2

x value

y value

3

-5

-3

4

-2

0

5

-1

1

6

0

3

7

2

4

8

4

6

9

6

8


CURRENT

Calculates the current value of a formula at the actual position.

Syntax

CURRENT()

Example

You can use this function in connection with the cell-formatting function TEMPLATE, for example, to assign the current value in a new formatting to the current cell:

=CURRENT()+TEMPLATE(New)

1+2+CURRENT() yields 6 (1+2=CURRENT+CURRENT=6)

1+CURRENT()+2 yields 4 (1=CURRENT+CURRENT+2=4)

FORMULA

This function displays the formula of a formula cell at any position. The formula will be returned as a string in the Reference position. If no formula cell can be found there or if the presented argument is no reference, the error value #NV will be set.

Syntax

FORMULA()

Example

The cell A8 contains the result of a formula having the value 23. You can now, for example, use the Formula function in cell A1 to display the formula of cell A8.

=FORMULA(A8)

ISREF

This functions tests if the content of one or several cells is a reference. Verifies the type of references in a cell or a range of cells.

Syntax

ISREF(value)

Value is the value to be tested to determine whether it is a reference.

Example

ISREF(C5) returns the result TRUE

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISERR

Returns TRUE if the value refers to any error value except #N/A. You can use this function to control error values in certain cells.

Syntax

ISERR(value)

Value is any value or expression where a test is performed to determine whether or not an error value unequal to #NV is present.

Example

ISERR(C5) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISERROR

Contrary to the ISERR, the ISERROR controls if the cells contain general error values.ISERROR recognizes the #NV error value.

Syntax

ISERROR(value)

Value is any value where a test is performed to determine whether or not it is an error value.

Example

ISERROR(C8) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISFORMULA

Returns TRUE if a cell is a formula cell.

Syntax

ISFORMULA(reference)

Reference indicates the reference to a cell in which a test will be performed to determine if it contains a reference.

Example

ISFORMULA(C4) returns FALSE as a result.

ISNONTEXT

Proofs if the cell contents are text or numbers, and returns FALSE if the contents refer to text.

Syntax

ISNONTEXT(value)

Value is any value or expression where a test is performed to determine whether it is a text or numbers or a Boolean value.

Example

ISNONTEXT(D2) returns FALSE as a result.

ISNONTEXT(D9) returns the result TRUE.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISBLANK

Returns TRUE if the reference to a cell is blank. This function is used to control if the content of a cell is empty.

Syntax

ISBLANK

Value is any content that is to be entered in the cell to be tested.

Example

ISBLANK(D2) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISLOG

This function returns TRUE, if the number format is a logarithm. This function is used to verify the occurrence of both TRUE and FALSE values in certain cells.

Syntax

ISLOGICAL(value)

Value is a value for which a test is performed to determine if its format corresponds to a logical number format.

Example

ISLOGICAL(D5) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISNV

Returns TRUE if a cell contains the #N/A (value not available) error value.

Syntax

ISNA(value)

Value is the value or expression to be tested.

Example

ISNA(D3) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISTEXT

Returns TRUE if the cell contents refer to text.

Syntax

ISTEXT(value)

Value is a value, a number, a Boolean value or an error value for which a test will be performed to determine if it is a text or numbers.

Example

ISTEXT(D9) returns the result TRUE.

ISTEXT(C3) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

ISNUMBER

Returns TRUE if the value refers to a number.

Syntax

ISNUMBER(value)

Value is any expression that is to be tested to determine whether it is a number or text.

Example

ISTEXT(C3) returns the result TRUE.

ISTEXT(C2) returns FALSE as a result.

ERRORTYPE, ISEVEN, ISODD, TYPE.

N

Returns a value converted to a number. You can use this function to convert true values in number values 1 or 0.

Syntax

N(value)

Value is a value that is to be converted into a number.

Example

N(TRUE) returns 1

N(FALSE) returns 0

N(#NV) returns 0

T.

NA

Returns the error value #N/A.

Syntax

NV()

Example

NV() converts the contents of the cell into #NV.

ISREF, ISERR, ISERROR, ISFORMULA. ISNONTEXT, ISBLANK, ISLOGICAL, ISNA, ISTEXT, ISNUMBER.

TYPE

Returns the type of value.

Syntax

TYPE(value)

Value is a specific value for which the data type is to be determined. Value 1 = number, value 2 = text, value 4 = Boolean value, value 8 = formula, value 16 = error value.

Example

TYPE(C2) returns 2 as a result.

TYPE(D9) returns 1 as a result.


Category: Logical


Here, you will find the functions available for the Logical category displayed in the form of an example. These are the FALSE, NOT, OR, AND, TRUE, and IF function. They are described below.

FALSE

Returns the logical value FALSE. Two or more arguments are linked by the logical operators AND or OR. The operator returns FALSE, if required by the content of the arguments.

Syntax

FALSE()

Example

If A=TRUE and B=FALSE the following examples appear:

A AND B returns FALSE

A OR B returns TRUE

TRUE.

NOT

Reverses the logical value.

Syntax

NOT(Boolean value)

Boolean Value is any value to be reversed.

Example

NOT(A). A=TRUE reverses to A=FALSE.

OR, AND.

OR

Returns TRUE if at least one argument is TRUE. This function returns the value FALSE, if all the arguments have the logical value FALSE.

The arguments are either logical expressions themselves (TRUE, 1<5, 2+3=7, B8<10) that return logical values, or arrays (A1:C3) containing logical values.


If an argument entered as a matrix contains text or empty cells, these will be ignored. For example, formula =OR(0<C9:C16; FALSE) returns TRUE if matrix (C9:C16) contains values greater than 0, and cells containing text or empty cells may be included.
If the range indicated does not contain any logical values AND returns the error value #Wert!


Syntax

OR(Boolean value 1; Boolean value 2 ...Boolean value 30)

Boolean value 1; Boolean value 2 ...Boolean value 30 are conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the Boolean values of the cells contained in that range are linked by the OR condition. The result is TRUE if the Boolean value in a cell within the cell range is TRUE.

Example

The logical values of entries 12<11; 13>22, and 45=45 are to be checked.

OR(12<11; 13>22; 45=45) returns TRUE.

OR(FALSE;TRUE) returns TRUE.

NOT, AND.

AND

Returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns the FALSE value.

The arguments are either logical expressions themselves (TRUE, 1<5, 2+3=7, B8<10) that return logical values, or arrays (A1:C3) containing logical values.


If an argument entered as a matrix contains text or empty cells, these will be ignored. For example, formula =AND(0<C9:C16; C9:C16<10) returns TRUE if matrix (C9:C16) contains values between 0 and 10, and cells containing text or empty cells may be included.
If the range indicated does not contain any logical values AND returns the error value #Wert!


Syntax

AND(Boolean value 1; Boolean value 2 ...Boolean value 30)

Boolean value 1; Boolean value 2 ...Boolean value 30 are conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the Boolean values of the cells contained in that range are linked by the AND condition. The result is TRUE if the Boolean value in all cells within the cell range is TRUE.

Example

The logical Boolean values of entries 12<13; 14>12, and 7<6 are to be checked.

AND(12<13; 14>12; 7<6) returns FALSE.

AND (FALSE;TRUE) returns FALSE.

NOT, OR.

TRUE

The logical value is set to TRUE. The logical function TRUE verifies two arguments and returns the TRUE value if both values match.

Syntax

TRUE()

Example

If A=TRUE and B=FALSE the following examples appear:

A AND B returns FALSE

A OR B returns TRUE

(NOT) A AND B returns TRUE

FALSE

IF

Returns one value if a specified condition is TRUE and another value if it is FALSE.

Syntax

IF(Logical Test; Then value; Else value)

Logical Test is any value or expression, which can be TRUE or FALSE.

Then Value (optional) is the value that is returned if the logical test is TRUE.

Else Value (optional) is the value that is returned if the logical test is FALSE.

Examples

IF(A1>5;100;"too little") If the value in A1 is greater than 5, the value 100 is entered in the current cell; otherwise, too little is entered in text format.

FALSE, NOT, OR, AND, TRUE.


Category: Mathematics


The functions available for the mathematics category, are shown here with an example. An explanation of how to use the function Autopilot is provided in the Insert - Function menu. These are the available functions: ROUNDDOWN, ABS, COUNTBLANK, ACOS, ACOSH, ACOT, ACOTH, ASIN, ASINH, ATAN, ATAN2, ATANH, ROUNDUP, COS, COSH, COT, COTH, DEG, EXP, FACT, INTEGER, EVEN, GCD, ISEVEN, ISODD, LCM, COMBIN, COMBINA, TRUNC, LN, LOG, LOG10, CEILING, PI, POWER, PRODUCT, SUMSQ, RADIANS, MOD, ROUND, SIN, SINH, SUM, SUMIF, TAN, TANH, SUBTOTAL, CONVERT, ODD, FLOOR, SIGN, SQRT, RAND, COUNTIF.

Some functions refer to table functions. In these cases, the following table serves as a basis.


A

B

C

D

E

1

167.56

351.10

57.25

17.95

870.29

2

479.01


269.99

86.30

351.10

3


12.60


22.50


4







ROUNDDOWN

Rounds a number down, toward zero.

Syntax:

ROUNDDOWN(Number; Count)

Number is the number to be rounded down.

Count (optional) is the number of digits to be rounded down to. If the Count parameter is negative, rounding is to the digits preceding the decimal point.

Example:

Entering the value 567.567 and the value 2 in the Count field will return 567.56.

ROUNDUP, INT, TRUNC, CEILING, MOD, ROUND, FLOOR

ABS

Returns the absolute value of a number.

Syntax:

ABS(Number)

Number is the value whose absolute value is to be calculated.

Example:

Entering the value -56 will return an absolute value of 56.

Entering the value 56 will return an absolute value of 56.

SIGN

COUNTBLANK

If you want to count the number of empty cells, enter the cell references separated by a colon in the text box Range.

Syntax:

COUNTBLANK(Area)

Area is the area, in which the empty cells are counted.

Example:

Entering = COUNTBLANK (A1:C3) in an empty cell area will result in 9.

COUNTIF

ARCCOS

Returns the arc cosine of a number.

Syntax:

ACOS(Number)

Number is the value, whose arccosine value is to be calculated.

Example:

The arccosine of -1 returns the value 3.14.

The arccosine of 0 returns the value 1.57.

The arccosine of 1 returns the value 0.

ASIN, ATAN, ACOT, COS

ARCCOSH

Returns the inverse arc cosine of a number.

Syntax:

ACOSH(Number)

Number is the value whose inverse hyperbolic cosine is to be calculated.

Example:

The inverse hyperbolic cosine of 1 returns the value 0.

The inverse hyperbolic cosine of 15 returns the value 3.4.

The inverse hyperbolic cosine of 30 returns the value 4.09.

ASINH, ATANH, ACOTH, COSH

ACOT

Returns the inverse cotangent of the given number.

Syntax:

ACOT(Number)

Number is the value whose inverse cotangent is to be calculated.

Example:

The inverse cotangent of -1 returns the value 2.36.

The inverse cotangent of 0 returns the value 1.57.

The inverse cotangent of 1 returns the value 0.79.

ATAN, ASIN, ACOS, COT

ARCCOTH

Returns the inverse hyperbolic cotangent of the given number.

Syntax:

ACOTH(Number)

Number is the value whose inverse hyperbolic cotangent is to be calculated.

Example:

The inverse hyperbolic cotangent of 1.1 returns the value 1.52.

The inverse hyperbolic cotangent of 45 returns the value 0.02.

The inverse hyperbolic cotangent of 90 returns the value 0.01.

ATANH, ASINH, ACOSH, COTH

ASIN

Returns the arcsine of a number.

Syntax:

ASIN (Number)

Number is the value whose arcsine is to be calculated.

Example:

The arcsine of -1 returns the value -1.57.

The arcsine of 0 returns the value 0.

The arcsine of 1 returns the value 1.57.

ACOS, ATAN, ACOT, SIN

ARCSINH

Returns the inverse arcsine of a number.

Syntax:

ASINH(Number)

Number is the value whose inverse hyperbolic sine is to be calculated.

Example:

The inverse hyperbolic sine of -90 returns the value -5.19.

The inverse hyperbolic sine of 0 returns the value 0.

The inverse hyperbolic sine of 90 returns the value 5.19.

ACOSH, ATANH, ACOTH, SINH

ATAN

Returns the arctangent of a number .

Syntax:

ATAN(Number)

Number is the value whose arctangent value is to be calculated.

Example:

The arctangent of -1 returns the value -0.79.

The arctangent of 0 returns the value 0.

The arctangent of 45 returns the value 1.55.

ASIN, ACOS, ACOT, TAN, ATAN2

ATAN2

Returns the arctangent of the specified x and y coordinates.

Syntax:

ATAN2(Number x; number y)

Number x is the value for the x coordinate.

Number y is the value for the y coordinate.

Example:

Entering the value 45 for the x coordinate and the value 90 for the y coordinate will return an arctangent of 1.11.

Entering the value -1 for the x coordinate and the value 0 for the y coordinate will return an arctangent of 3.14.

ATAN, ATANH, PI, TAN

ATANH

Returns the inverse hyperbolic tangent of a number.

Syntax:

ATANH(Number)

Number is the value whose inverse hyperbolic tangent is to be calculated.

Example:

The inverse hyperbolic tangent of 0.99 returns the value 2.65.

The inverse hyperbolic tangent of 0 returns the value 0.

The inverse hyperbolic tangent of -0.95 returns the value -1.83.

ACOTH, ASINH, ACOSH, TANH

ROUNDUP

Rounds a number up, according to the specified decimal place.

Syntax:

ROUNDUP(Number; Count)

Number is the number to be rounded up.

Count (optional) is the number of digits to which rounding up is to be done.

Example:

Entering the value 123.343 and the value 2 in the Count field will return the value 123.35.

ROUNDDOWN, INT, TRUNC, CEILING, MOD, ROUND, FLOOR

COS

Returns the cosine of the given number (angle).

Syntax:

COS(Number)

Number is the value whose cosine is to be calculated.

Example:

The angle 6.28 (2Pi) returns a cosine of 1 radian.

The angle 3.14 (Pi) returns a cosine of -1 radian.

The angle 1.57 (Pi/2) returns a cosine of 0 radians.

SIN, TAN, COT, PI, ACOS

COSH

Returns the inverse hyperbolic cosine of a number.

Syntax:

COSH(Number)

Number is the value whose hyperbolic cosine is to be calculated.

Example:

Entering the value -5 will return a hyperbolic cosine of 74.21.

Entering the value 0 will return a hyperbolic cosine of 1.

Entering the value 90 will return a hyperbolic cosine of 6.10E038.

SINH, TANH, COTH, ACOSH

COT

Returns the cotangent of the given angle.

Syntax:

COT(Number)

Number is the value whose cotangent is to be calculated.

Example:

The angle -45 returns a cotangent of -0.62 radians.

The angle 90 returns a cotangent of -0.5 radians.

SIN, COS, TAN, PI, ACOT

COTH

Returns the inverse hyperbolic cotangent of a given number (angle).

Syntax:

COTH(Number)

Number is the value whose hyperbolic cotangent is to be calculated.

Example:

Entering the value 90 returns a hyperbolic cotangent of 1.

Entering the value 45 returns a hyperbolic cotangent of 1.

Entering the value -45 returns a hyperbolic cotangent of -1.

TANH, SINH, COSH, ACOTH

DEGREES

Converts radians into degrees.

Syntax:

DEG(Number)

Number is the value whose degree is to be calculated.

Example:

The degree 5.73 will be calculated based on the 01 input value.

The degree 90 will be calculated based on the 1.5708 input value.

The degree -45 will be calculated based on the -0.7854 input value.

RADIANS, PI

EXP

Returns e raised to the power of a number.

Syntax:

EXP(number)

Number is the power to which e is to be raised.

Example:

The result for e raised to the power -4 is 0.02.

The result for e raised to the power 0.5 is 1.65.

The result for e raised to the power 1 is 2.72.

LN, LOG, POWER

FACT

Returns the factorial of a number.

Syntax:

FACT(number)

Number is the value whose factorial is to be calculated.

Example:

The factorial of the value 0 is 1.

The factorial of the value 3 is 6.

The factorial of the value 10 is 3628880.

PRODUCT

INTEGER

Rounds a number down to the nearest integer.

Syntax:

INT(number)

Number is the number that is to be rounded down to the nearest integer.

Example:

If you enter the number -0.1, -1 will be returned as the result.

If you enter the number 23.74, 23 will be returned as the result.

TRUNC, CEILING, MOD, ROUND, FLOOR

EVEN

Returns a number rounded up to the nearest even integer.

Syntax:

EVEN(number)

Number is the number that is to be rounded up to the nearest even integer.

Example:

If you enter the number 0.01, 2 will be returned as the result.

If you enter the number -2.1, -4 will be returned as the result.

If you enter the number 17.9, 18 will be returned as the result.

INT, ISEVEN, ISODD, TRUNC, CEILING, ROUND, ODD, FLOOR

GCD

Returns the greatest common divisor of two or more integers.

Syntax:

GCD(integer 1 to 30)

Integer 1 to 30 are up to 30 integers whose greatest common divisor is to be calculated.

Example:

If you enter the numbers 512;1024 and 2000 in text boxes Integer 1;2 and 3, 16 will be returned as the result.

LCM

ISEVEN

Returns TRUE if the value is an even integer, or FALSE if the value is odd.

Syntax:

ISEVEN(value)

Value is the value to be checked.

Example:

If you enter the value 642, TRUE will be returned as the result.

If you enter the value -4, TRUE will be returned as the result.

If you enter the value 7.6, FALSE will be returned as the result.

EVEN, ISODD, ODD

ISODD

Returns TRUE if the value is odd, or FALSE if the number is even.

Syntax:

ISODD(value)

Value is the value to be checked.

Example:

If you enter the value 642, FALSE will be returned as the result.

If you enter the value -4, FALSE will be returned as the result.

If you enter the value 7.6, TRUE will be returned as the result.

EVEN, ISEVEN, ODD

LCM

Returns the least common multiple of one or more integers.

Syntax:

LCM(integer 1 to 30)

Integer 1 to 30 are up to 30 integers whose lowest common multiple is to be calculated.

Example:

If you enter the numbers 512;1024 and 2000 in the Integer 1;2 and 3 text boxes, 128000 will be returned as the result.

GCD

COMBIN

Returns the number of combinations for a given number of objects.

Syntax:

COMBIN(count 1; count 2)

Count 1 is the total number of elements.

Count 2 is the select count from the elements.

Example:

If you enter 2 in text boxes Count 1 and 2, 1 will be returned as the result.

COMBINA, BINOMDIST, FACT, HYPGEOMDIST, CRITBINOM, NEGBINOMDIST, PERMUT

COMBIN2

Returns the number of combinations for a given number of objects (repetetition included).

Syntax:

COMBINA(count 1; count 2)

Count 1 is the total number of elements.

Count 2 is the select count from the elements.

Example:

If you enter 2 in text boxes Count 1 and 2, 3 will be returned as the result.

COMBIN, BINOMDIST, FACT, HYPGEOMDIST, CRITBINOM, NEGBINOMDIST, PERMUTATIONA

TRUNC

Truncates a number to an integer (number) by removing the fractional part of the number according to the precision specified in num_digits.



Entering the number of decimal places activates the number of decimal places shown in the Decimal places field under Tools - Options - General - Calculate. Modify this value based on the number of decimal places you want.


Syntax:

TRUNC(number; count)

Number is the number whose decimal places are to be cut off.

Count is the number whose decimal places are not cut off.

Example:

If you enter the number 34.5678 and the count 1, 34.5 will be returned as the result.

If you enter the number -15.769 and the count 3, -15.77 will be returned as the result.

INT, CEILING, MOD, ROUND, FLOOR

LN

Returns the natural logarithm based on the constant e (2.71828182845904) of a number.

Syntax:

LN(number)

Number is the value whose natural logarithm is to be calculated.

Example:

The natural logarithm to the base e of the value 3 will return 1.1 as the result.

The natural logarithm to the base e of the value 13 will return 2.56 as the result.

The natural logarithm to the base e of the value 1000 will return 6.91 as the result.

EXP, LOG, LOG10

LOG

Returns the logarithm of a number to the specified base.

Syntax:

LOG(number; base)

Number is the value whose logarithm is to be calculated.

Base is the base for the logarithm calculation.

Example:

The logarithm of the number 10 to the base 3 will return 2.1 as the result.

The logarithm of the number 2.1 to the base 7 will return 0.38 as the result.

The logarithm of the number 0.75 to the base 7.5 will return -0.14 as the result.

EXP, LN, LOG10

LOG10

Returns the base-10 logarithm of a number.

Syntax:

LOG10(number)

Number is the value whose logarithm to the base 10 is to be calculated.

Example:

The logarithm to the base 10 of the value 3 will return 0.48 as the result.

The logarithm to the base 10 of the value 10 will return 1 as the result.

The logarithm to the base 10 of the value 0.02 will return -1.7 as the result.

EXP, LN, LOG

CEILING

Rounds a number to the nearest integer or multiple of significance.

Syntax:

CEILING(number, increment; mode)

Number is the number that is to be rounded up.

Increment is the number to whose multiple the value is to be rounded up.

Mode is an optional value. If it is indicated and is not equal to zero and if the number and increment are negative, rounding up is carried out based on that value. Please note that this input gets lost in an import to MS Excel since Excel does not recognize any third parameter in functions.


If both parameters Number and Significance are negative and the Mode value is equal to zero or is not given, the results in StarOffice and Excel will differ after the import has been completed.


Example:

If you enter the number 3.4 and the increment 1, 4 will be returned as the result.

If you enter the number -6 and the increment -5, -5 will be returned as the result.

If you enter the number -0.6 and the increment -0.4, -0.4 will be returned as the result.

ROUNDDOWN, ROUNDUP, INTEGER, EVEN, TRUNC, ROUND, ODD, FLOOR

PI

Returns the value of PI (3.14).

Syntax:

PI()

Example:

Pi is 3.14... as a rounded value.

SIN, COS

POWER

Returns the result of a number raised to a power.

Syntax:

POWER(base; power) or base ^ power

Base is the number that is to be raised to a given power.

Power is the exponent with which the base is to be raised to a power.

Example:

If you enter 3 as the base and -2 as the power, 0.11 will be returned as the result.

If you enter -3 as the base and -2 as the power, 0.11 will be returned as the result.

If you enter -3 as the base and 2 as the power, 9 will be returned as the result.

POWER, PRODUCT, SQUARE ROOT

PRODUCT

Multiplies all the numbers given as arguments and returns the product.

Syntax:

PRODUCT(number 1 to 30)

Number 1 to number 30 are up to 30 arguments whose product is to be calculated.

Example:

If you enter the numbers 2; 3 and 4 in the Number 1; 2 and 3 text boxes, 24 will be returned as the result.

FACTOR, SUM, SUMPRODUCT

SUMSQ

If you want to calculate the sum of the squares of numbers (totalling up of the squares of the arguments), enter these into the text fields.

Syntax:

SUMSQ(number 1 to 30)

Number 1 to number 30 are up to 30 arguments the sum of whose squares is to be calculated.

Example:

If you enter the numbers 2; 3 and 4 in the Number 1; 2 and 3 text boxes, 29 will be returned as the result.

SUM, SUMPRODUCT

RADIANS

Converts degrees to radians.

Syntax:

RADIANS(number)

Number is the angle in degrees.

Example:

If you enter 5.73 as the angle in degrees, 0.1 will be returned as the angle in radians.

If you enter 90 as the angle in degrees, 1.57 will be returned as the angle in radians.

If you enter -45 as the angle in degrees, -0.79 will be returned as the angle in radians.

DEGREES, PI

MOD

Returns the remainder after a number is divided by a divisor.

Syntax:

MOD(Number; Divisor)

Number is the value for which to find the remainder after dividing.

Divisor is the number by which to divide the specified value.

Example:

The value 17 in the Integer field is to be divided by the divisor -1.4. -1.2 will be returned as the remainder.

The value -13 in the Integer field is to be divided by the divisor -3.4. -2.8 will be returned as the remainder.

The value 2987 in the Integer field is to be divided by the divisor 362. 91 will be returned as the remainder.

ROUNDDOWN, ROUNDUP, INT, TRUNC, ROUND

ROUND

Rounds a number to a specified number of digits.

Syntax:

ROUND(number; count)

Number is the number to be rounded.

Count (optional) is the number of the places to which the value is to be rounded. If the Count parameter is negative, numbers before the decimal are rounded.

Example:

If you enter the number 17.546 in the Number field, with 1 specified as the number of rounding places, 17.5 will be returned as the result.

If you enter the number -32.483 in the Number field, with 3 specified as the number of rounding places, -32.48 will be returned as the result.

ROUNDDOWN, ROUNDUP, INT, TRUNC, CEILING, MOD, FLOOR

SIN

Returns the sine of the given number (angle).

Syntax:

SIN(number)

Number is the angle in radians.

Example:

The sine of the angle (in radians) 3.14 (Pi) is 0.

The sine of the angle (in radians) 1.57 (Pi/2) is 1.

The sine of the angle (in radians) 0.79 (Pi/4) is 0.71.

COS, COT, TAN, PI, ASIN

SINH

Returns the hyperbolic sine of a number.

Syntax:

SINH(number)

Number is the number whose hyperbolic sine is to be calculated.

Example:

If you enter the value -5, -74.21 will be returned as the hyperbolic sine.

If you enter the value 0, 0 will be returned as the hyperbolic sine.

If you enter the value 90, 6.10E038 will be returned as the hyperbolic sine.

COSH, TANH, COTH, ASINH

SUM

Adds all the numbers in a range of cells.

Syntax:

SUM(number1; number 2; ...; number 30)

Number 1 to number 30 are up to 30 arguments whose sum is to be calculated.

Example:

If you enter the numbers 2; 3 and 4 in the Number 1; 2 and 3 text boxes, 9 will be returned as the result.

SUM(A1;A3;B5) calculates the sum of the three cells. SUM (A1:E10) calculates the sum of all cells in the A1 to E10 cell range.

Conditions linked by AND can be used with the function SUM() in the following way:

Example assumption: You have entered your invoices into a table. Column A contains the date value of the invoice, column B the amounts. You are wanting to find a formula which you can use to return the total of all amounts only for a specific month, e.g. only the amount for the period >=1.1.99 to <1.2.99. The range with the date values covers A1:A40, the range containing the amounts to be totalled is B1:B40. C1 contains the start date, 1.1.99, of the invoices to be included and C2 the date, 1.2.99 that is to be no longer included.

Now enter the following formula as a matrix formula:

=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)

In order to enter this as a matrix formula you must press the Shift Command+ Ctrl + Enter key instead of simply pressing the Enter key to close the formula. The formula will then be shown in the Formula bar enclosed in braces.

{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}

The formula is based on the fact that the result of a comparison is 1, if the criterion is met and 0 if it is not. The individual comparison results will be treated as a matrix and used in matrix multiplication and at the end the individual values will be totalled to give the result matrix. The SUM() function can also be used in this way, for example, as COUNTIF() with several criteria.

COUNT, COUNT2, AVERAGE, PRODUCT

SUMIF

Adds the cells specified by a given criteria. This function is used to browse a range when you search for a certain value.

Syntax:

SUMIF(range; criteria; sum range)

Range is the range to which the criteria are to be applied.

Criteria is the cell in which the search criterion is shown, or the search criterion itself.

Sum range is the range from which values are summed. If this parameter has not been indicated, the values found in the Range are summed.

Example:

From the example table, the criterion 351.10 from the range A1:E4 is to be summed. Enter A1:E4 in the Range text box. 702.2 will be returned as the result, as the system sums all cells containing 351.10.

Second example: Imagine a table which you use to manage your daily expenditures. Column A (area) contains individual bills, e.g. groceries, newspapers, travel costs, and column B (sum section) contains the amount for each bill. You have entered several bills in the grocery category, and you want to know the sum of your grocery expenditures. The formula is:=SUMIF(A1:A100;"groceries";B1:B100)

A further example can be found under SUM().

SUM, COUNTIF

TAN

Returns the tangent of the given angle.

Syntax:

TAN(number)

Number is the angle in radians.

Example:

The tangent of the angle (in radians) 3.14 (Pi) is 0.

The tangent of the angle (in radians) 0.79 (Pi/4) is 1.62.

The tangent of the angle (in radians) 0.39 (Pi/8) is -2.

SIN, COS, COT, PI, ATAN

TANH

Returns the hyperbolic tangent of a number .

Syntax:

TANH(number)

Number is the number whose hyperbolic tangent is to be calculated.

Example:

If you enter the value -5, the system returns the hyperbolic tangent -1.

If you enter the value 45, the system returns the hyperbolic tangent 1.

If you enter the value 90, the system returns the hyperbolic tangent 1.

COTH, SINH, COSH, ATANH

SUBTOTAL

Calculates subtotals in a table. If a range contains already subtotals, these are not used for other calculations. Use this function with the AutoFilters if you only want to consider the filtered records.

The following functions are available, and are field shaded with a function index.

Function index

Function

1

AVERAGE

2

COUNT

3

COUNT2

4

MAX

5

MIN

6

PRODUCT

7

STDEV

8

STDEVP

9

SUM

10

VAR

11

VARP


Syntax:

SUBTOTAL(function; range)

Function is an index indicating the possible function (sum, max., etc.).

Range is the range whose cells are included.

Example:

Imagine you have a table in the cell range A1:B5 containing cities in column A and accompanying figures in column B. You have used an AutoFilter, e.g. so that you only see rows containing the city Hamburg. You now wish to see the sum of the figures that are displayed, i.e. just the subtotal for the filtered rows. In this case the correct formula would be:

=SUBTOTAL(9; B2:B5)

From the example table at the top of the page, function index 6 (product) from the range A1:A2 is to be used. Enter Range A1:A2 in the text box, along with 6 as the function. 80262.92 is returned as the result.

From the example table at the top of the page, function index 9 (sum) from the range B2:D3 is to be used. Enter Range B2:D3 in the text box, along with 9 as the function . 391.39 will be returned as the result.

CONVERSION

Use this function to calculate a currency value (e.g. Austrian Schillings) in Euro and vice versa. The rates of exchange have been taken from the convert.ini file which is located in the Office52/share/config. You can copy this file to Office52/user/config and edit it so that your personal conversion factors can also be taken into account. If there is a conversion factor in both files then the conversion factor contained in the Office52/share/config file will be used.

When calculating a function and after each document loading, if you use this function, the convert.ini file is read from the Office52/share/config and Office52/user/config directories. Up to 16383 converting entries are possible. If a converting factor is found in both, the file in the Share directory and in the user directory, the one from the Share directory has preference. In the entries, the combination "Text1;Text2" will be searched for and, when found it will be multiplied by Value. If Text1;Text2" is not found, it will then search for "Text2;Text1" and, when found will Value be then divided by the resulting factor. If nothing is found, the error code #NA (not available) will be set.

Syntax:

CONVERT(value;"Text"; "Text")

Value is the amount in the currency to be converted.

Text is the designation for the currency in question and the euro, in the sequence "from unit" "to unit". The text should be placed in inverted commas and capitalized/placed in lower case as appropriate (e.g. "EUR").

Example:

=CONVERT(100;"ATS";"EUR")

=CONVERT(100;"EUR";"DEM")

The table contains the currency designations and the fixed euro exchange rates set by the European Commission, effective 31 December 1998. The basis for calculations is 1 euro. You can find more information about this at http://europa.eu.int/eurobirth/rates.html.

"EUR"

"ATS"

13.7603

Austrian schilling

"EUR"

"BEF"

40.3399

Belgian franc

"EUR"

"DEM"

1.95583

German mark

"EUR"

"ESP"

166386

Spanish peseta

"EUR"

"FIM"

5.94573

Finnish markka

"EUR"

"FRF"

6.55957

French franc

"EUR"

"IEP"

0.787564

Irish pound

"EUR"

"ITL"

1936.27

Italian lira

"EUR"

"LUF"

40.3399

Luxembourg franc

"EUR"

"NLG"

2.20371

Dutch guilder

"EUR"

"PTE"

200482

Portuguese escudo


ODD

Returns a number rounded up to the nearest odd integer.

Syntax:

ODD(number)

Number is the number that is to be rounded up.

Example:

If you enter the number 1.01, 3 will be returned as the result.

If you enter the number -3.01, -5 will be returned as the result.

If you enter the number 17.9, 19 will be returned as the result.

INT, EVEN, ISEVEN, ISODD, TRUNC, CEILING, ROUND, FLOOR

FLOOR

Rounds a number down to the nearest multiple of significance.

Syntax:

FLOOR(number; increment; mode)

Number is the number that is to be rounded down.

Increment is the value to whose multiple the number is to be rounded down.

Mode is an optional value. If it is indicated and is not equal to zero, if the number and increment are negative, rounding up is carried out based on that value. It is also possible that this value is ignored after completing an import to MS Excel as Excel does not know any third parameter.


If both parameters, Number and Significance are negative and if the Mode value is equal to zero or is not specified, then the results in StarOffice Calc and Excel will differ after exporting.


Example:

If you enter the number 3.4 and the increment 1, 3 will be returned as the result.

If you enter the number -6 and the increment -5, -10 will be returned as the result.

If you enter the number -0.6 and the increment -0.4, -0.8 will be returned as the result.

ROUNDDOWN, ROUNDUP, INT, EVEN, TRUNC, CEILING, ROUND, ODD

SIGN

Determines the sign of a number. Returns 1 for a positive sign and -1 for a negative sign.

Syntax:

SIGN(number)

Number is the number whose sign is to be determined.

Example:

If you enter the number 3.4, 1 will be returned as the result.

If you enter the number -4.5, -1 will be returned as the result.

ABS

SQRT

Returns a positive square root of a number. The value of the number must be positive.

Syntax:

SQRT(number)

Number is the number whose square root is to be calculated.

Example:

The square root of 16 is 4.

The square root of the number -16 results in an error message.

SQRT(ABS(-16)) = 4.

POWER, ABS

RAND

Returns a random number between 0 and 1.

Pull up a range in the table, start this function, select the Matrix field and click End. The range you have selected will be filled with random numbers between 0 and 1.

Syntax:

RAND( )

COUNTIF

If you want to count elements that meet with certain criteria enter the range into the Range field to define where the criteria are to be applied. Enter the search criteria into the Criteria field.

Syntax:

COUNTIF(range; criteria)

Range is the range to which the criteria are to be applied.

Criteria indicates the criteria in the form of a number, an expression or a character string. These criteria determine which cells are counted. For example, a search criterion may be formulated as 17, "17" ">100" or blue. You may also enter a search text in the form of a regular expression, e.g. "b.*" for all words that begin with b. You may also indicate a cell range that contains the search criterion.

Example:

In the example table, the criterion 22.5 is to be counted from the range A1:E4. You should enter Range A1:E4, along with 22.5 as the criterion , in the text box. 1 will be returned as the result.

COUNTIF(A1:E4;22,5) returns 1.

A further example can be found under SUM().

COUNTBLANK, SUMIF


Category: Matrix


The following functions belonging to the Matrix category functions will be explained using an example: MUNIT, FREQUENCY, MEDTERM, MINVERSE, MMULT, MTRANS, LINEST, LOGEST, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, TREND and GROWTH These functions will be further explained below.

What is a Matrix?

When carrying out spreadsheet calculations, a connected range of cells, containing values, will be designated as a matrix. If the cell range contains m rows and n columns, it is called an m x n matrix. For example, a square range with three columns and three rows in a 3 x 3 matrix:


A

B

C

1

7

31

33

2

95

17

2

3

5

10

50


The smallest possible matrix is a 1 x 2 or 2 x 1 matrix with two adjacent cells.

What is a matrix formula?

A formula in which the individual values in a cell range are evaluated referred to as a matrix formula. The difference between a matrix formula and other formulas is that the matrix formula deals with several values simultaneously instead of just one.

Not only can a matrix formula process several values, but it can also return several values. The results of a matrix formula is also a matrix.

To multiply the values in the individual cells by 10 in the above matrix, you do not need to apply a formula to each individual cell or value. Instead you just need to use a single matrix formula. Simply enter the formula "=10*A1:C3" and confirm this entry using the key combination (Command)(Ctrl)(Shift)(Enter). The result is a 3 x 3 matrix in which the individual values in the cell range (A1:C3) are multiplied by a factor of 10.

In addition to multiplication, you can also use other operators on the reference range (a matrix). With StarOffice Calc, you can add (+), subtract (-), multiply (*), division (/), use exponents (^), concatenation (&) and comparisons (=, <>, <, >, <=, >=). The operators can be used on each individual value in the cell range and return the result as a matrix if the matrix formula was entered.

When changing comparison operators in matrix formulas, empty cells will follow the same rules as for a single comparison, i.e., it could be represented either by 0 or as an empty string. Therefore, the matrix formulas {=A1:A2=""} and {=A1:A2=0} will both return TRUE if cells A1 and A2 are empty.



Matrix comparisons appear differently in StarOffice 5.0 and later than in earlier versions.


When do you use matrix formulas?

If you must repeatedly carry out the same calculations using different values, then you should use matrix formulas since you only need to enter the values once to calculate the different output values. If the method of calculation, i.e., formula changes, then you only have to change it once by changing it in one place. First select the entire matrix range and then make the required change to the matrix formula.

Matrix formulas are also a space-saving option when several values must be calculated, since they are not very memory-intensive. In addition, matrices are an essential tool for easily and clearly carrying out complex calculations, because you can have several cell ranges, i.e., several matrices included in your calculations. In this case, StarOffice has different math functions for matrices, e.g. the MMULT function for multiplying two matrices or the SUMPRODUCT function for calculating the scalar products of two matrices.

Using Matrix Formulas in StarOffice Calc

Of course, you can also create "normal" formulas in which the reference range, such as parameters, indicate a matrix formula. Therefore, not each and every value in the cell range will be calculated according to the formula; instead the result will be obtained from intersection of the reference range and the rows or columns, in which the formula is found. If there is no intersection or if the range at the intersection covers several rows or columns, a #value! error message will appear. The following example should make this clear:

The formula "=$A$2:$A$4=2" in column B returns results obtained from the intersection with the reference range (A2:A4). In contrast, the matrix formula "{=$A$2:$A$4=2}" in column C calculates individually all contents of the reference range (A2:A4) and returns the results as a matrix.


A

B

C

1


#VALUE!

FALSE

2

1

FALSE

TRUE

3

2

TRUE

FALSE

4

3

FALSE

#NA

5


#VALUE!

#NA




In earlier versions of StarOffice, a matrix was automatically created when the above-mentioned operators were applied as a parameter on the reference range. Thus, the formula "=Sum(B8-(B10:B12))" automatically returned the sum (B8-B10)+(B8-B11)+(B8-B12). This is no longer the case in StarOffice 5.0. Only when the formula is explicitly given as a matrix formula in the form {"=Sum(B8-(B10:B12))"} will the specified cell range will be considered a parameter.


If you import spreadsheet documents from older versions to StarOffice 5.2 and have used "normal" formulae with cell areas as parameters, then you have to manually create matrix formulae from these formulae to get the same results. To do this you have to:

Matrix formulas, creating

If you create a matrix formula using the Function Autopilot, you must select the Matrix check box each time so that the results are returned in a matrix. (Otherwise, only the value in the upper-left cell of the matrix being calculated would be returned.)

If you enter the matrix formula directly into the cell, you must use the key combination (Shift)(Command)(Ctrl)+(Enter) instead of the Enter key. Only then does the formula become a matrix formula.



Matrix formulas appear in braces in StarOffice Calc. N.B.: You cannot create matrix formulas by manually entering the braces, since StarOffice Calc will interpret the braces as characters.

The cells in a results matrix are automatically protected against changes. However, you can edit or copy the matrix formula by selecting the entire cell range; in other words, the entire matrix.

Editing Matrix Formulas



You can format the separate parts of a matrix. For example, you can change the font color. Simply select a given cell range and then change the attribute you want.


Copying Matrix Formulas

Adjusting a matrix range

If you want to edit the output matrix, e.g. if the matrix formula contains a range that should be hidden, do the following:

N.B.: When you adjust the matrix range, the matrix formula will not automatically be adjusted. You are only changing the range in which the result will appear.

By holding down the Ctrl key, you can create a copy of the matrix formula in the given range.

MUNIT

Returns the unitary square matrix of a certain size. The unitary matrix is a square matrix where the main diagonal elements equal 1 and all other matrix elements are equal to 0.

Syntax

MUNIT(Dimensions)

Dimensions refers to the size of the matrix unit.

Example

Select a square range within the spreadsheet, e.g., from A1 to E5.

Without deselecting the range, select the MUNIT function. Select the Matrix check box. Enter the desired dimensions for the matrix unit, in this case 5, and click OK.

You can also enter the =Munit (5) formula in the last cell of the selected range (i.e., E5), and enter (Shift) (Command) (Enter)(Shift) (Ctrl) (Enter).

You will now see a unit matrix with a range of A1:E5.

FREQUENCY

Indicates the frequency distribution in a one-column-matrix. The default value supply and the number of intervals or classes are used to count how many values are omitted on the single intervals.

Syntax

FREQUENCY(Data; Class)

Data represents the matrix of or reference to the set of values to be counted.

Class represents the matrix of the class set.

Example

Note the following data. Column A lists measurement values (data) in no specific order. Column B contains the upper limit you entered for the classes, in which you want to divide the data. The value of 5 in cell B1 means that the first result you want from the FREQUENCY function is the number of measured values (data) less than or equal to 5. In B2, the next class limit is 10. Therefore, the FREQUENCY function should return as the second result the number of measured values that are greater than 5 or greater than or equal to 10. In cell B6, you entered text ">25" only for your own reference.


A

B

C

1

12

5

1

2

8

10

3

3

24

15

2

4

11

20

3

5

5

25

1

6

20

>25

1

7

16



8

9



9

7



10

16



11

33



Select a single column range in which to enter the frequency (you must select one field more than the class ceiling) for values that are greater than the upper class limit. In this example, select the range C1:C6. Call up the FREQUENCY function in the Function Autopilot. Select the measurement range for Data in (A1:A11), and then a column for Class , in which you entered the class limits (B1:B6) Select the Matrix check box and click OK. You will see the frequency count in the initially selected range C1:C6.

COUNT, DCOUNT.

MDETERM

Returns the matrix determinant of an array. This function returns a value in the current cell; it is not necessary to define a range for the results.

Syntax

MDETERM(Matrix)

Matrix represents a square matrix in which the determinants are defined.

Example

MEDETERM(C3:D4) = -6.

MINVERSE, MMULT, MTRANS.

MINVERSE

Returns the inverse matrix for the matrix in an array.

Syntax

MINVERSE(Matrix)

Matrix represents a square matrix that is to be inverted.

Example

Select a square range and select MINVERSE. Select the output matrix, select the Matrix field and click OK.

INDEX, MMULT.

MMULT

Calculates the matrix product of two arrays. The number of columns for the Matrix 1 must match the number of rows for the Matrix 2. The square matrix has an equal number of rows and columns.

Syntax

MMULT(Matrix 1; Matrix 2)

Matrix 1 represents the first matrix used in the matrix product.

Matrix 2 represents the second matrix with the same number of rows.

Example

Select, for example, a square range. Choose the MMULT function. Select Matrix 1, and then Matrix 2. Using Function Autopilot, select the Matrix field. Click OK. The output matrix will appear in the first selected range.

MDETERM, MINV, MTRANS.

MINVERSE

Transposes the rows and columns of a matrix array.

Syntax

MTRANS(Matrix)

Matrix represents the matrix in the spreadsheet that is to be transposed.

Example

In the spreadsheet, select the range in which the transposed matrix will appear. If the original matrix has n rows and m columns, your selected range must have at least n rows and m columns. Then enter the formula directly, select the original matrix and press (Shift) (Command) (Enter)(Shift) (Ctrl) (Enter) If you are using the Function Autopilot, select the Matrix check box. The transposed matrix will appear in the selected target range and will be protected automatically against changes.

MDETERM, MINV, MMULT.

LINEST

Returns the parameters of a linear trend.

Syntax

LINEST(Y Data; X Data; Line Type; Stats)

Y Data represents the Y Data matrix.

X Data (optional) represents the X Data matrix.

Line Type (optional). If the line goes through the zero point, then set Line Type = 0.

Stats (optional). If Stats=0, only the regression coefficient is to be calculated. Otherwise, you will see other stats.

Example

This function returns a matrix (it therefore should be handled in the same way as the other matrix functions, as described in the introduction of this section.) Select a range in which the answers will appear. Select the function. Select Y Data. If desired, you can enter other parameters. Select Matrix and click OK.

The results returned by the system (if Stats = 0), will at least show the slope of the regression line and its intersection with the Y axis. If Stats do not equal 0, other results are to be displayed.

Other LINEST Results:

Examine the following examples:


A

B

C

D

E

F

G

1

x1

x2

y


LINEST value



2

4

7

100


4,17

-3,48

82,33

3

5

9

105


5,46

10,96

9,35

4

6

11

104


0,87

5,06

#NA

5

7

12

108


13,21

4

#NA

6

8

15

111


675,45

102,26

#NA

7

9

17

120





8

10

19,02

133






Column A contains several X1 values, column B several X2 values and column C the Y values. You have already entered these values in your spreadsheet. You have now set up E2:G6 in the spreadsheet and activated the Function Autopilot. For the LINEST function to work you must have selected the Matrix check box in the Function Autopilot. Next, on page 2 of the Function Autopilot, select the following values in the spreadsheet (or enter them using the keyboard):

Y Data is C2:C8

X Data is A2:B8

Line Type and Stats are both set to 1.

As soon as you click OK, StarOffice Calc will fill the above example with the visible LINEST values.

The formula in the Formula Bar corresponds to each cell of the LINEST matrix {=LINEST(C2:C8;A2:B8;1;1)}

This represents the calculated LINEST values:

E2 and F2: Slope m of the regression line y=b+m*x for the x1 and x2 values.

G2: Intersection b with the y axis.

E3 and F3: the standard error of the slope value.

G3: The standard error of the intercept

E4: RSQ

F4: The standard error of the regression calculated for the Y value.

E5: The F value, which can be further evaluated using FDIST.

F5: The degrees of freedom for the F test.

E6: The sum of the squared deviation of the estimated Y values from their linear mean.

F6: The sum of the squared deviation of the estimated Y value from the given Y values.

LOGEST, TREND, GROWTH.

LOGEST

This function calculates the adjustment of the entered data as a exponential regression curve (y=b*m^x).

Syntax

LOGEST(Y Data; X Data; Function Type; Stats)

Y Data represents the Y Data matrix.

X Data (optional) represents the X Data matrix.

Function Type (optional). If Type = 0, functions in the form y = m^x will be calculated. Otherwise, y = b*m^x functions will be calculated.

Stats (optional). If Stats=0, only the regression coefficient is calculated.

Example

See LOGEST. However, no square sum will be returned.

LOGEST, TREND, GROWTH.

PRODSUM

Multiplies corresponding components in the given arrays, and returns the sum of those products.

Syntax

SUMPRODUCT(Matrix 1; Matrix 2...Matrix 30)

Matrix 1, Matrix 2...Matrix 30 represents matrices whose arguments are to be multiplied.

Example

=SUMPRODUCT(C3;D6;D8;D3) returns 270.

MMULT, PRODUCT, SUM.

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays.

Syntax

SUMX2MY2(Matrix X; Matrix Y)

Matrix X represents the first matrix whose elements are to be squared and added.

Matrix Y represents the second matrix whose elements are to be squared and subtracted.

Example

=SUMX2MY2(D6; D8) returns -27.

SUMPRODUCT, SUMX2PY2, SUMXMY2.

SUM2PY2

Returns the sum of the sum of squares of corresponding values in two arrays.

Syntax

SUMX2PY2(Matrix X; Matrix Y)

Matrix X represents the first matrix whose arguments are to be squared and added.

Matrix Y represents the second matrix, whose elements are to be added and squared.

Example

=SUMX2PY2(D6;D8) returns 45.

SUMPRODUCT, SUMX2MY2, SUMXMY2.

SUMXMY2

Adds the squares of the variance between corresponding values in two arrays.

Syntax

SUMXMY2(Matrix X; Matrix Y)

Matrix X represents the first matrix whose elements are to be subtracted and squared.

Matrix Y represents the second matrix, whose elements are to be subtracted and squared.

Example

=SUMX2MY2(D6;D8) returns 45.

SUMPRODUCT, SUMX2MY2, SUMX2PY2.

TREND

Returns values along a linear trend.

Syntax

TREND(Y Data; X Data; New X Data; Line Types)

Y Data represents the Y Data matrix.

X Data (optional) represents the X Data matrix.

New X Data (optional) represents the matrix of the X data, which are used for recalculating values.

Line Type(Optional). If Type = 0, then lines will be calculated through the zero point. Otherwise, offset lines will also be calculated. The default is Type <> 0.

Example

Select a spreadsheet range in which the trend data will appear. Select the function. Enter the output data or select it with the mouse. Select the Matrix field. click OK. The trend data calculated from the output data is displayed.

LINEST, LOGEST, GROWTH.

VARIATION

Calculates the points of an exponential trend in a matrix.

Syntax

Growth(Y Data;X Data; New X Data; Line Types)

Y Data represents the Y Data matrix.

X Data (optional) represents the X Data matrix.

New X Data (optional) represents the X data matrix, in which the values are recalculated.

Function Type(optional). If Type = 0, functions in the form y = m^x will be calculated. Otherwise, y = b*m^x functions will be calculated.

Example

This function returns a matrix (it therefore should be handled in the same way as the other matrix functions, as described in the introduction of this section.) Select a range in which the answers are to appear. Select the function. Select Y Data. If desired, you can enter other parameters. Select Matrix and click OK.

LINEST, LOGEST, TREND.


Category: Statistics


Here, you obtain the following functions belonging to the Statistics category, with examples:

INTERCEPT, COUNT, COUNT2, B, RSQ, BETAINV, BETADIST, BINOMDIST, CHIINV, CHITEST, CHIDIST, EXPONDIST,

FINV, FISHER, FISHERINV, FTEST, FDIST, GAMMAINV, GAMMALN, GAMMADIST, GAUSS, GEOMEAN, TRIMEAN, GTEST, HARMEAN, HYPGEOMDIST,

LARGE, SMALL, CONFIDENCE, CORREL, COVAR, CRITBINOM, KURT, LOGINV, LOGNORMDIST,

MAX, MAXA, MEDIAN, MIN, MINA, AVEDEV, AVERAGE, AVERAGEA, MODE, NEGBINOMDIST, NORMINV, NORMDIST, PEARSON, PHI, POISSON, PERCENTILE, PERCENTRANK, QUARTILE,

RANK, SKEW, FORECAST, STDEV, STDEVA, STDEVP, STDEVPA, STANDARDIZE, NORMINV, NORMDIST, SLOPE, STEYX, DEVSQ,TINV, TTEST, TDIST, VAR, VARA, VARP, VARPA, PERMUT, PERMUTATION2, PROB, WEIBULL.

The subsequent subsections describe the various functions.

Certain examples can only be illustrated with spreadsheets. For those examples, the following spreadsheets will be used:


C

D

2

x value

y value

3

-5

-3

4

-2

0

5

-1

1

6

0

3

7

2

4

8

4

6

9

6

8


In the following subsections you will find descriptions of the statistical functions arranged in the groups in which they are listed above in blue.







Category: Spreadsheet


In the following, the available functions for the Spreadsheet category are described with an example. In detail, these functions are: ADDRESS, AREAS, CHOOSE, COLUMN, COLUMNS, DDE, ERRORTYPE, HLOOKUP, INDEX, INDIRECT, LOOKUP, MATCH, OFFSET, ROW, ROWS, SHEET, SHEETS, STYLE, and VLOOKUP. See the following descriptions.

ADDRESS

Returns a cell address (reference) as text, according to the specified row and column numbers. Optionally, you can determine whether the address is interpreted as an absolute address (e.g., $A$1) or relatively (as A1) or in a mixed form (A$1 or $A1). You can also specify the name of the sheet here.

Syntax

ADDRESS(row_num; column_num; abs_num;sheet_text)

row_num represents the row number for the cell reference

column_num represents the column number for the cell reference (the number, not the letter)

abs_num determines the type of reference:

1 or empty: absolute ($A$1)

2: row reference type is absolute; column reference is relative (A$1)

3: row (relative); column (absolute) ($A1)

4: relative (A1)

sheet_text represents the name of the sheet.

Example:

ADDRESS(1; 1; 2; Sheet2) returns the following: Sheet2.A$1

Use this function, for instance, in cell B2. If the cell A1 in sheet 2 contains the value -6, you can refer indirectly to the referenced cell using the function in B2 by entering =ABS(INDIRECT(B2)). The result is the absolute value of the cell reference specified in B2, which in this case is 6.

COLUMN, ROW.

AREAS

Returns the number of areas in a reference. An area can be a range of contiguous cells or a single cell.

Syntax

AREAS(Reference)

Reference represents the reference to a cell or cell range.

Example

=AREAS(A1:B3;F2;G1) returns 3, as it is a reference to three cells and/or areas.

=AREAS(All) returns 1 if you have defined an area under Data - Define... named All.



References to deleted sections (for example, referenced tables or columns) can be imported and exported by StarOffice.


ADDRESS, INDEX, COLUMN, COLUMNS, ROW, ROWS.

DDE

Returns the result of a DDE-based link. If the contents of the referenced area changes, the value returned by the formula will also change.

Syntax

DDE(server;file;range;mode)

Server is the name of a server application. StarOffice consists of the following server applications:

File is the complete file name, including path specification.

Range is the area containing the data to be evaluated.

Mode is an optional parameter that controls the method by which the DDE server converts its data into numbers. (This is a new parameter. If you open the table in an earlier version of StarOffice Calc you should skip this parameter.)

Mode

Effect

0 or missing

Number format from the "Standard" cell style

1

Data are always interpreted in the standard format for US English

2

Data are retrieved as text; no conversion to numbers


Example

=DDE("soffice";"c:\office5\document\data1.sdc";"sheet1.A1") reads the contents of cell A1 in sheet1 of the StarOffice Calc spreadsheet data1.sdc.

=DDE("soffice";"c:\office5\document\motto.sdw";"Today's motto") returns a motto in the cell containing this formula. First, you must enter a line in the motto.sdw document containing the motto text and define it as the first line of a section named Today's Motto (in StarOffice Writer under Insert - Section...). If the motto is modified (and saved) in the StarOffice Writer document, the motto is updated in all StarOffice Calc cells in which this DDE link is defined.

ERRORTYPE

Returns the number corresponding to an error value occurring in a different cell. With the aid of this number, you can generate an error message text.


The Status bar has the predefined error code from StarOffice if you click the cell containing the error.


Syntax

ERRORTYPE(Reference)

Reference contains the address of the cell in which the error occurs.

Example

If cell A1 displays Err:518, the function =ERRORTYPE(A1) returns the number 518.

ISREF, ISERR, ISERROR, ISFORMULA. ISNONTEXT, ISBLANK, ISLOGICAL, ISNA, ISTEXT, ISNUMBER.

INDEX

INDEX returns the content of a cell, specified by row and column number (or an optional area name).

Syntax

INDEX(reference;row_num;column_name;area_num)

Reference is a cell reference, entered either directly or by specifying an area name. If the reference consists of multiple defined areas, you must enclose the reference or area name in parentheses.

row_num (optional) represents the row number of the Reference area, for which to return a value.

column_num (optional) represents the column number of the Reference area, for which to return a value.

area_num (optional) represents the area from which to return the row and column intersection.

Example

=INDEX(Prices;4;1) returns the value from row 4 and column 1 of the database area defined via Data - Define... as Prices.

=INDEX(SumX;4;1) returns the value from the area SumX in row 4 and column 1 as defined via Insert - Names - Define....

=INDEX((Row and Column);4;1) indicates the value contained in row 4 and column 1 of the row and column intersection named, which you named under Insert - Names - Set... as Row and Column . The row and column intersection may consist of several rectangular cell areas, each with a row 4 and column 1. If you now want to call the second block of this row and column intersection enter "2" as a final Area parameter.

=INDEX(A1:B6;1,1) indicates the value in the upper-left of the A1:B6 area.

=INDEX ((A1:B6;D5:E9) ;4;1)

VLOOKUP, MATCH, LOOKUP, CHOOSE, HLOOKUP.

INDIRECT

Returns the reference specified by a text string. This function can also be used to return the area of a corresponding string.

Syntax

INDIRECT(ref_text)

ref_text represents a reference to a cell or an area (in text form) for which to return the contents.

Example

=INDIRECT(A1) equals 100 if A1 contains C108 as a reference and cell C108 contains a value of 100.

=SUM(INDIRECT("a1:" & ADDRESS(1;3))) totals the cells in the area of A1 up to the cell, whose address is defined by row 1 and column 3. Therefore, area A1:C1 is totaled.

OFFSET.

COLUMN

Returns the column number of a cell reference. if the reference is a cell the column number of the cell is returned; if the parameter is a cell area, the corresponding column numbers are returned in a single-row matrix if the formula is entered as a matrix formula. If the COLUMN function with an area reference parameter is not used for a matrix formula, only the column number of the first cell within the area is determined.

Syntax

COLUMN(reference)

Reference is the reference to a cell or cell area whose first column number is to be found.

If no reference is entered, the column number of the cell in which the formula is entered is found. StarOffice Calc automatically sets the reference to the current cell.

Example

=COLUMN(A1) equals 1. Column A is the first column in the table.

=COLUMN(C3:E3) equals 3. Column C is the third column in the table.

=COLUMN(D3:G10) returns 4 because column D is the fourth column in the table and the COLUMN function is not used as a matrix formula. (In this case, the first value of the matrix is always used as the result.)

{=COLUMN(B2:B7)} and =COLUMN(B2:B7) both return 2 because the reference only contains column B as the second column in the table. (Because single-column areas have only one column number, it does not make a difference whether or not the formula is used as a matrix formula.)

=COLUMN() returns 3 if the formula was entered in column C.

{=COLUMN(Rabbit)} returns the single-row matrix (3, 4) if "Rabbit" is the named area (C1:D3).


The COLUMN function is not compatible with earlier versions of StarOffice. If you load older StarOffice Calc documents please note that COLUMN() formerly returned the results in a vertical single-column m x 1 matrix instead of in a horizontal single-row 1 x n matrix.


COLUMNS, ROWS.

COLUMNS

Returns the number of columns in the given reference.

Syntax

COLUMNS(Reference)

Reference is the reference to a cell or cell area whose total number of columns is to be found.

Example

=Columns(B5) returns 1 because a cell only contains one column.

=COLUMNS(A1:C5) equals 3. The reference comprises three columns.

=COLUMNS(Rabbit) returns 3 if "Rabbit" is the named area (C1:D3).


Starting with StarOffice 5.0, the COLUMNS function shows a different interpreting behavior than it did in older versions if you submit an internally generated matrix for a parameter. If you load older StarOffice Calc documents and have used such parameters, note that COLUMNS() now returns different results (no longer the number of rows but rather the number of columns) in these cases.


COLUMN, ROWS.

VLOOKUP

Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of a matrix. The function then returns the value to the same line of a specific matrix column named by index.

Syntax

=VLOOKUP(lookup_value;table_array;col_index;sort order)

Search Criterion is the value searched for in the first column of the matrix.

Matrix is the reference, which is to comprise at least two columns.

Index is the number of the column in the matrix that contains the value to be returned. The first column has the number 1.

Sorted is an optional parameter that indicates whether the first column in the matrix is sorted in ascending order (this is the default). Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly (if it is between the lowest and highest value of the sorted list). In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

Example

Say you want to enter the number of a dish on the menu (in cell A1), and the name of the dish is to appear as text in the neighboring cell (B1) immediately. The Number to Name assignment is contained in the D1:E100 matrix. D1 contains 100, E1 contains the name Vegetable Soup, and so forth, for 100 menu items. The numbers in column D are sorted in ascending order; thus, the optional Sorted parameter is not necessary.

Enter the following formula in B1:

=VLOOKUP(A1; D1:E100; 2)

As soon as you enter a number in A1 B1 will show the corresponding text contained in the second column of reference D1:E100. Entering a nonexistent number displays the text with the next number down. To prevent this, enter FALSE as the last parameter in the formula so that an error message is generated when a nonexistent number is entered.

INDEX, MATCH, LOOKUP, HLOOKUP.

Sheet

Returns the sheet number of a reference or a string representing a sheet name. If you do not enter any parameters, the result is the sheet number of the spreadsheet containing the formula.

Syntax

Table(Reference) or Table(String)

Reference is optional and is the reference to a cell, an area, or a table name string.

Example

=TABLE(Table2.A1) returns 2 if Table2 is the second table in the sequence.

Sheets

Determines the number of sheets in a reference. If you do not enter any parameters, it returns the number of sheets in the current document.

Syntax

Tables(Reference)

Reference is the reference to a table or an area. This parameter is optional.

Example

=TABLES(Table1.A1:Table3.G12) returns 3 if Table1, Table2, and Table3 exist in the sequence indicated.

=TABLES(Table1.A1:B12;Table3.A1:B12) returns 3 if Table1, Table2, and Table3 exist in the sequence indicated.

MATCH

Returns the relative position (as number) of an item in an array that matches a specified value in a ceratin order. The function returns the position of the value found in the search matrix as a number. If the value is found several times the function returns an error.

Syntax

MATCH(lookup_value;lookup_array;match_type)

Search Criterion is the value which is to be searched for in the single-row or single-column matrix.

Search Matrix is the reference searched. A search matrix can be a single row or column, or part of a single row or column.

Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, the first column of the search matrix must be sorted in ascending order. If Type = -1 it must be sorted in descending order. If Type = 0 the column is not sorted (in this case, only exact matches are searched for, and if none are found an error is generated; see VLOOKUP).

Example

=LOOKUP(200; D1:E100) searches the area D1:E100, which is sorted by column D for instances of the value 200. As soon as this value is reached, the number of the row in which it was found is returned. If the value is not found during the search of the sorted column and a higher value is found instead the number of the previous row is returned.

INDEX, VLOOKUP, HLOOKUP.

OFFSET

Returns the value of a cell offset by a certain number of rows and columns from a given reference point.

Syntax

OFFSET(reference;rows;cols;height;width)

Reference is the cell from which the function searches for the new reference.

Rows is the number of cells by which the reference was corrected up (negative value) or down.

Columns is the number of columns by which the reference was corrected to the left (negative value) or to the right.

Height is the optional vertical height for an area that starts at the new reference position.

Width is the optional horizontal width for an area that starts at the new reference position.

Example

=OFFSET(A1; 2, 2) returns the value in cell C3 (A1 moved by two rows and two columns down). If C3 contains the value 100 this function returns the value 100.

=OFFSET(A1; 2; 2; 5; 6) specifies the area that starts in cell C3 and has a height of 5 rows and a width of 6 columns, i.e. the area C3:H7. Please note that you can also enter the parameter as references to cells.

LOOKUP

Returns the contents of a cell either from a one-row or one-column range or from an array. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted, otherwise the search will not return any usable results.

Syntax

LOOKUP(lookup_value;lookup_vector;result_vector)

Search Criterion is the value to be searched for; entered either directly or as a reference.

Search Vector is the single-row or single-column area to be searched.

Result Vector is another single-row or single-column area from which the result of the function is taken. The result is the cell of the result vector with the same index as the instance found in the search vector.

Example

=LOOKUP(A1; D1:D100;F1:F100) searches the corresponding cell in area D1:D100 for the number you entered in A1. For the instance found, the index is determined, e.g. the 12th cell in this area. Then, the contents of the 12th cell are returned as the value of the function (in the result vector).

INDEX, VLOOKUP, HLOOKUP.

STYLE

Applies a style to the cell containing the formula. After an adjustable time you can apply another style. This function returns always the 0 value, allowing you to add it to another function without changing the respective values. Together with the CURRENT function you can apply a color to a cell (independent from the corresponding value). E.g.: =...+STYLE(IF(CURRENT()>3;"red";"green")) applies red to the cell if the value is greater than 3, otherwise the style with the green name.

Syntax

STYLE(style;time;style2)

Style Name1 is the name of a cell style assigned to the cell. Style names must be entered in quotation marks.

Time is an optional time range in seconds. If this parameter is missing the style will not be changed after a certain amount of time has passed.

Style Name2 is the optional name of a cell style assigned to the cell after a certain amount of time has passed. If this parameter is missing "Standard" is entered.

Example

=STYLE("Invisible";60;"Standard") formats the cell in Transparent format for 60 seconds after the document was recalculated or loaded, then the Standard format is assigned.

CHOOSE

Uses an index to return a value from a list of up to 30 values.

Syntax

CHOOSE(Index_num;value1;...value30)

Index is a reference or number between 1 and 30 indicating which value is to be taken from the list.

Value1...Value30 is the list of values entered as a reference to a cell or as individual values.

Example

=CHOOSE(A1; B1; B2; B3; Today; Yesterday; Tomorrow;Test), for example, returns the contents of cell B2 for A1 = 2; for A1 = 4, the function returns Today; for A1 = 7, it returns the content of the first cell within the named area Test .

INDEX

HLOOKUP

Search for a value and reference to the cells below the selected area. This function verifies if the first row of a matrix contains a certain value. The function returns then the value in a row of the matrix, named in the Index , in the same column.

Syntax

=HLOOKUP(search_criteria;table_array;row_index_num;range_lookup)

See also:VLOOKUP (cols and rows are exchanged)

INDEX, VLOOKUP, MATCH, LOOKUP.

ROW

Returns the row number of a cell reference. If the reference is a cell, it returns the row number of the cell. If the reference is a cell range, it returns the corresponding row numbers in a one-column Matrix if the formula is entered as a matrix formula. If the ROW function with a range reference is not used in a matrix formula, only the row number of the first range cell will be returned.

Syntax

ROW(reference)

Reference is a cell, an area, or the name of an area.

If you do not indicate a reference, the row number of the cell in which the formula is entered will be found. StarOffice Calc automatically sets the reference to the current cell.

Example

=ROW(B3) returns 3 because the reference refers to the third row in the table.

{=ROW(D5:D8)} returns the single-column matrix (5, 6, 7, 8) because the reference specified contains rows 5 through 8.

=ROW(D5:D8) returns 5 because the ROW function is not used as matrix formula and only the number of the first row of the reference is returned.

{=ROW(A1:E1)} and =ROW(A1:E1) both return 1 because the reference only contains row 1 as the first column in the table. (Because single-row areas only have one row number it does not make any difference whether or not the formula is used as a matrix formula.)

=ROW() returns 3 if the formula was entered in row 3.

{=ROW(Rabbit)} returns the single-column matrix (1, 2, 3) if "Rabbit" is the named area (C1:D3).


The ROW function is not compatible with older versions of StarOffice. If you load older StarOffice Calc documents please note that ROW() formerly returned the results in a vertical single-column 1 x n matrix instead of in a horizontal single-row m x 1 matrix.


COLUMN, ROWS.

ROWS

Returns the number of rows in a reference or array.

Syntax

ROWS(array)

Array is the reference or named area whose total number of rows is to be determined.

Example

=Rows(B5) returns 1 because a cell only contains one row.

=ROWS(A10:B12) returns 3.

=ROWS(Rabbit) returns 2 if "Rabbit" is the named area (C1:D3).


Starting with StarOffice 5.0, the ROWS function shows a different interpreting behavior than it did in older versions if you submit an internally generated matrix for a parameter. If you load older StarOffice Calc documents and have used such parameters, note that ROWS() now returns different results in these cases (no longer the number of columns but rather the number of rows) .


COLUMNS, ROW.


Category Text


The functions belonging to the Text category will be explained by means of an example in the following. These functions are: BASE, CODE, DECIMAL, DOLLAR, REPLACE, FIXED, FIND, TRIM, UPPER, PROPER, EXACT, LOWER, LEFT, LEN, RIGHT, SEARCH, CLEAN, no-commas, MID, TEXT, CONCATENATE, SUBSTITUTE, VALUE, REPT and CHAR.

BASE

Converts a positive integer to a specified base into a text from the numbering system. The digits 0-9 and the letters A-Z will be used.

Syntax

BASE(Number; Radix[; Minimum length])

Number is a positive integer, which is to be converted.

Radix indicates the base of the number system. It may be any positive integer between 2 and 36.

Minimum Length determines the minimum length of the character sequence that has been created, and can be indicated optionally. If this parameter is passed, a text that is shorter than the indicated minimum length will be padded on the left with nulls.

Example

BASE(17;10;4) returns 0017 in the decimal system.

BASE(17;2) returns 10001 in the binary system.

BASE(255;16;4) returns 00FF in the hexadecimal system.

DECIMAL.

CODE

Returns a numeric code for the first character in a text string.

Syntax

CODE(Text)

Text refers to the text for which to return a numeric code of the first character.

Example

CODE ("Hieronymus") returns 72, CODE ("hieroglyphic") returns 104.



The code used here does not refer to ASCII, but to the code table currently loaded.


CHAR

DECIMAL

Converts a text with characters from a Number System in the base Radix into a decimal number... Radix must be placed in the range 2 till 36. Spaces and tabs are ignored, the case sensitive of Text is equivalent.

If the radix is 16, a leading x or X or 0x or 0X and an appended h or H is disregarded. If the Radix is 2, an appended b or B is disregarded. Other characters that do not belong to the number system generate an error.

Syntax

DECIMAL(Text; Radix)

Text is the text that is to be converted.

Radix indicates the base of the number system. It may be any positive integer between 2 and 36.

Example

DECIMAL("17";10) returns decimal 17.

DECIMAL("FACE";16) returns decimal 64106 from the number in the hexadecimal system.

DECIMAL("0101";2) returns decimal 5 from the number in the binary system

BASE.

DOLLAR

Converts a number to an amount in the currency format, rounded to a specified decimal place. In the value field enter the number to be converted to currency. Optionally, you may enter the number of decimal places in the decimals field. If no value is specified, all numbers in currency format will be displayed with two decimal places.

Syntax

DOLLAR(value; decimals)

Value is a number, a reference to a cell containing a number, or a formula which returns a number.

Decimal is the number of decimal places.

Example

Entering DOLLAR(255) in the value field returns $255.00.

Entering a value of 367.456 in the value field and 2 in the decimals field returns $367.46.

DOLLAR(A1) returns $5.00 if cell A1 contains the number 5.

FIXED, TEXT, VALUE.

REPLACE

Replaces part of a text string with a different text string. This function can be used to replace both characters and numbers (which are automatically converted to text). The result of the function is always displayed as text. If you intend to perform further calculations with a number which has been replaced by text, you will need to convert it back to a number using the VALUE function.

Any text containing numbers must be enclosed in quotation marks if you don't want it to be interpreted as a number and automatically converted to text.

Syntax

REPLACE(old_text; start_num; num_chars; new_text)

Old_text refers to text of which a part will be replaced.

Start_num refers to the position within the text where the replacement will begin.

Num_chars is the number of characters to be replaced.

New_text refers to the text which replaces old_text.

Example

REPLACE(12345.67;1;1;444) Enter 12345.67 in the old_text field, 1 in both the start_num and num_chars fields and 444 in the new_text field. The result will be 4442345.67, and the first character is replaced by the new text.

MID, SEARCH, SUBSTITUTE, TRIM.

FIXED

Here you can specify a number to be formatted with a fixed number of decimal places and a thousands separator. This function can be used to apply an uniform format to a column of numbers.

Syntax

FIXED(Number; decimals; no_commas)

Number refers to the number to be formatted.

Decimals refers to the number of decimal places to be displayed.

no_commas (optional) determines whether a thousands separator is displayed.

Example

If you enter 1234567.89 in the Number field, 3 in the decimals and nothing in the no_commas box, the result will be 1,234,567.890. Any entry in the no_commas field will result in 1234567.890.

DOLLAR, ROUND, TEXT, VALUE.

FIND

Finds one text string within another text string, and returns the number of the starting position of find_text, from the leftmost character of within_text. You may also define where to begin the search. The search term can be a single letter, a number, as well as a complete word or phrase. The search is case-sensitive.

Syntax

FIND(find_text; within_text; start_num)

Find_text refers to the text to be found.

Within_text is the text where the search takes place.

Start_num (optional) is the position in the text at which to begin the search.

Example

Enter 998877665544 in the find_text field, and 76 in within_text: the result will be 6.

IDENTICAL, LEN, MID, SEARCH.

TRIM

Removes all spaces from the string specified in text, except for single spaces between words.

Syntax

TRIM(Text)

Text refers to text in which extra spaces between words should be removed.

Example

Type ...Hello in the Text text box and the word Hello will be displayed without the ellipsis points.

CLEAN, REPLACE, MID, SUBSTITUTE.

UPPER

Converts the string specified in the Text field to uppercase.

Syntax

UPPER(Text)

Text refers to the text in which lower case letters should be converted to capitals.

Example

Enter Good Morning in the Text field and the result will be GOOD MORNING.

LOWER, PROPER.

PROPER

Capitalizes the first letter in a text string and all other letters after each space.

Syntax

PROPER(Text)

Text refers to the text in which the first letters of words should be replaced with a capital.

Example

Enter have fun working with StarOffice Calc in the Text text box and the result will be Have Fun Working With StarOffice Calc.

UPPER, LOWER.

EXACT

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. This function is case-sensitive.

Syntax

IDENTICAL(text 1;text 2)

Text 1 refers to the first text to compare.

Text 2 is the second text to compare.

Example

A comparison between StarOffice and Staroffice returns a value of FALSE.

LEN, SEARCH.

LOWER

Converts all uppercase letters in a text string to lowercase.

Syntax

LOWER(Text)

Text refers to the text in which capital letters should be replaced by lower case letters.

Example

Enter StARoFFice cALc in the Text text box and the result will be StarOffice Calc.

PROPER, UPPER.

LEFT

Returns the first (or leftmost) character or characters (specified in the text field) in a string. Optionally, you can specify the amount of single characters in the num_chars field. This function is useful if you want to automatically delete extra characters from a string.

Syntax

LEFT(text; num_chars)

Text is the text for which to determine the first character or characters.

Num_chars (optional) specifies the number of characters to return.

Example

Enter output in the Text field and 3 in the num_chars field. The result will be "out."

MID, RIGHT.

LEN

Returns the length of a string specified in the Text field. Any blank spaces in the string will be counted as well.

Syntax

LEN(Text)

Text is the text whose length is to be determined.

Example

Entering Hello returns a result of 5.

Entering 12345.67 returns a value of 8.

IDENTICAL, SEARCH.

RIGHT

Returns the last (or rightmost) character or characters (specified in num_chars) in a text string.

Syntax

RIGHT(text; num_chars)

Text is the text string containing the characters to extract.

Num_chars (optional) is the number of character to be extracted from the end of the string.

Example

Enter the string Roast Turkey and 3 in the num_chars field. The result is key.

LEFT, MID.

SEARCH

Returns the number of the character at which a specific character or text string (find_text) is first found (in within_text), reading from left to right. Optionally, you can also define the point at which to begin the search. The search text may consist of a single letter, a number, or any word or phrase. The search is not case-sensitive.

Syntax

SEARCH(find_text; within_text; start_num)

Find_text is the text to be searched for.

Within_text is the text where the search will take place.

Num_chars (optional) is the position in the text where the search will start.

Example

Enter 998877665544 in the within_text field and 54 in the find_text field. The result is 10.

FIND, MID, REPLACE, SUBSTITUTE.

CLEAN

Removes all nonprintable characters from a string entered in the Text field.

Syntax

CLEAN(Text)

Text refers to the text from which to remove all non-printable characters.

Example

Entering (non-printable character)Hello produces Hello.

CHAR, TRIM.

T

This function converts a number to a blank text string.

Syntax

T(Value)

Value is the value to be converted. Also a reference can be sent as a parameter. If the the referenced cell includes a number or a formula containing a numerical result, the result will be an empty string.

Example

The number 12345 becomes text "". The text "12345" remains unchanged.

N, VALUE.

MID

Returns a specific number of characters from a string entered in the Text field, starting at the position you specify.

Syntax

MID(text; start_num; num_chars)

Text is the text containing the characters to extract.

Start_num is the position of the first character in the text to extract.

Num_chars specifies how many characters to return.

Example

If you enter Autopilot in the Text field, 3 in the start_num field and 3 again in the num_chars field, the result will be top.

CODE, FIND, LEFT, RIGHT, SEARCH.

TEXT

Converts a value (specified in the number field) to text in a specific number format.

Syntax

TEXT(Number; Format)

Number is the numerical value to be converted.

Format is the text which defines the format. (The language set in the cell format will always be the standard for the output.)

Example

Enter 54321 in the Number field and "00.00" (with quotation marks) in the Format field and the result will be 54321.00.

DOLLAR, FIXED, T, VALUE.

CONCATENATE

Combines several text passages into one string.

Syntax

CONCATENATE(text 1; text 2; ...)

Text 1; text 2; ... represent up to 30 text passages which are to be combined into one string.

Example

Enter "Hello" "Ms" "Smith" as separate text elements. The result after concatenating is Hello Ms Smith.

SUBSTITUTE

Substitutes new_text for search_text in a text string. In the occurence field you can define how many times to copy the text.

Syntax

SUBSTITUTE(text; search_text; new_text; occurence)

Text is the text containing text to be substituted.

Search_text is the text to be replaced (multiple times, if desired).

New_text is the text used as the replacement.

Occurrence (optional) refers to the number of occurrences in which the text is to be replaced.

Example

Enter 1234567890 in the text field, 3456789 in the search_text field, 9876543 in new_text and 1 as the occurrence. The result will be 1298765430.

REPLACE, TRIM.

VALUE

Converts text entered in the Text field to a number. For example, if you concatenate the text elements 123 and 456, VALUE returns the number 123456 as the result.

Syntax

VALUE(text)

Text is the text to be converted to a number.

Example

The entry "4321" produces 4321.

DOLLAR, FIXED, TEXT.

REPEAT

Repeats a string specified in the Text field a given number of times (num_times).

Syntax

REPT(text; num_times)

Text is the text to be repeated.

Num_times is the number of repeats.

The result can be a maximum of 255 characters.

Example

Enter Have a nice day as the text and 2 in the num_times field. The result is Have a nice dayHave a nice day.

CHAR

Returns a character corresponding to the code specified in the Number field. Code numbers may be two or three digit natural numbers. This function allows you to display characters which cannot be entered directly via the keyboard, as long as you know the code number.

Syntax

CHAR(Number)

Number is a number between 1 and 255 representing the code value for the character.

Letters and other characters can also be entered via the keyboard by pressing (ALT) while you enter the character code on the number block.

Example

The entry 100 returns d.

The entry 75 returns K.

CODE


Category: Add-In


Here you can use additional Add-In functions.

The AddIn functions involve additional functions that enhance StarOffice Calc. They are provided either with StarOffice API (see below) or Shared LibrariesExternal DLLs, which you simply have to copy into your StarOffice directory. Create a new directory called "addin" and set the path under Tools - Options.... - General - Paths - AddIns. In default settings, the path will be "C:\Office52\program\addin" if you installed StarOffice on drive C. In the AddIn directory, copy the files that you received from the AddIn manufacturer and restart StarOffice.


If you use an operating system that differentiates between uppercase and lowercase characters, then make sure you create a new directory where the "addin" directory is written in lowercase and StarOffice can recognize it.

When installing StarOffice, you should not create a new directory if you specified that the addins be included. In this case, you will find that your Office directory will already contain a directory called "addin".


When starting your computer, StarOffice searches for the Shared Libraryappropriate DLL under Tools - General - Paths - AddIns. After finding LibraryDLL, the AddIn category will appear in Function AutoPilot, for the Shared LibraryExternal DLL, allowing you to select ready-to-run functions that can be used for your work in StarOffice Calc.

You will also find adescription of the StarOffice Calc-AddIn-Interface in the Help. In addition, important functions and their parameters are described in the Help for the Shared LibraryStarOffice Calc-AddIn-DLL.

Standard Sample AddIns

StarOffice comes with two samples for StarOffice Calc's addin interface. If you have installed the addins, you will find a subdirectory named "addin" under your Office directory, in which Shared Librariestwo DLLs for StarOffice Calc are found. These contain the functions for the Function AutoPilot found under the AddIn category. They are simple sample functions, which you can configure by inserting new or availableShared LibrariesDLLs to meet your own needs.

Furthermore, a folder named "source" is found under the AddIn directory. This folder contains the source code for the Shared LibrariesDLLs and provides interesting programming examples in an overview of the AddIn-Shared LibraryDLL functions.

The source code supports German and English. The current language does not allow you to query the AddIn interface, therefore the Shared LibrariesDLLs will be language-dependent when installing StarOffice. When installing a German version, you will have the DLLs "Rot50049.SODLL" and "Dfa50049.SODLL". The following sections describe the AddIn functions that will be included in the Shared LibrariesDLLs.

The "Rot50049.SODLL" provides an encryption function called ROT13, which is based on a simple algorithm. These types of encryption algorithms were used in original cryptography

"Dfa50049.SODLL" provides seven additional date functions for date calculations: ISLEAPYEAR, YEARS, MONTHS, DAYSINYEAR, DAYSINMONTH, WEEKS, WEEKSINYEAR.


You can use the available "Dfa50049.SODLL" functions if Tools - Options - Spreadsheet Document... - Calculate in the Date section of the StarOffice default settings has been activated with 30.12.1899 set as the starting point on the timeline.


ISLEAPYEAR

Determines whether a year is a leap year. If yes, the function will return the value 1 (TRUE); if not, it will return 0 (FALSE).

Syntax

ISLEAPYEAR(Date)

Date: Specifies whether a given date falls within a leap year.

Example

ISLEAPYEAR(29.02.68) returns 1.

YEARS

Calculates the difference in years between two dates.

Syntax

YEARS(Date1, Date2, Type)

Date1: First date
Date2: Second date
Type: Calculates the type of difference. Possible values are 0 (Interval) and 1 (in calendar years).

MONTHS

Calculates the difference in months between two dates.

Syntax

MONTHS(Date1, Date2, Type)

Date1: First date
Date2: Second date
Type: Calculates the type of difference. Possible values include 0 (Interval) and 1 (in calendar months).

ROT13

Encrypts a character string by moving the characters 13 positions in the alphabet. After the letter Z, the alphabet begins again (Rotation). By applying the encryption function again to the resulting code, you can decrypt the text.

Syntax

ROT13(Text)

Text: Enter the character string to be encrypted. ROT13(ROT13(Text)) decrypts the Code.

Examples

ROT13("This is a secret code") returns "Qnf vfg rva trurvzre Pbqr".

ROT13("Qnf jne rva trurvzre Pbqr") returns "This was a secret code".

DAYSINYEAR

Calculates the number of days in the year until a given date.

Syntax

DAYSINYEAR(Date)

Date: The date until which a number of days in the year will be calculated.

Example

DAYSINYEAR(29.02.68) returns 366 days.

DAYSINMONTH

Calculates the number of days in a month until a certain date.

Syntax

DAYSINMONTH(Date)

Date: The date until which the days in the month will be calculated.

Example

DAYSINMONTH(17.02.68) returns 29 days.

WEEKS

Calculates the difference in weeks between two dates.

Syntax

WEEKS(Date1, Date2, Type)

Date1: First date
Date2: Second date
Type: Calculates the type of difference. The possible values are 0 (interval) and 1 (in numbers of weeks).

WEEKSINYEAR

Calculates the number of weeks in a year until a certain date. The number of weeks is defined as follows: a week that spans two years is added to the year in which most days of that week occur.

Syntax

WEEKSINYEAR(Date)

Date: The date until which the weeks in the year will be calculated.

Example

WEEKSINYEAR(17.02.70) returns 53.

Addins via StarOffice API

AddIns can also be implemented via the StarOffice API. You have to define a Service supporting com::sun::star::sheet::AddIn and register an implementation of it. You can get more information about StarOffice API documentation for advanced users by requesting it.


AddIn for programming in StarOffice Calc


An AddIn can be attached to StarOffice Calc, which is an external programming module providing additional functions for working with spreadsheets. These are displayed in the Function AutoPilot in the AddIn category. If you would like to program such an AddIn yourself, you can learn here which functions must be exported by the Shared Libraryexternal DLL so that the AddIn can be successfully attached.

StarOffice searches the module path Tools - Options... - General - Paths for modules with an "addin" subdirectory, and examines this subdirectory for a usable Shared LibraryDLL. In order to have this DLL detected by StarOffice, it must show certain characteristics, which are detailed below. This information allows you to program an adequate AddIn for the function AutoPilot in StarOffice Calc.


Included in the program package are two samples of an AddIn interface of StarOffice Calc. If you indicated during installation of the program that AddIns are also to be installed, an "addin" directory is included in the Office directory, which contains sample DLLs as well as an additional "source" subdirectory listing the source code.

A description of the AddIn functions provided by the sample DLLs can be found in StarOffice Help about the Function AutoPilot in the AddIn category.


The AddIn concept

Each AddIn library provides several functions. Some functions are used for management purposes and must follow a certain convention. Through these functions information about other functions is available, which make up the extension of StarOffice Calc. You can choose any name for these functions. However, they must also follow certain rules regarding parameter passing. The exact naming and calling conventions vary for different platforms. Under Windows, the Pascal conventions are used, among others, because Pascal is still widely common.

Functions of Shared LibraryAddIn DLL

At a minimum, the management functions GetFunctionCount and GetFunctionData must exist. Using these, the functions as well as parameter types and return values can be determined. For return values, the Double and String types are supported. For additional parameters, the cell areas Double Array, String Array, and Cell Array are supported.

Parameters are passed using references. Therefore, a change of these values is basically possible. However, this is not supported in StarOffice Calc because it does not make sense within spreadsheets.

Libraries can be reloaded during load time and their contents can be analyzed using the two management functions. For each function, information about parameter number and type, application internal and real names as well as management numbers is available.

The functions are called synchronously and return their results immediately. Real time functions (asynchronous functions) are also possible; however, they are not explained in detail because of their complexity.

General information about the interface

The maximum number of parameters in an AddIn function attached to StarOffice Calc is 16: one result value and a maximum of 15 input function parameters.

The data types are defined as follows:

Data types

Definition

CALLTYPE

Under Windows: FAR PASCAL (_far _pascal)
other: default (operating system specific default)

USHORT

2 Byte unsigned Integer

double

8 byte platform-dependent format

Paramtype

platform-dependent like int

PTR_DOUBLE =0 pointer to a double
PTR_STRING =1 pointer to a zero-terminated string
PTR_DOUBLE_ARR =2 pointer to a double array
PTR_STRING_ARR =3 pointer to a string array
PTR_CELL_ARR =4 pointer to a cell array
NONE =5


Shared LibraryDLL functions

Following you will find a description of those functions, which are called at the Shared Libraryexternal DLL.

For all Shared LibraryDLL functions applies:

void CALLTYPE fn(out, in1, in2, ...)

Output: Resulting value
Input: Any number of types (double&, char*, double*, char**, Cel area), where the Cell area is an array of types double array, string array, or cell array.

GetFunctionCount()

Returns the number of functions without the management functions of the reference parameter. Each function has a unique number between 0 and nCount-1. This number will be needed for the GetFunctionData and GetParameterDescription functions later.

Syntax

void CALLTYPE GetFunctionCount(USHORT& nCount)

Parameter

USHORT &nCount:
Output: Reference to a variable, which is supposed to contain the number of AddIn functions. E.g.: If the AddIn provides 5 functions for StarOffice Calc, then nCount=5.

GetFunctionData()

Determines all the important information about an AddIn function.

Syntax

void CALLTYPE GetFunctionData(USHORT& nNo, char* pFuncName, USHORT& nParamCount, Paramtype* peType, char* pInternalName)

Parameter

USHORT& nNo:
Input: Function number between 0 and nCount-1, inclusively.

char* pFuncName:
Output: Function name as seen by the programmer, as it is named in the Shared LibraryDLL. This name does not determine the name used in the function AutoPilot.

USHORT& nParamCount:
Output: Number of parameters in AddIn function. This number must be greater than 0, because there is always a result value; the maximum value is 16.

Paramtype* peType:
Output: Pointer to an array of exactly 16 variables of type Paramtype. The first nParamCount entries are filled with the suitable type of parameter.

char* pInternalName:
Output: Function name as seen by the user, as it appears in the Function AutoPilot. May contain umlauts.

The pFuncName and pInternalName parameters are char arrays, which are implemented with size 256 in StarOffice Calc.

GetParameterDescription()

Provides a brief description of the AddIn function and its parameters. As an option, this function can be used to show a function and parameter description in the function AutoPilot.

Syntax

void CALLTYPE GetParameterDescription(USHORT& nNo, USHORT& nParam, char* pName, char* pDesc)

Parameter

USHORT& nNo:
Input: Number of the function in the library; between 0 and nCount-1.

USHORT& nParam:
Input: Indicates, for which parameter the description is provided; parameters start at 1. If nParam is 0, the description itself is supposed to be provided in pDesc; in this case, pName does not have any meaning.

char* pName:
Output: Takes up the parameter name or type, e.g. the word "Number" or "String" or "Date", etc. Implemented in StarOffice Calc as char[256].

char* pDesc:
Output: Takes up the description of the parameter, e.g. "Value, at which the universe is to be calculated." Implemented in StarOffice Calc as char[256].

pName and pDesc are char arrays; implemented in StarOffice Calc with size 256. Please note that the space available in the Function AutoPilot is limited and that the 256 characters cannot be fully used.

Cell areas

The following tables contain information about which data structures must be provided by an external program module in order to pass cell areas. StarOffice Calc distinguishes between three different arrays, depending on the data type.

Double Array

As a parameter, a cell area with values of the Number/Double type can be passed. A double array in StarOffice Calc is defined as follows:

Offset

Name

Description

0

Col1

Column number in the upper-left corner of the cell area. Numbering starts at 0.

2

Row1

Row number in the upper-left corner of the cell area; numbering starts at 0.

4

Tab1

Table number in the upper-left corner of the cell area; numbering starts at 0.

6

Col2

Column number in the lower-right corner of the cell area. Numbering starts at 0.

8

Row2

Row number in the lower-right corner of the cell area; numbering starts at 0.

10

Tab2

Table number in the lower-right corner of the cell area; numbering starts at 0.

12

Count

Number of the following elements. Empty cells are not counted or passed.

14

Col

Column number of the element. Numbering starts at 0.

16

Row

Row number of the element; numbering starts at 0.

18

Tab

Table number of the element; numbering starts at 0.

20

Error

Error number, where the value 0 is defined as "no error." If the element comes from a formula cell the error value is determined by the formula.

22

Value

8 byte IEEE variable of type double/floating point

30

...

Next element


String Array

A cell area, which contains values of data type Text and is passed as a string array. A string array in StarOffice Calc is defined as follows:

Offset

Name

Description

0

Col1

Column number in the upper-left corner of the cell area. Numbering starts at 0.

2

Row1

Row number in the upper-left corner of the cell area; numbering starts at 0.

4

Tab1

Table number in the upper-left corner of the cell area; numbering starts at 0.

6

Col2

Column number in the lower-right corner of the cell area. Numbering starts at 0.

8

Row2

Row number in the lower-right corner of the cell area; numbering starts at 0.

10

Tab2

Table number in the lower-right corner of the cell area; numbering starts at 0.

12

Count

Number of the following elements. Empty cells are not counted or passed.

14

Col

Column number of the element. Numbering starts at 0.

16

Row

Row number of the element; numbering starts at 0.

18

Tab

Table number of the element; numbering starts at 0.

20

Error

Error number, where the value 0 is defined as "no error." If the element comes from a formula cell the error value is determined by the formula.

22

Len

Length of the following string, including closing zero byte. If the length including closing zero byte equals an odd value a second zero byte is added to the string so that an even value is achieved. Therefore, Len is calculated using ((StrLen+2)&~1).

24

String

String with closing zero byte

24+Len

...

Next element


Cell Array

Cel arrays are used to call cell areas containing text as well as numbers. A cell array in StarOffice Calc is defined as follows:

Offset

Name

Description

0

Col1

Column number in the upper-left corner of the cell area. Numbering starts at 0.

2

Row1

Row number in the upper-left corner of the cell area; numbering starts at 0.

4

Tab1

Table number in the upper-left corner of the cell area; numbering starts at 0.

6

Col2

Column number in the lower-right corner of the cell area. Numbering starts at 0.

8

Row2

Row number in the lower-right corner of the cell area; numbering starts at 0.

10

Tab2

Table number in the lower-right corner of the cell area; numbering starts at 0.

12

Count

Number of the following elements. Empty cells are not counted or passed.

14

Col

Column number of the element. Numbering starts at 0.

16

Row

Row number of the element; numbering starts at 0.

18

Tab

Table number of the element; numbering starts at 0.

20

Error

Error number, where the value 0 is defined as "no error." If the element comes from a formula cell the error value is determined by the formula.

22

Type

Type of cell content, 0 == Double, 1 == String

24

Value or Len

If type == 0: 8 byte IEEE variable of type double/floating point

If type == 1: Length of the following string, including closing zero byte. If the length including closing zero byte equals an odd value a second zero byte is added to the string so that an even value is achieved. Therefore, Len is calculated using ((StrLen+2)&~1).

26 if type==1

String

If type == 1: String with closing zero byte

32 or 26+Len

...

Next element



Parameter of the function DAYS360


The interpretation behavior of the DAYS360 function has changed in StarOffice 5.2. This is why you can arrive to other results when recalculating documents from the previous versions. In this section, the word "Old" refers to the StarOffice behavior before the 5.2 version and "New" refers to the behavior after the 5.2 version (including the 5.2 version).

Note: DAYS360 calculates intervals on the basis of 30-day-months, the last day of the month is always the 30th. (the 31st. will be handled as 30th.), with the exception of February. February is especially handled. The modifications are:

Old: If the third optional parameter was not entered, it would have been interpreted as non 0 (TRUE) (European method).
New: If the third optional parameter is not entered, it will be now interpreted as =0 (FALSE) (US method (NASD)).

Old: If the date 2/28 was a leap year, it would have been converted to 2/29 if the third parameter had been previously entered and was FALSE.
New: if the date 2/28 is a leap year, it will always remain 2/28.

The table shows a short summary.

Date

Leap year

3. Parameter Type

Old:

New:

01.02.28

yes

FALSE

changed to 29.

remains 28.

01.02.28

no

FALSE

changed to 30.

changes to 30.

01.02.29

yes

FALSE

changed to 30.

changes to 30.

01.02.28

yes

TRUE

remained 28.

remains 28.

01.02.28

no

TRUE

remained 28.

remains 28.

01.02.29

yes

TRUE

remained 29.

remains 29.


Furthermore, the following applies:

If the earlier date is a 31st. and the third parameter is FALSE and the old date was not any 30th. (after a possible conversion of a date such as 2/28-2/29 or 31), then the earlier date will be set on the 1st. of the following month.

If the earlier date is a 31st. and the third parameter is TRUE or the previous date was a 30th., then the earlier date will be set on the 30th. The approach is (Old:/New:) remaining equal, but the result can be modified via the modified default values with a missing third parameter.

Example

Old: =DAYS360( "03/01/00"; "03/31/00" ) = 29

New: =DAYS360( "03/01/00";"03/31/00" ) = 30

=DAYS360( "03/01/00"; "03/31/00"; FALSE ) = 30

=DAYS360( "03/01/00"; "03/31/00"; TRUE ) = 29

=DAYS360( "02/28/00"; "03/01/00" ) = 3

Old: =DAYS360( "02/28/00";"03/01/00"; FALSE ) = 2

New: =DAYS360( "02/28/00"; "03/01/00"; FALSE ) = 3

=DAYS360( "02/28/00"; "03/01/00"; TRUE ) = 3

Old: =DAYS360( "02/29/00"; "03/01/00" ) = 2

New: =DAYS360( "02/29/00";"03/01/00" ) = 1

=DAYS360( "02/29/00"; "03/01/00"; FALSE ) = 1

=TAGE360( "02/29/00"; "01/03/00"; TRUE ) = 2


Statistics: Part One


INTERCEPT

Calculates the point at which a line will intersect the y-values by using existing x-values (known_x's) and y-values (known_y's).

Syntax

INTERCEPT(known_y's;known_x's)

Known_y's is the dependent set of observations or data.

Known_x's is the independent set of observations or data.

Names, matrices or references containing numbers must be used here. Numbers can, of course, also be entered directly.

Example

To calculate the intercept, use cells D3:D9 as the y value and C3:C9 as the x value from the example spreadsheet. Input will be as follows:

INTERCEPT(D3:D9;C3:C9) = 2.15. The result is 2.15.

FORECAST,GROWTH, LINEST, LOGEST, PEARSON, RSQ, SLOPE, STEYX, TREND

COUNT

Counts how many values are in the list of arguments. Text entries are ignored, and the individual values are added together.

Syntax

COUNT(value1; value2; ... value30)

Value1; value2, ... are 1 to 30 values arguments representing the values to be counted.

Example

The entries 2,4,6 and eight in the value 1-4 fields are to be counted.

COUNT(2;4;6;eight) = 3. The number of entries is therefore 3.

AVERAGE, COUNTA, DCOUNT, DCOUNTA, SUM

COUNT2

Counts how many values are in the list of arguments. Text entries are also counted. The individual values will be added together, and empty arguments are ignored.

Syntax

COUNTA(value1; value2; ... value30)

value1; value2, ... are 1 to 30 arguments representing the values to be counted.

Example

The entries 2,4,6 and eight in the value1-4 fields are to be counted.

COUNTA(2;4;6;eight) = 4. The number of entries is therefore 4.

AVERAGE, COUNT, DCOUNT, DCOUNTA, PRODUCT, SUM

B

Returns the probability of a sample with binomial distribution.

Syntax

B(trials;probability_s;T1;T2)

Trials is the number of independent trials.

Probability_s is the probability of success on each trial.

T1 defines the lower limit for the number of trials.

T2 (optional) defines the upper limit for the number of trials.

Example

What is the probability with ten throws of the dice, that a six will come up exactly twice? The probability of a six (or any other number) is 1/6. The following formula combines these factors:

=B(10; 1/6; 2) returns a probability of 29%.

BINOMDIST

RSQ

Returns the square of the Pearson correlation coefficient based on the given values. RSQ (also called determination coefficient) is a measure for the accuracy of an adjustment and can be used to produce a regression analysis.

Syntax

RSQ(known_y's; known_x's)

Known_y's is an array or range of data points.

Known_x's is an array or range of data points.

Example

=RSQ(A1:A20; B1:B20) calculates the correlation coefficient for both data sets in columns A and B.

CORREL, COVAR, INTERCEPT, LINEST, LOGEST, PEARSON, SLOPE, STEYX, TREND

BETAINV

Returns the inverse of the cumulative beta probability density function.

Syntax

BETAINV(number;alpha;beta;A;B)

Number is the value between A and B at which to evaluate the function.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution.

A (optional) is the lower bound for number.

B (optional) is the upper bound for number.

Example

=BETAINV(0.5; 5; 10) returns the value 0.33.

BETADIST

BETADIST

Returns the cumulative beta probability density function.

Syntax

BETADIST(number;alpha;beta;A;B)

Number is the value between A and B at which to evaluate the function.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution.

A (optional) is the lower bound for number.

B (optional) is the upper bound for number.

Example

=BETADIST(0.75; 3; 4) returns the value 0.96

BETAINV

BINOMDIST

Returns the individual term binomial distribution probability.

Syntax

BINOMDIST(number_s;trials;probability_s;cumulative)

Number_s is the number of successes in a set of trials.

Trials is the number of independent trials.

Probability_s is the probability of success on each trial.

Cumulative = 0 calculates the single probability and cumulative = 1 calculates the cumulative probability.

Example

=BINOMDIST(A1; 12; 0,5; 0) shows (if the values 0 to 12 are entered in A1) the probabilities for 12 flips of a coin that Heads will come up exactly the number of times entered in A1.

=BINOMDIST(A1; 12; 0,5; 1) shows the cumulative probabilities for the same series, i.e. if A1 = 4, the probability of 0, 1, 2, 3 or 4 times Heads (non-exclusive OR).

B, COMBIN, CRITBINOM, FACT, HYPGEOMDIST, NEGBINOMDIST, PERMUT, PROB

CHIINV

Returns the inverse of the one-tailed probability of the chi-squared distribution.

The Chi square distribution is a distribution from statistics that is taken as the basis for the "Chi square test". A hypothesis is examined in this test: If the observed extent of randomness is sufficient for the preset distribution law (thus the Chi square distribution), then the hypothesis is confirmed. Granted the assumption that the hypothesis is correct, the observed Chi square would have to correspond to the hypothetical Chi square of the theoretical Chi square distribution (at least roughly).

Chi square is determined as the sum of
(observed value-expected value)^2/expected value
for all values.

Since Chi square is a measure of the deviation from the true (observed) distribution from the hypothetical (theoretical) distribution, the hypothesis will be rejected if the value calculated for Chi square from a concrete random sample exceeds a certain critical value. The function CHIINV returns this critical value for the probability of error that was preset and the degrees of freedom. It can also be found in tables in mathematical reference works.

Syntax

CHIINV(number; degrees_freedom)

Number is the value of the error probability, where the critical size CHIINV should be calculated, in other words, it is the probability that secures the hypothesis.

Degrees_freedom is the degrees of freedom of the experiment.

Example

A die is thrown 1020 times. The numbers on the die 1 through 6 come up 195, 151, 148, 189, 183 and 154 times (observation values). The hypothesis that the die is not fixed is to be tested.

The Chi square distribution of the random sample is determined by the formula given above. Since the expected value for a given number on the die for n throws is n times 1/6, thus 1020/6 = 170, the formula returns a Chi square value of 13.27.

If the (observed) Chi square is greater than or equal to the (theoretical) Chi square CHIINV, the hypothesis will be discarded, since the deviation between theory and experiment is too great. If the observed Chi square is less that CHIINV, the hypothesis is confirmed with the indicated probability of error.

=CHIINV(0,05; 5) returns 11,07.

=CHIINV(0,02; 5) returns 13.39.

If the probability of error is 5%, the die is not true. If the probability of error is 2%, there is no reason to believe it is fixed.

CHIDIST, CHITEST

CHITEST

Returns the probability of a deviance from a random distribution of two test series based on the chi-squared test for independence. CHIDIST returns the chi-squared distribution of the data.

The probability determined by CHITEST can also be determined with CHIDIST, in which case the Chi square of the random sample must then be passed as a parameter instead of the data row.

Syntax

CHITEST(actual_range; expected_range)

Actual_range is the matrix of the observations.

Expected_range is the range of the expected values.

Example


A (observed)

B (expected)

1

195

170

2

151

170

3

148

170

4

189

170

5

183

170

6

154

170


=CHITEST(A1:A6; B1:B6) equals 0,02. This is the probability which suffices the observed data of the theoretical Chi-square distribution.

CHIINV, CHIDIST

CHIDIST

Returns the probability value from the indicated Chi square that a hypothesis is confirmed. CHIDIST compares the Chi square value to be given for a random sample that is calculated from the sum of (observed value-expected value)^2/expected value for all values with the theoretical Chi square distribution and determines from this the probability of error for the hypothesis to be tested.

The probability determined by CHIDIST can also be determined by CHITEST, in which case instead of the Chi square of the random sample, the observed and expected data must be passed as the parameter.

Syntax

CHIDIST (x; degrees_freedom)

Number is the chi-square value of the random sample to which the error probability should be detected.

Degrees_freedom are the degrees of freedom of the experiment.

Example

=CHIDIST(13,27; 5) equals 0,02.

If the Chi square value of the random sample is 13.27 and if the experiment has 5 degrees of freedom, then the hypothesis is assured with a probability of error of 2%.

CHIINV, CHITEST

EXPONDIST

Returns the exponential distribution.

Syntax

EXPONDIST(x; lambda; cumulative)

X is the value of the function.

Lambda is the parameter value.

Cumulative is a logical value that determines the form of the function. Cumulative = 0 calculates the density function, and Cumulative = 1 calculates the distribution.

Example

=EXPONDIST(3; 0.5; 1) returns 0.78.

GAMMADIST, POISSON


Statistics: Part Two


FINV

Returns the inverse of the F probability distribution. The F distribution is used for F tests in order to set the relation between two diffused data quantities.

Syntax

FINV(Number; degrees_freedom1; degrees_freedom2)

Number is probability value for which the inverse F distribution is to be calculated.

degrees_freedom1 is the number of degrees of freedom in the numerator of the F distribution.

degrees_freedom2 is the number of degrees of freedom in the denominator of the F distribution.

Example

=FINV(0.5; 5; 10) yields 0.93.

FTEST, FDIST

FISHER

Returns the Fisher transformation for x and creates a function close to a normal distribution.

Syntax

FISHER(Number)

Number is the value to be transformed.

Example

=FISHER(0.5) yields 0.55.

FISHERINV, CORREL, COVAR

FISHERINV

Returns the inverse of the Fisher transformation for x and creates a function close to a normal distribution.

Syntax

FISHERINV(Number)

Number is the value that is to undergo reverse-transformation.

Example

=FISHERINV(0.5) yields 0.46.

FISHER, CORREL, COVAR

FTEST

Returns the result of an F test.

Syntax

FTEST(Data 1; Data 2)

Data 1 is the matrix of the first record.

Data 2 is the matrix of the second record.

Example

=FTEST(A1:A30; B1:B12) calculates whether the two data rows are different in their variance and returns the probability that both rows could have come from the same total population.

FINV, FDIST

FDIST

Calculates the values of an F distribution.

Syntax

FDIST(Number; degrees_freedom 1; degrees_freedom 2)

Number is the value for which the F distribution is to be calculated.

degrees_freedom degrees_freedom 1 is the degree of freedom in the numerator in the F distribution.

degrees_freedom 2 is the degree of freedom in the denominator in the F distribution.

Example

=FDIST(0.8; 8; 12) yields 0.61.

FINV, FTEST

GAMMAINV

Returns the inverse of the gamma cumulative distribution. This function allows you to search for variables with different distribution.

Syntax

GAMMAINV(Number; Alpha; Beta)

Number is the probability value for which the inverse Gamma distribution is to be calculated.

Alpha is the parameter Alpha of the gamma distribution.

Beta is the parameter Beta of the gamma distribution.

Example

=GAMMAINV(0.8; 1; 1) yields 1.61.

GAMMADIST

GAMMALN

Returns the natural logarithm of the gamma function: G(x).

Syntax

GAMMALN(Number)

Number is the value for which the natural logarithm of the of the Gamma function is to be calculated.

Example

=GAMMALN(2) yields 0.

FACT

GAMMADIST

Returns the probabilities of a gamma distribution.

Syntax

GAMMADIST(Number; Alpha; Beta; K)

Number is the value for which the Gamma distribution is to be calculated.

Alpha is the parameter Alpha of the Gamma distribution.

Beta is the parameter Alpha of the Gamma distribution

K = 0 calculates the density function K = 1 the distribution.

Example

=GAMMADIST(2; 1; 1; 1) yields 0.86.

CHIDIST, EXPONDIST, GAMMAINV

GAUSS

Returns the standard normal cumulative distribution.

Syntax

GAUSS(number)

Number is the value for which the integral value of the normalized standard distribution is to be calculated.

Example

GAUSS(0.19) = 0.08

GAUSS(0.0375) = 0.01

ZTEST, NORMDIST, NORMSDIST

GEOMEAN

Returns the geometric mean of a sample.

Syntax

GEOMEAN(Number 1; Number 2; ...Number 30)

Number 1, Number 2,...Number 30 are numeric arguments that represent a random sample.

Example

If you enter the values 23, 46 and 69 in text boxes value 1, 2 and 3, the result displayed will be 41.79.

GEOMEAN(23; 46; 69) = 41.79. The geometric mean value of this random sample is therefore 41.79.

TRIMMEAN, HARMEAN, MEDIAN, MEAN, MODE

TRIMMEAN

Returns the mean of an interior of a data set.

Syntax

TRIMMEAN(Data; Alpha)

Data is the matrix of data in the random sample.

Alpha is the percentage of the marginal data that will not be taken into consideration.

Example

=TRIMMEAN(A1:A50; 0,1) calculates the mean value of numbers in A1:A50, without taking into consideration the 5 percent of the values representing the highest values and the 5 percent of the values representing the lowest ones. The percentage numbers refer to the amount of the untrimmed mean value, not to the number of summands.

GEOMEAN, HARMEAN, MEDIAN, MEAN, MODE

ZTEST

Returns the two-tailed P value of a z test with standard distribution.

Syntax

ZTEST(Data; x; STD)

Data is the matrix of the data.

K is the value to be tested.

STD (optional) is the standard deviation of the total population. If this argument is missing, the standard deviation of the random sample in question will be processed.

Example

=ZTEST(A1:A50; 12) yields the probability that value 12 belongs to the standard distribution of the total population of data in A1:A50.

CONFIDENCE, NORMINV, NORMDIST, STANDARDIZE, NORMSINV, NORMSDIST

HARMEAN

Returns the harmonic mean of a data set.

Syntax

HARMEAN(Number 1; Number 2; ...Number 30)

Number 1,Number 2,...Number 30 are up to 30 arguments, that can be used to calculate the harmonic mean.

Example

If you enter the values 23, 46 and 69 in the text boxes number 1, 2 and 3 the result that is displayed will be 37.64.

HARMEAN(23;46;69) = 37.64. The harmonic mean of this random sample is thus 37.64

GEOMEAN, TRIMMEAN, MEDIAN, MEAN, MODE

HYPGEOMDIST

Returns the hypergeometric distribution.

Syntax

HYPGEOMDIST(X; N Random; M; N Total)

X is the number of results achieved in the random sample.

N Random is the size of the random sample.

M is the number of possible results in the total population.

N Total is the size of the total population.

Example

=HYPGEOMDIST(2; 2; 90; 100) yields 0.81. If 90 out of 100 pieces of buttered toast fall from the table and hit the floor with the buttered side first, then if I drop 2 pieces of buttered toast from the table the probability is 81%, that both will strike buttered side first.

BINOMDIST, FACT, COMBIN, NEGBINDIST, VARIATIONS


Statistics: Part Three


LARGE

Returns the k-th largest value in a data set.

Syntax

LARGE(Data; Rank K)

Data is the matrix of data in the random sample.

Rank K is the rank of the value.

Example

=LARGE(A1:C50; 2) gives the second largest value in A1:C50.

SMALL, MAX, MEDIAN, PERCENTILE, PERCENTRANK, QUARTILE

SMALL

Returns the k-th smallest value in a data set.

Syntax

SMALL(Data; Rank K)

Data is the matrix of data in the random sample.

Rank K is the rank of the value.

Example

=SMALL(A1:C50; 2) gives the second smallest value in A1:C50.

LARGE, MEDIAN, MIN, PERCENTILE, PERCENTRANK, QUARTILE

CONFIDENCE

Returns the confidence interval for a population mean.

Syntax

CONFIDENCE(Alpha; STD; N)

Alpha is the level of the confidence interval.

STD is the standard deviation for the total population.

N is the size of the total population.

Example

=CONFIDENCE(0.05; 1.5; 100) gives 0.29.

ZTEST

CORREL

Returns the correlation coefficient between two data sets.

Syntax

CORREL(Data 1; Data 2)

Data 1 is the matrix of the first record.

Data 2 is the matrix of the second record.

Example

=CORREL(A1:A50; B1:B50) calculates the correlation coefficient as a measure of the linear relation between the two data rows.

FISHER, FISHERINV, COVAR

COVAR

Returns the covariance of the product of paired deviations.

Syntax

COVAR(Data 1; Data 2)

Data 1 is the matrix of the first record.

Data 2 is the matrix of the second record.

Example

=COVAR(A1:A30; B1:B30)

FISHER, FISHERINV, CORREL

CRITBINOM

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.

Syntax

CRITBINOM(N; W; Alpha)

N is the total number of attempts.

W is the probability of success for one attempt.

Alpha is the threshold probability to be reached or exceeded.

Example

=CRITBINOM(100; 0.5; 0.1) yields 44.

BINOMDIST, FACT, HYPGEOMDIST, COMBINATIONS, NEGBINOMDIST, VARIATIONS, PROB

KURT

Returns the kurtosis of a data set (at least 4 values required).

Syntax

KURT(Number 1; Number 2; ...Number 30)

Number 1,Number 2,...Number 30 are numeric arguments representing a random sample of distribution.

Example

=KURT(A1;A2;A3;A4;A5;A6)

SKEW, STDEV, STABWP, VAR, VARP

LOGINV

Returns the inverse of the lognormal distribution.

Syntax

LOGINV(Number; MV; STD)

Number is the probability value for which the inverse standard logarithmic distribution is to be calculated.

MV is the mean value of the standard logarithmic distribution.

STD is the standard deviation of the standard logarithmic distribution.

Example

=LOGINV(0.05; 0; 1) returns 0.19.

EXP, LN, LOG, LOG10, LOGNORMDIST

LOGNORMDIST

Returns the cumulative lognormal distribution.

Syntax

LOGNORMDIST(NUMBER; MV; STD)

Number is the probability value for which the standard logarithmic distribution is to be calculated.

MV is the mean value of the standard logarithmic distribution.

STD is the standard deviation of the standard logarithmic distribution.

Example

=LOGNORMDIST(0,1; 0; 1) returns 0.01.

EXP, LN, LOG, LOG10, LOGINV


Statistics: Part Four


MAX

Returns the maximum value in a list of arguments.

Syntax

MAX(Number 1; Number 2; ...Number 30)

Number 1; Number 2;...Number 30 are numerical arguments, the largest of which must be determined. Each number can also be replaced by a reference.

Example

=MAX(A1;A2;A3;50;100;200) returns the largest value from the list.

=MAX(A1:B100) returns the largest value from the list.

DMAX, MIN

MAXA

Returns the maximum value in a list of arguments. In opposite to MAX, here you can enter text. The value of the text is 0.

Syntax

MAXA(Value 1; Value 2; ... Value 30)

Value 1; Value 2;...Value 30 are arguments, the largest value of which must be determined. Each value can be replaced by a reference. Text will have the value of 0.

Example

=MAXA(A1;A2;A3;50;100;200;Text) returns the largest value from the list.

=MAXA(A1:B100) returns the largest value from the list.

MINA

MEDIAN

Returns the median of a set of numbers. In a set containing an uneven number of values, the median will be the number in the middle of the set and in a set containing an even number of values, it will be the mean of the two values in the middle of the set.

Syntax

MEDIAN(Number 1; Number 2; ...Number 30)

Number 1; Number 2;...Number 30 are arguments, which represents a sample. Each number can also be replaced by a reference.

Example

If (odd number): =MEDIAN(1, 5, 9, 20, 21), the list will return 9 as the median value.

(even number): =MEDIAN(1, 5, 9, 20) returns the average of the two middle values 5 and 9, thus 7.

COUNT, COUNT2, DAVERAGE, AVERAGE, MEAN, SUM

MIN

Returns the minimum value in a list of arguments.

Syntax

MIN(Number 1; Number 2; ...Number 30)

Number 1; Number 2;...Number 30 are numerical arguments, the smallest of which must be determined. Each number can also be replaced by a reference.

Example

=MIN(A1:B100) returns the smallest value in the list.

DMIN, MAX

MINA

Returns the minimum value in a list of arguments.Here you can also enter text. The value of the text is 0.

Syntax

MINA(Value 1; Value 2; ... Value 30)

Value 1; Value 2;...Value 30 are arguments, the smallest value of which must be determined. Each value can be replaced by a reference. Text will have the value of 0.

Example

=MINA(1; Text; 20) returns the smallest value in the list.

=MINA(A1:B100) returns the smallest value in the list.

MAXA

AVEDEV

Returns the average of the absolute deviations of data points from their mean. Displays the diffusion in a data set.

Syntax

AVEDEV(Number 1; Number 2; ...Number 30)

Number 1, Number 2,...Number 30 are arguments that represent a sample. Each number can also be replaced by a reference.

Example

=AVEDEV(A1:A50)

STDEV, STDEVP, DEVSQ, VAR, VARP

AVERAGE

Returns the average of the arguments.

Syntax

AVERAGE(Number 1; Number 2; ...Number 30)

Number 1; Number 2;...Number 30 are numerical arguments, which represent a sample taken from a population. Each number can also be replaced by a reference.

Example

=AVERAGE(A1:A50)

GEOMEAN, TRIMEAN, HARMEAN, MEDIAN, MODE

AVERAGEA

Returns the average of the arguments. The value of a text is 0.

Syntax

AVERAGEA(Value 1; Value 2; ... Value 30)

Value 1; Value 2;...Value 30 are arguments, which represent a sample taken from a population. Each value can be replaced by a reference. Text will have the value of 0.

Example

=AVERAGEA(A1:A50)

AVERAGE,

MODE

Returns the most common value in a data set. If there are several values with the same frequency, it returns the smallest value. An error occurs when a value doesn't appear twice.

Syntax

MODE(Number 1; Number 2; ...Number 30)

Number 1; Number 2;...Number 30 are numerical arguments, which represent a sample. Each number can also be replaced by a reference.

Example

=MODE(A1:A50)

GEOMEAN, TRIMEAN, HARMEAN, MEDIAN, AVERAGE

NEGBINOMDIST

Returns the negative binomial distribution.

Syntax

NEGBINOMDIST(X; R; W)

X represents the value returned for unsuccessful tests.

R represents the value returned for successful tests.

P is the probability of the success of an attempt.

Example

=NEGBINOMDIST(1; 1; 0.5) returns 0.25.

BINOMDIST, FACT, HYPGEOMDIST, COMBIN, PERMUT

NORMINV

Returns the inverse of the normal cumulative distribution.

Syntax

NORMINV(Number; MW; STD

Number represents the probability value according to which the inverse normal distribution should be calculated.

MV represents the middle value in normal distribution.

STD represents the standard deviation of the normal distribution.

Example

=NORMINV(0,9; 63; 5) returns 69,41. If the average egg weighs 63 grams with a standard deviation of 5, then there will be 90% probability that the egg will not be heavier than 69.41g grams.

GTEST, NORMDIST, STANDARDIZE, STANDNORMINV, STANDNORMDIST

NORMDIST

Returns the standard normal cumulative distribution.

Syntax

NORMDIST(Number; MV; STD; K)

Number is the value of the distribution based on which the normal distribution is to be calculated.

MV is the linear middle of the distribution.

STD is the standard deviation of the distribution.

K = 0 calculates the density function; K = 1 calculates the distribution.

Example

=NORMDIST(70; 63; 5 0) returns 0.03.

=NORMDIST(70; 63; 5 1) returns 0.92.

GTEST, NORMINV, STANDARDIZE, NORMINV, NORMVERT

PEARSON

Returns the Pearson correlation coefficient r.

Syntax

PEARSON(Data 1; Data 2)

Data 1 represents the array of the first record.

Data 2 represents the array of the second record.

Example

=PEARSON(A1:A30; B1:B30) returns the Pearson correlation coefficient of both data rows.

INTERCEPT, RSQ, LINEST, SLOPE, STEYX

PHI

Returns the values of the distribution function for a standard normal distribution.

Syntax

PHI(Number)

Number represents the value based on which the standard normal distribution is calculated.

Example

PHI(2,25) = 0,03

PHI(-2,25) = 0,03

PHI(0) = 0,4

NORMDIST

POISSON

Returns the Poisson distribution.

Syntax

POISSON(Number; MV; K)

Number represents the value based on which the Poisson distribution is calculated.

MV represents the middle value of the Poisson distribution.

K = 0 calculates the density function; K = 1 calculates the distribution.

Example

=POISSON(60;50;1) returns 0.93.

EXPONDIST

PERCENTILE

Returns the k-th percentile of values in a range. A percentile returns the scale value of a data series which goes from the smallest to the largest value of the data series on the Alpha percent . For Alpha = 25%, the percentile means the first quartile; Alpha = 50% is the MEDIAN.

Syntax

PERCENTILE(Data;Alpha)

Data represents the array of data.

Alpha represents the percentage record of the percentile between 0 and 1.

Example

=PERCENTILE(A1:A50; 0,1) represents the value in the data rows, which equals 10% of the total data span in A1:A50.

LARGE, SMALL, MAX, MEDIAN, MIN, PERCENTRANK, QUARTILE

PERCENTRANK

Returns the percentage rank of a value in a sample.

Syntax

PERCENTRANK(Data; Value)

Data represents the array of data in the sample.

Value represents the value whose percentile rank must be determined.

Example

=PERCENTRANK(A1:A50; 50) returns the percentage rank of the value 50 from the total range of all values found in A1:A50. If 50 falls outside the total range, an error message will appear.

LARGE, SMALL, MAX, MEDIAN, MIN, PERCENT, QUARTILE

QUARTILE

Returns the quartile of a data set.

Syntax

QUARTILE(Data; Type)

Data represents the array of data in the sample.

Type represents the type of percentile. (0 = MIN, 1 = 25%, 2 = 50% (MEDIAN), 3 = 75% and 4 = MAX.)

Example

=QUARTILE(A1:A50; 2) returns the value of which 25% of the scale corresponds to the lowest to highest values in the range A1:A50.

LARGE, SMALL, MAX, MEDIAN, MIN, PERCENTILE, PERCENTRANK


Statistics: Part Five


RANK

Returns the rank of a number in a sample.

Syntax

RANK(Value; Data; Type)

Value is the value, whose rank is to be determined.

Data is the array or range of data in the sample.

Type (optional) is the sequence order. = 0 means ascending, = 1 means descending.

Example

=RANK(A10; A1:A50) returns the ranking of the value in A10 in value range A1:A50. If Value does not exist within the range an error message is displayed.

PERCENTRANK

SKEW

Returns the skewness of a distribution.

Syntax

SKEW(Number 1; number 2; ...number 30)

Number 1, number 2...number 30 are numerical arguments representing a sample of the distribution. They can also stand for ranges.

Example

=SKEW(A1:A50) calculates the value of skew for the data referenced.

KURT, STDEV, STDEVP, VAR, VARP

FORECAST

Returns the inverse of the normal cumulative distribution.

Syntax

FORECAST(Value; known_y's; known_x's)

Value is the x value, for which the y value on the linear regression is to be returned.

Known_y's is the array or range of known y's.

Known_x's is the array or range of known x's.

Example

=FORECAST(50; A1:A50; B1;B50) returns the Y value expected for the X value of -Value 50 if the X and Y values in both references are linked by a linear trend.

LINEST, RKP, TREND, GROWTH

STDEV

Estimates the standard deviation based on a sample.

Syntax

STABW(Number 1;number 2;...number 30)

Number 1,number 2,...number 30 are numerical arguments representing a sample based on an entire population.

Example

=STDEV(A1:A50) returns the estimated standard deviation based on the data referenced.

AVEDEV, AVERAGE, MODE, STDEVP, VAR

STDEVA

Calculates the standard deviation of an estimation based on a sample. The value of text is 0.

Syntax

STDEVA(Value 1;value 2;...value 30)

Value 1,value 2,...value 30 are arguments representing a sample derived from an entire population. Texts are also possible and are considered to be 0.

Example

=STDEVA(A1:A50) returns the estimated standard deviation based on the data referenced.

STDEV, STDEVP and STDEVPA.

STDEVP

Calculates the standard deviation based on the entire population.

Syntax

STDEVP(Number 1;number 2;...number 30)

Number 1,number 2,...number 30 are numerical arguments representing a sample based on an entire population.

Example

=STDEVP(A1:A50) returns a standard deviation of the data referenced.

AVEDEV, AVERAGE, MODE, STDEV, VARP

STDEVPA

Calculates the standard deviation based on the entire population. The value of text is 0.

Syntax

STDEVPA(Value 1;value 2;...value 30)

Value 1,value 2,...value 30 are arguments representing a sample derived from an entire population. Text is considered to be 0.

Example

=STDEVPA(A1:A50) returns the standard deviation of the data referenced.

STDEVP and STDEV.

STANDARDIZE

Converts a random variable to a normalized value.

Syntax

STANDARDIZE(Number; MEAN; STD)

Number is the value to be standardized.

MEAN is the arithmetic mean of the distribution.

STD is the standard deviation of the distribution.

Example

=STANDARDIZE(11; 10; 1) returns 1. The value 11 is exactly as much higher than the mean 10 in a distribution with a mean of 10 and a standard deviation of 1 as the value 1 is exactly as much higher as the mean 0 in standard normal cumulative distribution.

ZTEST, NORMINV, NORMDIST, NORMSINV, NORMSDIST

NORMSINV

Returns the inverse of the standard normal cumulative distribution.

Syntax

NORMINV(Number)

Number is the probability to which the inverse standard normal distribution is calculated.

Example

NORMSINV(0.908789) returns 1.3333.

ZTEST, NORMINV, NORMDIST, STANDARDIZE, NORMSDIST

NORMSDIST

Returns the normal cumulative distribution.

Syntax

NORMSDIST(Number)

Number is the value to which the standard normal distribution is calculated.

Example

=NORMSDIST(1) returns 0.84. The area below the standard normal distribution curve to the left of X value 1 is 84% of the total area.

ZTEST, NORMINV, NORMDIST, PHI, STANDARDIZE, NORMSINV

SLOPE

Returns the slope of the linear regression line. The slope is adapted to the data points set in the y and x values.

Syntax

SLOPE(Known y's; known x's)

Known y's is the dependent array or range of data.

Known x's is the dependent array or range of data.

Example

=SLOPE(A1:A50; B1:B50)

INTERCEPT, RSQ, PEARSON, LINEST, LOGEST, STEYX, TREND

STEYX

Returns the standard error of the predicted y value for each x in the regression.

Syntax

STEYX(known y's; known x's)

Known y's is the dependent array or range of data.

Known x's is the dependent array or range of data.

Example

=STEXY(A1:A50; B1:B50)

INTERCEPT, RSQ, PEARSON, LINEST, LOGEST, SLOPE

DEVSQ

Returns the sum of squares of deviations based on a sample mean.

Syntax

DEVSQ(Number 1; number 2; ...number 30)

Number 1,number 2,...number 30 numerical arguments representing a sample. They can also stand for references.

Example

=DEVSQ(A1:A50)

AVEDEV, STDEV, STDEVP, VAR, VARP

TINV

Returns the inverse of the t-distribution.

Syntax

TINV(Number; degrees of freedom)

Number is the probability associated with the two-tailed t-distribution.

Degrees of freedom is the number of degrees of freedom for the t-distribution.

Example

=TINV(0,1; 6) returns 1.94

TTEST, TDIST

TTEST

Returns the probability associated with a Student's t-Test.

Syntax

TTEST(Data 1; Data 2; Mode; Type)

Data 1 is the dependent array or range of data for the first record.

Data 2 is the dependent array or range of data for the second record.

Mode = 1 calculates the one tailed test, Mode = 2 two- tailed distribution.

Type is the kind of t-test to perform. Type 1 means paired. Type 2 means two samples, equal variance (homoscedastic). Type 3 means two samples, unequal variance (heteroscedastic).

Example

=TTEST(A1:A50; B1:B50; 2; 2)

TINV, TDIST

TDIST

Returns the t-distribution.

Syntax

TDIST(Number; Degrees of freedom; Mode)

Number is the value for which the t-distribution is calculated.

Degrees of freedom is the number of degrees of freedom for the t-distribution.

Mode = 1 returns the one-tailed test, Mode = 2 returns the two-tailed test.

Example

=TDIST(12; 5; 1)

TINV, TTEST

VAR

Estimates the variance based on a sample.

Syntax

VAR(Number 1; number 2; ...number 30)

Number 1,number 2,...number 30 are numerical arguments representing a sample based on an entire population. They can also stand for references.

Example

=VAR(A1:A50)

AVEDEV, AVERAGE, MODE, STDEV

VARA

Estimates a variance based on a sample. The value of text is 0.

Syntax

VARA(Value 1; value 2; ...value 30)

Value 1,value 2,...value 30 are arguments representing a sample derived from an entire population. They can also stand for references. Text is considered to be 0.

Example

=VARA(A1:A50)

VAR, STDEV and STDEVA

VARP

Calculates a variance based on the entire population.

Syntax

VARP(Number 1; number 2; ...number 30)

Number 1,number 2,...number 30 are numerical arguments representing an entire population.

Example

=VARP(A1:A50)

AVEDEV, AVERAGE, MODE, STDEV

VARPA

Calculates the variance based on the entire population. The value of text is 0.

Syntax

VARA(Value 1; value 2; ...value 30)

Value 1,value 2,...value 30 are arguments representing an entire population.

Example

=VARPA(A1:A50)

STDEVP and STDEVPA

PERMUT

Returns the number of pemutations for a given number of objects.

Syntax

PERMUT(Number 1; Number 2)

Number 1 is the total number of objects.

Number 2 is the number of objects in each permutation.

Example

=PERMUT(6; 3) returns 120. There are 120 different possibilities, to pick a sequence of 3 playing cards out of 6 playing cards.

BINOMDIST, FACT, HYPGEOMDIST, COMBIN, CRITBINOM, NEGBINOMDIST

PERMUT2

Returns the number of permutations for a given number of objects (repetition allowed).

Syntax

PERMUT2(Number 1; Number 2)

Number 1 is the total number of objects.

Number 2 is the number of objects in each permutation.

Example

How often can 2 objects be selected from a total of 11 objects?

PERMUT2(11;2) returns 121.

PERMUT2(6; 3) returns 216. There are 216 different possibilities to put a sequence of 3 playing cards together out of six playing cards if every card is returned before the next one is drawn.

BINOMDIST, FACT, HYPGEOMDIST, COMBIN2, CRITBINOM, NEGBINOMDIST

PROB

Returns the probability that values in a range are between two limits. If there is no CEILING, this function calculates the probability based on the principle that the data values are equal to the value of FLOOR.

Syntax

PROB(Data; Probabilities: Beginning; End)

Data is the array or range of data in the sample.

Probabilities is the array or range of the corresponding probabilities.

Beginning is the beginning of the value interval whose probabilities are to be summed.

End (optional) is the end of the value interval whose probabilities are to be summed. If this parameter is missing, the probability that this exact Beginning value exists is calculated.

Example

=PROB(A1:A50; B1:B50; 50; 60) returns the probability with which a value within the range of A1:A50 is also within the limits between 50 and 60. Every value within the range of A1:A50 has a probability within the range of B1:B50.

BINOMDIST, CRITBINOM

WEIBULL

Returns the values of the Weibull distribution.

Syntax

WEIBULL(Number; Alpha; Beta; K)

Number is the value at which to calculate the Weibull-distribution.

Alphais the Alpha parameter of the Weibull-distribution.

Beta is the Beta parameter of the Weibull-distribution.

K indicates the type of function. If Cumulated equals 0 the form of the function is calculated, if Cumulated equals 1 the distribution is calculated.

Example

=WEIBULL(2; 1; 1; 1) returns 0.86.

EXPONDIST


Operators in StarOffice Calc


You can use the following operators in StarOffice Calc.

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


Comparative operators

These operators return either true or false.

Operator

Name

Example

= (equal sign)

Equal

A1=B1

> (Greater than)

Greater than

A1>B1

< (Less than)

Less than

A1<B1

>= (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


Text operators

The operator combines sections of text to the entire text.

Operator

Name

Example

& (And)

Text link: And

"Star" & "Office" is "StarOffice"


Reference operators

These operators link cell ranges.

Operator

Name

Example

: (Colon)

Range

A1:C108

! (Exclamation point)

Intersection

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.



Names


With this command, you can name the different sections of your spreadsheet document. By naming the different sections, you can easily navigate through the spreadsheet documents and find specific information.

Define...

Insert...

Apply...

Labels....


Define Names


Specify a name for a selected area here. StarOffice organizes area names according to cell references, which are taken directly from your spreadsheet. Area names can also contain formulas. You can define references by clicking the respective cells, or manually entering a reference in the Define Names dialog.

The Sheet Range drop-down box in the Formula bar contains a list of defined names for the ranges. Click a name from this box and the corresponding reference will be highlighted in the spreadsheet. The names given to formulas or to parts of a formula are not listed here.

Name

Enter the name of the area for which you want to define a reference. The list box lists all area names defined in the spreadsheet.If you click a name on the list, the corresponding reference in the document will be marked with a blue frame. Several cell ranges will be displayed at the same with different colors if they are all included in an area name.

Refers to

The reference of the selected area name is shown here as an absolute value.

To insert a new area reference, place the cursor in this field and use your mouse to select the desired area in any sheet of your spreadsheet document.


>>More

Click this button to specify the type of area for the reference.

Type of area

Here you can define additional options related to the type of reference area.

Print area

Check this box to define the area as a print area.

Filter

Check this box if you want to define the selected area as a filter.

Repeat column

Check this box to define the area as a repeating column.

Repeat row

Check this box to define the area as a repeating row.

<<More

Click here to close the options box.

Add/Modify

Click the Add button to add the defined name to the list. Click the Modify button to enter another name for an already existing name on the list.



Paste Name


Select a previously defined cell range to insert it in the current cursor's position. Simply select the name of the range.


You can only insert a cell area after having defined a name for the area.


Paste Name

A list of all the defined cell areas is displayed here. Double-click any entry to insert the named area into the active sheet at the current cursor position.

Paste List

Click here to insert a list of all named areas and the corresponding cell references at the current cursor position.


Creating Names


You can automatically generate the area names from the cell contents of the selection. The cell references extend rows or columns until the end of the selected are. The cells with the area names are not included in the cell reference.

First, select all cells you want to edit, the later name areas as well as the header/footer rows and/or row titles to the left or right of the data cells. Then access this dialog.

Create name from

Define which part of the spreadsheet is to be used for creating the name.

Header

Creates the area names from the header row of the selected area. Each column receives a separated name and cell reference.

Left Column

Creates the area names from the entries in the first column of the selected sheet area. Each row receives a separated name and cell reference.

Footer

Creates the area names from the entries in the last row of the selected sheet area. Each column receives a separated name and cell reference.

Right Column

Creates the area names from the entries in the last column of the selected sheet area. Each row receives a separated name and cell reference.


Define Label Range


Define a label range in this dialog; that is an area which you can name for easy identification.

You can assign names to rows, columns or their parts with labeled sections. You can place the individual contents of the cell of a labeled section as a name in a formula. StarOffice then recognizes the names in the same way it recognizes predefined names such as weekdays and months. As a result, the name will be completed automatically when you begin entering it. The label ranges take precedence over the automatically set ranges if a name occurs more than once.



You can set several label ranges that contain the same labels for different tables. In that case, StarOffice checks the ranges of the active table and, following a failed search, the ranges of other tables.


Label range

Here you will see the respective area reference of the existing label ranges. The text box below displays the selected range as a setting when you open the dialog. The current selected cell will be displayed if you did not make a Selection. Select the label area if you want to Delete it.


Contains column headers

Choose this option if you want to include column headers in the current defined label area.

Contains row headers

Choose this option if you want to include row headers in the current defined label area.

For data range

This field displays the defined sheet area. To modify it, click in the sheet and select another area with the mouse.


Add

Click here to add the current defined label area to the list.



Functions


This command opens the function window which offers you several functions to insert in your document. The function window is similar to the Functions tab of the Function AutoPilot. The arguments need placeholders to be replaced with your own values.

The function window is a andockbares window that is resizable. It is used for quickly entering functions in the spreadsheet document. By double-clicking an entry in the function list, the respective function is directly inserted with all parameters.

Category List

Function List

This list box displays the functions of the selected category. Select the desired function with the mouse. When you select a function, the area below the function window displays a short description about this function. To apply the selected function to the document just double-click it or click the Insert in Spreadsheet icon.

Insert Function in the Spreadsheet


Click this icon to apply the selected function to the document.



Cell Attributes


Use this command to choose from a variety of formatting options and to apply attributes to the selected cells.

Numbers

Font







Cell Protection


Use this tab to define various protection options for the sheet or the cell area.

Protect

In this section, you can select one of the following protection options: Protected, Hide Formula or Hide All.

Protected

To prevent certain cells from being modified, click here.


This cell protection will only take effect if you have also protected the spreadsheet in which cell protection is activated.


Hide Formulas

Check this box to temporarily hide formulas in the sheet.

Hide All

Choose this option to temporarily hide all elements of a sheet.

Print

Define print options for the sheet.

Hide when printing

If you choose this option, all previously selected cell areas in the sheet will be omitted when printing. Deactivate this option to print the corresponding cell areas.


Row


With these submenus, you can set the row height and either hide or show selected rows.

Height...

Optimal Height...




Optimal Row Height


Sets the optimal row height for the selected rows. The optimal row height depends on the font size of the largest character within a row. You can choose from the various measurement units available.

Add

In this spin box you can specify additional spacing between the largest character in a row and the cell boundaries.

Default Value

Restores the default value for the optimal row height. The additional spacing for the optimal row height is set to 0,0 cm.


Hide


Use this command to hide rows, columns or individual sheets.

Select the rows and columns that you want to hide, and then select Row - Hide or Column - Hide on the Format menu. If you have several spreadsheets in your document, you can hide a spreadsheet by selecting it on the sheet tabs and then choosing the Hide command.


A break in the row or column header indicates whether the row or column is hidden.


To display hidden rows, columns or spreadsheets, use the Format - Row/Column - Show or Format - Sheet - Show command.


Show


Choose this command to show previously hidden rows or columns. To show cells or columns, select the corresponding area with the hidden elements and select then the Format - Row - Show menu or Format - Column - Show.

To show a column or row, select the range of rows or columns containing the hidden elements, then choose the Format - Row - Show or Format - Column - Show.


Column


This area contains the submenu items used to set the Column Width, as well as to show/hide columns.

Width...

Optimal Width...




Optimal Column Width


Sets the optimal column width for selected columns. The optimal column width depends on the largest entry within a column. You can choose from the available measurement units.

Add

Use the spin buttons to define additional spacing between the longest entry in a column and the vertical column borders.

Default value

Define the optimal column width in order to display the entire contents of the column. The additional spacing for the optimal column width is preset to 0.1 in.


Spreadsheet


By using this command, you will call up a submenu in which you can rename and hide the current spreadsheet. If the spreadsheet is already hidden, you will display a dialog where you can make the spreadsheet visible again.

Rename...


Show...


Rename


This command opens a dialog where you can assign a different name to the current sheet.

Name

Enter a new name for the sheet here.


You can also open this dialog via the context menu by positioning the cursor over a sheet tab at the bottom of the window and clicking while pressing the (Ctrl) key clicking the right mouse button.

Alternatively, click the sheet tab while pressing the (Command)(Alt) key. Now you can change the name directly.


Choose Append in the context menu of the sheet tab to insert an additional sheet after all existing sheets. In the Append Sheet dialog, assign a name to the new sheet.


Show Sheet


Use this command to display again sheets that were previously hidden with the Hide command.

Hidden sheets

Here you will see a list of all hidden sheets in your spreadsheet document. To show a certain sheet, click the corresponding entry on the list and confirm with OK.


Merge Cells


Here you can see a submenu for combining cells into a common cell and for undoing this combination.




Define


Merges the selected cell area and handles it as one cell. The merged cell receives the name of the first cell of the original cell range. Merged cells cannot be merged a second time with other cells. The range must form a rectangle, multiple selection is not supported.


If the cells to be merged have any contents a security response is necessary.



Split


Choose this command to separate the merged area of cells.


Page Style


Opens a dialog where you can determine the appearance of all pages in your document.










Sheet


In this tabpage you can specify the elements to be included in the printed document. Additionally, you can set the print order, the first page number and the page scale.

Print

Define which elements of the spreadsheet are to be printed.

Column and row headers

Check this box if you want the column headers A, B,... and the row headers 1, 2,... to be printed.

Grid

Check this box to print the gridlines between individual cells.

Notes

Check this box to print the notes defined in your spreadsheet. They will be printed on a separate page, along with the corresponding cell reference.

Objects/pictures

Choose this option to include all inserted objects (if printable) and graphics with the printed document.

Charts

Click here to print the charts inserted in your spreadsheet.

Drawing Objects

If you choose this option, all draw objects will be included in printing.

Formulas

Check this box if you want to print the formulas contained in the cells instead of the results.

Zero Values

Click here to specify that the number 0 be included in printing. Otherwise, all cells containing the number 0 will not be printed.

Page Order

Define the order in which data in a sheet is numbered and printed when it does not fit on one printed page.

Top to Bottom

Choose this option to print vertically from the left column to the bottom of the sheet.

Left to Right

Choose this option to print horizontally from the top row of the sheet to the right column.

Number of first page

Select this option if you want the first page to start with a number other than 1.

The first page receives the specified page number in the spin box.

Scale

Here you can define a page scale for the printed spreadsheet.

All pages to (%)

Choose this option to specify an enlargement or reduction factor to scale all printed pages.


Enter a scaling factor here.

Max. no. of pages

Choose this option to specify the maximum number of pages on which the entire document is to be printed. The document will be scaled to fit the defined number of pages to be printed.


Enter the maximum number of pages to be printed.


Print Range


Here, you have the option of defining a print range for each spreadsheet. Once the print range has been defined, only the specified print range will be printed. You can also specify a row or column to be printed on all the following pages.




Edit...


Set


Use this command to define an active cell or selected cell area as the print range.


Clear


Use this command to clear the defined print area.


Edit Print Range


Opens a dialog that enables you to edit the print range. Furthermore, you can set the rows or columns which are to be repeated in every page. .

Print Area

In this field you can modify a defined print area.

Select -None- to remove a print range definition for the current spreadsheet. Select -Selection- to define the selected area of a spreadsheet as the print range. By selecting -User-defined-, you can define a print range that you have already defined using the Format - Print Range - Set commands. If, for example, you have given a name to a range using the Insert - Name - Set command, this name will be displayed and can be selected in the list box.

In the right-hand text box, you can enter a print range in the reference style or as a print range. If the cursor is in the Print Range text box, you can also select the print range in the spreadsheet with your mouse.


Row to Repeat

Choose on or more rows to print on every page. In the right text box enter the row reference, e.g. "1" or "$1" or "$2:$3". The list box displays then -user defined-. You can also select -none- to remove a defined repeating row.

As well, You can define repeat rows by dragging the mouse in the spreadsheet if the cursor is in the Repeat Row text field in the dialog.


Column to repeat

Choose on or more columns to print on every page. In the right text box enter the column reference, e.g. "A" or "AB" or "$C:$E". The combo box displays then -user defined-. You can also select -none- to remove a defined repeating column.

You can also define repeat columns by dragging the mouse in the spreadsheet if the cursor is in the Repeat Column text field in the dialog.



Add


Use this command to add the current selection to the defined print areas.


Style Catalog


Opens the style catalog for creating, editing and organizing styles. A style can be applied to a cell or a page.

All the settings in this dialog pertain to soft formatting. With the exception of template management, the Designer offers the same functionality.

Style Type

In the drop-down list, choose whether to show cell or page styles. The selection Cell Styles displays all the available cell styles; the selection Page styles displays all the available page styles.

List of Styles

Lists all existing styles of the selected style type; the current style in use is selected.

In the context menu you can choose commands to create a new template, delete a user-defined template, or change the selected template.

Style Category

To keep an overview, the styles are divided into various style categories. If the desired style is not displayed in Styles just select another style category for the current style.

All templates

Shows all templates of the current template type.

Used templates

Shows the templates of the current template type used in the current document. These can also be selected on the Object Bar.

User templates

Shows the user-defined templates of the current template type.

Hierarchical

Displays the templates of the current template type in the form of a hierarchical list. The list looks like a directory tree structure on the hard drive. To show lower-level templates, click the plus sign next to the template name.


New...

Click here to create a new style. Depending on the style type, you will open the Cell Style or the Page Style dialog.

Modify...

Select this command to modify the selected style. Depending on the style type you will open the Cell Style or Page Style dialog.

Delete...

Click here to delete the selected style (after confirming a delete warning). Only custom styles can be deleted.

Organize...

Click this button to activate the Document Templates dialog.


Stylist


Click here to turn the Stylist on or off. Use the Stylist to assign styles to objects and text ranges. You can update a style, modify an existing style or create a new style. The Stylist "notes" the style chosen for a specific view and restores this state when switching to this view. The Stylist's dockable window can remain open while editing the document.

By using styles in your documents, you can make comprehensive format changes in a snap, since when you modify a style, all text and objects to which the style has been applied are automatically updated.

In the Stylist you will find various icons used to format documents:

Cell Styles

Click this icon to display a list of existing cell styles for soft formatting.


Cell Styles


Page Styles

Click here to display the Page Styles. Page styles allow you to use soft formatting for the page layout. If you determine another style, StarOffice Calc assigns the defined page style when changing to the new page


Page Styles


Fill Format Mode

Use this mode to apply identical styles to areas or objects in the work area. When the Fill Format mode is active, the mouse pointer appears as a watercan symbol. To exit this mode, click the icon in the Stylist again.


Fill Format Mode


How to apply a new style with the watercan:

New Style by Example

Click here to create a new style based on the formatting of a selected object. Assign a name for the style in the Create Style dialog.


New Style By Example


Update Style

Click this icon to update a style in the Stylist with the formatting of a selected object. The update applies to all draw objects to which this style has been assigned.


Update Style



Context menu New.../ Modify.../ Delete...

Here you have access to the same functions as in the Format - Style Catalog dialog.

Style Category

Select the desired style category in this list box.


AutoFormat


Use this command to apply an AutoFormat to a selected sheet area or define your own AutoFormats.


Starting with StarOffice Version 5.0, reversed texts are also copied over to AutoFormat and applied as AutoFormat. This makes the file format used for saving StarOffice Writer AutoFormat and StarOffice Calc AutoFormat incompatible with earlier versions. StarOffice 5.0, 5.1 and 5.2 can still read the old formats but older versions of StarOffice cannot read the new AutoFormat.


Format

Choose a predefined AutoFormat to apply to a selected area in your sheet.


Add...

If you have selected a range of at least 4 x 4 cells, you can add its formatting to the list of predefined AutoFormats by clicking Add.... The Add AutoFormat dialog will then appear.



More >>

Click this button to view the Formatting section where you can exclude certain AutoFormat options.

Formatting

In this section you can turn the following elements of the AutoFormat on or off: Number format, Borders, Font, Pattern, Orientation and Adjust width and height.

Number Format

Clear this check box if the number format is not to be changed.

Borders

Clear this check box if the borders are not to be changed.

Font

Clear this check box if you want to retain the font.

Pattern

Clear this check box if you want to retain the pattern (colors, etc.)

Alignment

Deactivate this check box if the alignment is not to be changed.

Adjust width and height

Deactivate this check box if the width and height of the selected cells are not to be changed.

Rename

Click this button to change the specification of the selected AutoFormat. The button is only visible if you selected the More >> option.


The dialog Rename AutoFormat is opened. Enter the new name of the AutoFormat here.

More <<

Use this button as well to close the Formatting options section.


Conditional Formatting


Choose Conditional Formatting to define format styles depending on certain conditions. If a style was already assigned to a cell, it remains unchanged. The style entered here will be then evaluated. You can enter 3 conditions, which query the contents of cell values or formulas. The conditions are queried from 1 to 3. If the condition 1 matches, the defined style will be used. Otherwise, the condition 2 will be queried and the defined style used. If this style does not match, the condition 3 will be queried.

In the StarOffice samples, you will find the document Office52/share/samples/english/spreadsheets/biorhythm.sdc. Conditional formatting has been used in the "Details" table contained in this document.

Condition 1/2/3

Check this box to define a condition. Check the boxes corresponding to each condition and enter the respective condition. To close the dialog, click OK.

Cell value/Formula

Specify if the conditional formatting should be dependent on a cell value or a formula. If you select a formula as a reference, the combo box Cell Value Condition will be displayed on the right of the Cell value/Formula field. If the condition is "Is formula" enter a cell reference; if the cell reference is a value other than zero, the condition will match.

Cell Value Condition

Choose a condition for the format to be applied to the selected cells.

Cell style

Choose the style to be applied if the specified condition matches.

Parameter field

Enter a reference, value or formula here. Enter a reference, value or formula in the parameter field (or in both parameter fields if you have selected a condition that requires two parameters). You can also enter formulas containing relative references.

Once the parameters have been defined, the condition is complete. It may appear, for example, as:

Cell value is equal 0: Cell style Null value (You must have already defined a cell style with this name before assigning it to a condition).

Cell value is between $B$20 and $B$21: Cell style Result (The corresponding value limits must already exist in cells B20 and B21)

Formula is SUM($A$1:$A$5)=10: Cell style Result (The selected cells are formatted with the Result style if the sum of the contents in cells A1 to A5 is equal to 10.



Cell Style


Here, you can create a cell style.

Numbers









Detective


This command activates the Spreadsheet Detective. With the Detective, you can make the links between the current formula cells and the cells in the spreadsheet document visible. Thus, you can easily check formula definitions and make any necessary corrections.

If you define an icon in the spreadsheet, you can put your cursor on it. It will change into a magnifying glass with reference arrows. Double-click the visible icon to select the cell at the furthest end of the icon.












Trace Precedents


This function shows the relationship between the current cell containing a formula and the cells used in the formula. StarOffice Calc draws tracer arrows in the spreadsheet and highlights all cells used in the formula with a blue border.



This function is based on a principle of layers. For example, if the precedent cell to a formula is already indicated with a tracer arrow, when you repeat this command, the tracer arrows are drawn to the precedent cells of this cell.



Remove Precedents


Deletes one level of trace arrows (inserted with Trace Precendents).


Trace Dependents


Draws tracer arrows to the active cell from formulas that depend on values in the active cell.. Additionally, the area of all cells that are used together with the active cell in an equation is surrounded by a blue frame.


This function works per level. For instance, if one level of traces has already been activated to show the Precedents (or dependents), then you would see the next dependency level by activating the Trace function again.



Remove Dependents


Deletes one level of tracer arrows created with Trace Dependents.


Remove All Traces


Removes all tracer arrows from the spreadsheet.


Trace Error


This function draws tracer arrows to all cells which cause an error value in a selected cell.


Fill Mode


This command activates the Fill mode in the Detective. The mouse pointer changes to a special symbol, and you can click any cell to see a trace to the precedent cell. To exit this mode, press Escape or click the End Fill Mode command in the context menu.

The Fill Mode function is identical with the Trace Precedent command if you call this mode for the first time. Use the context menu to select further options for the Fill Mode and to exit this mode.


Mark invalid data


This command marks all cells in the sheet, containing values outside the validation rules.

The validity rules restrict the input of numbers, dates, time values and text to certain values. However, it is possible to enter invalid values (if the Stop action is not selected) or copy invalid values into the cells. When you assign a validity rule, existing values in a cell will not be modified.

Use the Detective function to trace invalid values.


Refresh Traces


Use this command to redraw all traces in the sheet. The modified formulas by the redrawing of the traces are also considered.

Detective arrows in the document are updated under the following circumstances:


AutoRefresh


Use this button to automatically refresh all the traces in the sheet when you modify a formula.


Goal Seek


Opens a dialog where you can specify a target value for a selected cell. After a successful search you will see a dialog with the results, allowing you to apply the target value directly to the cell.

The operation of Goal Seek is explained in the User Manual with an example.

Default

In this section, you can define the variables in your formula.

Formula cell

In the formula cell, enter the reference of the cell which contains the formula. It contains the current cell reference. Click another cell in the sheet to apply its reference to the text box.

Target value

Enter the value you want to achieve as a new result.

Variable cell

Enter the reference for the cell that contains the value you want to adjust in order to reach the target.


Create Scenario


Use this field to define a scenario for the selected sheet area.

Scenario name

Use this field to enter or change the name for the scenario (in the Navigator via the context menu - Properties...). Use a clear, unique name so that you are able to identify the scenario anytime, as scenarios are selected (and edited) by means of the name that appears on the sheet tabs at the lower window frame or in the Navigator.

Comments

Use this field to modify the existing information or to enter additional information about the scenario (in the Navigator via the context menu - Properties...). This information will be displayed in the Navigator when you click the Scenarios icon and select the desired scenario.

Settings

This section is used to define some of the settings used in the scenario display.

Display border in

Check this box if you want to highlight the scenario in your table with a border. The color for the border is specify in the combo box below this check box. The border will have a title bar displaying the name of the last scenario. The button on the right 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

If this check box has been activated the data will first of all be copied back into the active scenario if one has been selected from the list. Afterwards, you can edit the data for each scenario directly in the sheet. If this option has not been selected together with Display border in you will not be able to see scenarios in the sheet. In this case, the Navigator will help you. Click the Scenarios button located in the Navigator to show and then select one of the defined scenarios for this sheet.

Copy entire sheet

Check this box if you want to copy the entire sheet into an additional scenario table. If the two other options have also been deselected, the procedure is the same as above described .


Protect Document


This command prevents changes from being made to spreadsheets and documents. As an option, you can define a password for the appropriate section.

Spreadsheets....

Documents....


Protecting Sheet


Click here to protect the sheet from being modified. Via the menu Tools - Protect Document - Sheet... you open the Protect Sheet dialog. Specify here the necessary settings, with or without password, in order to protect the sheet.


To protect the cells of the table from further editing the locked check box must be checked on the Format - Cell... - Cell Protection menu or on the Format Cells... context menu.




Unprotected cells or cell areas can also be set up in an otherwise protected environment by using the Tools - Protect Document - Table... and Format - Cell... - Cell Protection menus (or the Format Cells... - Cell Protection context menu). First, select the "free" area, and then display the Cell Protection tab on the Format - Cell... menu or on the Format Cells context menu. In the Disable Protection section, uncheck the locked check box and click OK. On the Tools - Protect Document - Table... menu, activate protection for the table. Effective immediately, only the area you defined can be edited.

To later change a "free" area to a protected area, select the area in question. Next, on the Format - Cell... - Cell Protection menu or on the corresponding context menu, check the locked check box. Finally, choose the Tools - Protect Document - Table... menu. The previously editable area is now protected.

Table protection also applies to the context menu of the Table tab at the bottom of the screen. The Delete... and Move/Copy... commands cannot be selected.


A protected table or table area can no longer be changed until this protection is disabled. To disable the protection, choose the Tools - Protect Document - Table... command again. If no password was set the table protection is immediately disabled. If the table was password protected you will see the Remove Protection dialog where you must enter the password. Only then is the checkmark indicating the protection removed.

Once saved, protected tables can only be saved again by using the File - Save As... command, which makes it more difficult to accidentally overwrite the file.

Password (optional)

To protect your sheet from unauthorized changes, enter a password here.


If you forget your password there is no way to undo this option.



Complete protection of your work can be achieved by combining both options on the Tools - Protect Document menu, including password protection. To prohibit opening the document altogether, first enable the Save with Password field during the initial save before you click the Save button. The Enter Password dialog appears where you can enter an appropriate string and confirm it with OK. Note that a forgotten password will exclude you from accessing your document.



Protecting document


Protects your entire document from modifications. It is impossible to insert, delete, rename, move or copy sheets. Via the menu Tools - Protect Document - Document... open the Protect document dialog. Specify here the necessary settings, with or without password, in order to protect the document.

The structure of protected spreadsheet documents can be changed only if the Protect option is disabled. On the context menus for the spreadsheet tabs at the lower graphic border, only the menu item Select All Spreadsheets can be activated. You can select either Delete... or Move/Copy... or another option. To remove the protection, once again call up the command Tools - Protect Document - Document.... If no password is assigned, protection is immediately removed. If you were assigned a password, you can now view the dialog Remove Spreadsheet Protection, in which you must enter the password. Only then will the check mark specifying that protection is active be removed.

A protected document, once saved, can only be saved again with the File - Save As.... menu command. This makes it more difficult to save incorrect changes to the file.

Password (optional)

Enter you password to protect your document against unauthorized or accidental modifications.


If you have forgotten your password, you cannot cancel the option.




You will be able to completely protect your work by combining both options from Tools - Protect Document, including password entry. If you want to prevent the document from being opened by other users, select Save With Password and click the Save button. The Enter Password dialog appears, in which you can enter a string of characters and confirm it by clicking OK. Consider carefully when choosing a password; if you forget it after you close a document you will be unable to access the document.



Auto Calculate


If you choose this option, formulas will be automatically recalculated, and if necessary, the results will be corrected. The recalculation will take place in all cells when you modify a cell in the sheet. The charts in the sheet will be also updated. If this button is active, the Recalculate function (F9) is not available.


Recalculate


Recalculates the current sheet immediately. Use this command if you deselected the automatic recalculation or if you want to be sure that you get the real new recalculated status (especially in very large sheets). The document is redisplayed after the recalculation. The charts in the sheet will be also updated.


This command is not active if automatic recalculation is turned on.



Auto Input


Use this command to activate the AutoComplete. The AutoComplete function automatically creates existing character strings within, at the beginning or at the end of a vertical listing. The whole column will be considered up to a length of 2000 cells or 200 different strings, including empty cells. A string's annex will be automatically generated, when obtaining a clear string correlation within the created listing. The generated part of the string will be displayed in an inverted way.

When entering a formula (a formula begins with an equal sign) and when achieving a clear string correlation, a listing of the last used functions from the Function AutoPilot will be displayed as a Tip. All defined range names, all database range names and the contents of all label areas will be displayed as a complete string including your entered correlation. Press the Enter key to accept the displayed tip in your document.

If several entries match, a select list will be provided. You can start the list for the column being edited via the Select List... context menu or by pressing (Ctrl) (D). Use the tab key to leaf forwards; if you press the shift key as well you can leaf backwards.


Cell Contents


Open a submenu with commands to calculate tables and activate AutoInput.





Split


Activate this command to split the window area into four sections. A horizontal and vertical line are displayed extending from the active cell. These lines divide the screen into left, right, upper and lower sections. All four sections contain a scroll bar which you can use to view the entries in the individual sections.



If you want to split row 1, column A or row 1 and column A jointly, place the cell cursor in cell A2, B1 or B2.



You can also use the mouse to split the workpage window horizontally or vertically. To accomplish this, drag the thick black line located directly above the vertical scroll bar or directly to the right of the horizontal scroll bar into the workpage. A thick black line will show where the workpage is split.


A split window has its own scroll bars in each partial section; by contrast, fixed window sections are not scrollable.


Freeze


Select this command to freeze the active cell in the sheet. Freezing a worksheet can be especially useful when working with large spreadsheet documents, since you will be able to view the column or row headers even when you are working in another area of your document.


If you want to print one line on all pages of a document, use the Format - Print Ranges - Edit... command


After starting the Freeze command, a horizontal and/or a vertical line, stretching out from the current cell will be displayed. These lines serve to separate the upper/left area remaining in the image from the lower/right area, which can be moved up to this line.



If you want to freeze Line 1, Column A or freeze Line 1 and Column A together, you must place the cell cursor in either Cell A 2, B 1 or B 2. Otherwise no separation into the movable and the fixed area of a work page takes place.


If you want the frozen area to be scrollable, use the Window - Split command.


Define Database Range


Use this dialog to define a database range in your sheet.

Name

In this field you can define a new database range or select an existing one.Enter the name for the database range that you want to define or select a name from the existing list box.

After defining a name, select the area in the table. For database areas, only rectangular areas are permitted.

Range

This field displays the range reference of the selected database range. When you open the dialog, you will see the selected range displayed. If you have not made a selection the data range adjoining the cursor will be selected.


Add/Modify

Click here to add the new defined database range to the list, or modify an existing one.


More >>

Click this button to expand the dialog to view additional options.


Options


Define additional options for a database import.

Contains column headers

Check this box if the defined database area contains column headers.

Insert or delete cells

If you choose this option, any rows or columns subsequently created in the database are automatically inserted into your document as well. Use the Refresh Area command in the Data menu. This option will be also evaluated when filtered to another area.

Keep formatting as is

Check here to apply the existing cell formatting to new cells inserted from a database.

Don't save external data

Choose this option to save only a reference to the database rather than the file itself. Advantage: you save disk space.

Source:

Displays information about the source of the database and any existing operators.

More <<

Click here to reduce the extended dialog.


Select Database Area


Select a previously defined database area here. These are available in the Define Database Area dialog.

Areas

Here you will find a list of all existing database areas defined in the current document. Choose a database area from this list. After clicking OK, the database area will be marked in the sheet.


Sort


In this dialog you can define the conditions for the sort function.


If the cursor is positioned outside a database area, an untitled database will be created, and, upon activation, applied.






Sort Criteria


Define the sort criteria here. Select the sort order, sort direction and the table columns or rows to be sorted.


Make sure you select the row titles/column titles along with the row/columns to be sorted.


Sort by

Select the criteria you want to be used as the primary sort key.

Ascending

Choose this option to sort beginning with the lowest value, i.e. from A to Z or 0 to 9.

Descending

Choose this option to sort beginning with the highest value, i.e. from Z to A or 9 to 0.

Then by

Choose the criteria to use as the secondary sort key.

Ascending

Choose this option to sort beginning with the lowest value, i.e. from A to Z or 0 to 9.

Descending

Choose this option to sort beginning with the highest value, i.e. from Z to A or 9 to 0.

Then by

Choose the criteria to use as the third sort key.

Ascending

Choose this option to sort beginning with the lowest value, i.e. from A to Z or 0 to 9.

Descending

Choose this option to sort beginning with the highest value, i.e. from Z to A or 9 to 0.

Sort Ascending/Descending

Use the icons to select the sort order (ascending and descending). Number fields are sorted by size and text fields by the ASCII order of the contained characters.

First, select the cell in whose column or row you wish sorting to be carried out, and then clicke of the icons for ascending or descending sorting. In the Sort dialog under Options you should specify whether the row or column is to be sorted.

Icons on the Toolbar


Options


Choose additional options for the sort function defined in Sort criteria.

Case Sensitive

Check this box to sort text first by upper case and then by lower case letters.

Area contains column headers

If you check this box, the first selected row in the sheet is omitted in the sort function.

Include formats

If this check box is not selected, all formatting in the individual cells remain unchanged after being sorted, even if the cell contents change. Usually formatting is applied to an entire cell, not the cell contents.

If the check box is active, however, the formatting made to the text present in the cell is connected to the text and is moved to the target location if sorting takes place.

Copy sort results to:

If you choose this option, the sort results will be saved in a predefined sheet area.

Sort results

If you have defined a sheet area, you can select it here.

Sort results

Enter the cell area where you want the results to appear.

User-defined sort order

Check here to use a custom sort order. If the sheet area to be sorted contains defined terms in the selected sort list, those will be sorted with the highest priority. They are placed even before the letter A. If you entered the words "january, february, february, january" in a column and want to sort them based on the user-defined sort list, you will have the result "january, february, january, february". The reason for this sort sequence is the sorting algorithm.

Custom sort order

Choose a custom sort order defined in Tools - Options - Spreadsheet - Custom Lists.

Direction

In this area you can select the sorting direction.

Top to Bottom (Sort Rows)

Sorts all rows of the active columns within the selected area.

Left to Right (Sort Columns)

Sorts all columns of the active rows within the selected area.

Data area

Here, the range selected for sorting is displayed for you.


Filter


This submenu contains data filters commands. If you have defined a database area, StarOffice will automatically recognize it when the cursor is positioned in one of the cells of this area.


Positioning the cursor outside the database displays an unknown database area that when activated will be applied.


The following options are available:


Default filter...

Advanced filter...




AutoFilter


Use the AutoFilter to select certain values from a data list or a database which are to be displayed on the worksheet. The AutoFilter command configures one-row list boxes for each column of the selected area, which enable you to choose the cells to be filtered.

Select the entry -Default-, to bring up the Default Filter dialog. In this dialog you can enter additional filter conditions. Select the Top 10 entry to display the highest 10 values and to fade out all other values.


Options


This button shows an area in which you can select the definition of the filter from among several options.

Options

In this module you will find the various option preferences.

Case Sensitive

Choose this option to differentiate between upper and lower case text in a filtered list of values.

Range contains Column Headers

If you choose this option, the column headers in the first row of a sheet will be included.

Copy results to

Click here to select a range where you want the filter results to appear. You can select a named range from the list box. You can also select a range directly in the sheet, which will be the output range for the filter results. The AutoFilter list boxes will be then displayed only in the new output range.


Regular expression

Check this box if you want to use placeholders in the filter. StarOffice supports following placeholders:

Character

Effect/usage

.

Uses the entry "Jens.n" to find the terms "Jensen" and "Jenson".

^Peter

Finds the word only if it is located at the beginning of a paragraph.

Peter$

Finds the word only if it is located at the end of a paragraph.

*

This represents a character or characters that may occur not at all or any number of times. "Peter .*home" will find "Peter went home" and "Peter is at home".

+

The preceding character must occur at least once and may occur any number of times greater than one: "AX.+4" finds "AX 4", but not "AX4"

\t

Finds a tab.

()

Forms alternate groups for the search: "(Peter has) | (Peter gets)" will find both "Peter has" and "Peter gets" in the course of a search.

\>

The search text must occur at the end of the word: "pipe\> " will find "stovepipe", but not "pipeline".

\<

The search text must occur at the beginning of the word: "\<pipe" will find "pipeline", but not "stovepipe".


If the regular expressions are activated, EQUAL (=) and NOT EQUAL (<>) also apply for comparisons. The same applies for functions that always work with regular expressions for strings: DCOUNTA, DGET, MATCH, COUNTIF, SUMIF, LOOKUP, VLOOKUP and HLOOKUP.

No duplication

Check here to exclude duplicate rows in the list of filtered data. Several equal results are reduced to one entry.

Keep filter criteria

If a target area is specified in the field Copy results to, you can define the filter to be savedsavedsaved as filter criteria in a different area in the source area. The defined filter can be permanently run via Data Refresh Area. You have to activate the target area. An already active filtering within the area will be replaced. Only the filtering will be run if you deselect this check box.

Data range

Displays a reference to the data area in the sheet which is to be filtered. If you are working with a named area, its name will appear here as well.


Special Filter


Use this dialog to define a special filter. A special filter offers you more than three different filter criteria.


Read filter criteria from

Choose the area name in the sheet which contains the necessary filter criteria, or enter the area reference directly.

Options >>


Remove Filter


Clears a filter set in the selected sheet area. Set the cursor in the filtered cell range. Activate Remove Filter to remove all the previously selected filter.


Hide AutoFilter


Click here to hide the buttons representing the AutoFilter defined in the sheet area. The AutoFilter action for hiding certain rows will remain.


Subtotals


In this dialog you can define the settings for automatic calculation of subtotals.Subtotals can be partial sums or results of other mathematical functions, automatically calculated in a sheet. If you defined a database area, StarOffice recognizes it automatically when the cursor is positioned in a cell of this area.


Positioning the cursor outside the database displays an unknown database area that when activated will be applied.


Evaluate a client database based on sales for certain zip code areas by using the Subtotal function to automatically generate a sales summary for a zip code area.




Delete

This button deletes the subtotal rows in a selected area.


1st, 2nd, 3rd Group


Define the settings for one of the three subtotal groups. All subtotal tabpages have the same structure.

To insert subtotals for a table, proceed as follows:

Group by

Select the column which will control the the subtotal calculation process. If the contents of the selected column change, the calculation of a subtotal will be automatically started.

Calculate subtotals for

Choose the selected columns in the sheet which contain the values you want to calculate.

Use function

Choose the mathematical function to be used for the calculation of subtotals.


Options


Define various settings for calculating and presenting subtotals.

Page break between groups

Click here to insert a new page after each group of subtotaled data.

Case sensitive

Select this option if you want the case sensitive changes to create new subtotals.

Pre-sort area according to groups

Sorts the area defined in the Group tabs under Group by according to the specified columns.

Sort

Under Sort, you can specify how to sort the groups.

Include formats

Check this box to include all formatting attributes when sorting.

Custom sort order

Check here to use a selected custom sort order defined in the tab Tools - Options... - Spreadsheet Document - Sorted lists.

Ascending

Sorts beginning with the lowest value, i.e. from A to Z or 0 to 9.

Descending

Sorts beginning with the highest value, i.e. from Z to A or 9 to 0.


Multiple Operations


In this dialog you can specify references to a previously defined sheet area. With a multiple operation you can calculate totals for different values in a cohesive data area, defined in only one formula. You have to create a formula with a reference to the data area. This formula is in an independent cell.



Formulas

Enter the cell reference containing the formula on which the multiple operation is based.

Row

Enter the input cell reference to be used as a variable for the rows in the data table.

Column

Enter the input cell reference to be used as a variable for the columns in the data table.


Consolidate


Use this function to combine data from several independent table areas. A new area will be calculated via a selected mathematical function and based on those areas.

To consolidate multiple table areas contained in different tables of the table document, do the following:

Function

Select here the mathematical function you want to use for the consolidation.

Consolidation areas

This list displays the selected range of cells for the consolidation.

Source data area

Select any previously defined areas with names and add them to the list of Consolidation areas .

In the text field, you can enter cell areas directly and add them to the Consolidation Areas list. If you place the cursor in the text field and then select table areas with the mouse, the selected cell areas are added to this text field.


Copy results to

Select here the cell that will be used as the starting point of the data table. The consolidation table will be inserted at this point. In the list box select a predefined area, enter a cell reference directly or select a cell with the mouse, if the cursor is positioned in the text field at the right.


Add

Click here to add the reference specified in Source data area to the Consolidation areas.

More >>

Click here to expand the consolidation dialog to show the options Consolidate by.


Consolidate by


Consolidate by

In this section, you can indicate whether column headers or row labels should be taken into account during consolidation. If one of the offered check boxes is selected, then separate consolidation will be executed for varying column headers or row labels in the individual table areas. If nothing is selected, each cell will be consolidated by its position in the table without checking whether the data fits. In other words, the columns or rows of the areas to be consolidated do not need to be arranged in the same sequence. As long as the descriptions correspond, they can be arranged in a different sequence.

Row labels

Check here to include row labels in the consolidation area.

Column headers

Check here to include column labels in the consolidation area.

Link to source data

Links the data in the consolidation area to the source data, and automatically updates the results of the consolidation if any changes are made to the original data.

More <<

Click here to close the expanded options dialog.


Outline


The table can be designed more clearly by choosing these commands on the Outline submenu. After grouping a table, you can show/hide individual areas of the table by clicking the symbols shown at the table edges.



Group...

Ungroup...




Hide Details


Click here to hide an outline level in a grouped sheet. Click Show Details to display the outline again.


Show Details


Click here to display hidden sheet areas.


Group


Click here to group a selected sheet area. In the dialog, define the group by rows or columns.

There is an icon in the margins next to the grouping. Simply click the icon to hide or show the grouped ranges. To ungroup the group, simply use the Ungroup command.

Activate

Here, you can specify whether to group by rows or columns.

Rows

Combines selected rows to a group in an outline.

Columns

This option summarizes the selected columns and places them in a group.


Ungroup


Clears the lowest level of the existing group that was inserted with the command Group.

Remove for

Here you can select which groupings to remove.

Rows

Removes group properties for sheet rows.

Columns

Removes group properties for sheet columns.


Auto Outline


Click here to automatically group a selected range of cells in a sheet. The Auto Outline groups rows or columns depending on each other in formula cells. The dependence of the rows or columns to each other is displayed on the top of the worksheet.

In the AutoOutline bar that appears, the area that was merged in a logical outline by StarOffice is displayed as a row. The starting point of the row is a minus sign and end point is a right-angled connector line.

Click the starting point to merge the entire area. The minus sign changes to a plus sign. Click the plus sign to display the hidden area again.

Using Data - Outline - Remove, you can undo the AutoOutline.


Clear Outline


Click here to remove a grouping defined manually or with the Auto Outline function.


Data Pilot


You will see a submenu allowing you to open the Datapilot and update and delete calculated cells.

Start...




Select Source


Use the DataPilot to rearrange and organize large amounts of data in three or more dimensions. The DataPilot offers you the desired view for your data.

Refer to the Advanced Tips section of the User's Guide for details on how to create a DataPilot table and analyze the resulting information.

Selection

Use this area for selecting a data source.

Current Selection

The source will be the data in the cell area of the StarOffice Calc document. After confirming your selection by pressing the OK button, the DataPilot will appear enabling you to define the layout and the presentation of the DataPilot's results.

Data source registered in StarOffice

The data source will be here an attached database table or query in StarOffice. You don't need to first import the data in the StarOffice Calc document for this purpose. Confirm this selection by pressing the OK button and in the Select Data Source dialog you will be able to exactly define the source.

External source/interface

Select this option to use the OLAP Server as a source. By pressing the OK button you will access the External Source dialog. In this dialog, you can exactly define the OLAP data source in the fields "Service", "Source", "Name", "User" and "Password". By clicking again the OK button you'll access the DataPilot dialog. This dialog enables you to define the layout and presentation of the AutoPilot's results. The Glossary contains further information on OLAP.



If you use data from StarOffice Calc to create your DataPilot table, the number format of the data columns will be extracted from the first data row and correspondingly set in the DataPilot table.



Select Data Source


Select a database and an exact data source in this dialog.

Selection

Use this area for selecting a data source which has been previously registered in StarOffice 5.2.

Database

This list box displays all registered and therefore, selectable databases.

Data source

Use this list box to select one of the sources that have been set to the database.

Type

This list box enables you to select the source type. You can choose among the entries "Table", "Query" and "SQL" or SQL (Native).

OK

After confirming your selection by pressing the OK button, you'll access the DataPilot dialog. This dialog enables you to define the layout and presentation of the DataPilot' results.


DataPilot


Use this dialog to define the layout and results of the DataPilot.

Layout

Use this area to define how the AutoPilot should organize your data. Simply drag the fields into the "Column", "Row" and "Data" layout areas. You can always modify the order of the set fields and drag the fields within the layout area. It is also possible to remove them by simply dragging the outside the layout area into the other buttons.

If a button is dragged into the Data area, it will automatically receive a caption containing the name and the formula that was used to create the data in the data area. By double clicking any of the buttons in the Data area you'll access the Data Field dialog. This dialog enables you to select the used arithmetic formula.

You can also access the Data Field dialog by double clicking one of the fields in the Column or Row area. You can then decide if StarOffice should display and calculate subtotals.

More>>

Click this button to extend the dialog with the Results area. You can close this area by clicking again this button.

Result

Define the settings for the result of the DataPilot table here.

Results to

Select a defined area from the list box. Then, select a sheet area with the mouse or enter a cell range directly for preventing the results table from being positioned directly below the original table.


If no result area has been specified, the DataPilot table will be positioned directly below the source area regardless of existing data in that position.


Ignore empty rows

Select this option if you want all empty cells in the original table to be ignored.

Identify categories

If you choose this option, StarOffice automatically orders rows without labels to the next higher category specified by a row label.

Total columns

Select this check box if you want the grand total resulting from the columns calculation to be displayed.

Total rows

Select this check box if you want the grand total resulting from the rows calculation to be displayed.


Filter


In this dialog you can define conditions for filtering data in a sheet or table.

Criteria

You can define a default filter by indicating the type of line, the name of the field, a logical condition and a value or a combination of arguments.

Operator

Choose between the logical operators AND/OR.

Field name

In the list box select the field name of the current sheet to be used in the filter. The column labels are displayed here if the texts for the field names do not exist.

Condition

Choose a comparative operator to link the entries in Field name and Value.

The following comparison operators are available:

Conditions:


=

equal

<

less than

>

greater than

<=

less than or equal to

>=

greater than or equal to

<>

not equal to


Value

This list box contains all possible values for the specified field name . Choose the value to be used in the filter. You can also choose the - empty - or -not empty - entries to filter according to your choice.

More>>


Options


Click this button to display an area where you can choose between different options for the definition of the filter.

Options

This area contains the various Options preferences.

Case sensitive

Choose this option to differentiate between upper and lower case text in a filtered list of values.

Regular Expression

Check this box if you want to use placeholders in the filter. StarOffice supports following placeholders:

Character

Effect/usage

.

Uses the entry Jens.n to find the terms "Jensen" and "Jenson".

^Peter

Finds the word only if it is located at the beginning of a paragraph.

Peter$

Finds the word only if it is located at the end of a paragraph.

*

Represents a character or characters that may occur not at all or any number of times. "Peter .*home" will find "Peter went home" and "Peter is at home".

+

The preceding character must occur at least once and may occur any number of times greater than one: "AX.+4" finds "AX 4", but not "AX4"

\t

Finds a tab

()

Forms alternate groups for the search: "(Peter has) | (Peter gets)" will find both "Peter has" and "Peter gets" in the course of a search.

\>

The search text must occur at the end of the word: "pipe\> " will find "stovepipe", but not "pipeline".

\<

The search text must occur at the beginning of the word: "\<pipe" will find "pipeline", but not "stovepipe".


If the regular expressions are activated, EQUAL (=) and NOT EQUAL (<>) also apply for comparisons. The same applies for functions that always work with regular expressions for strings: DCOUNTA, DGET, MATCH, COUNTIF, SUMIF, LOOKUP, VLOOKUP and HLOOKUP.

Unique records only

Check here to exclude duplicate rows in the list of filtered data. The several filtered equal results will be reduced to one entry.

Data area

The name of the data area in the table on which the filter is being used is indicated here in the notation of references. If you are working with a named area, its name will appear here as well.

More<<

This button hides the extended dialog.


Data field


The appearance of the dialog depends on the way you called up the dialog: either by double clicking the field in the Data area in the DataPilot or by double clicking a field in the Row or Column area.

Subtotals

Define the type of subtotals to calculate.

None

The default setting does not calculate any subtotals.

Automatic

Choose this option to automatically calculate subtotals.

User-defined

Select this option if you want to define the type of subtotals to be calculated. Afterwards you can select the type of the subtotal in the list box of the Subtotals area.

Function

Click a function in this box to specify the type of subtotals to be calculated. The entries contained in this box can only be selected if the User-defined option is checked or if you've called this dialog by double clicking a field from the Data area.

Show elements without data

Check this box to define if the elements from the data source that do not contain any data or empty columns or rows are to be inserted in the results table.

Name:

Here you will find the name of the button in the DataPilot area Column, Row or Data for which the subtotals are defined.


Refresh


Click here to update a table created with the DataPilot. It can be necessary if, e.g., the output data changed.


Delete


Click here to delete the DataPilot table. The deleted table is the one where the cursor is positioned.


Refresh Area


Updates data inserted from an external database. The data in the table will be equal to the data in the external database.


Validity


This dialog allows you to define various validity criteria for a selected sheet area.






Values


Defines which values to accept for the validity criteria..

Validity criteria

Select the border conditions for the validity of the marked cells. With all entry fields together, you can define criteria of type: "Numbers between 1 and 10" or "Texts that are no more than 20 characters".

Allow

Define the validity condition for cell data. You can change the other options in this dialog depending on the selected condition.

The following conditions are available for selection:

Condition

Effect

Each value

No limitation.

Whole number

Only whole numbers corresponding to the condition.

Decimal

All numbers corresponding to the condition.

Date

All numbers corresponding to the condition. The entered values are formatted accordingly the next time the dialog is called up.

Time

All numbers corresponding to the condition. The entered values are formatted accordingly the next time the dialog is called up.

Text length

Entries whose length corresponds to the condition.


Ignore blanks

Click here to allow blank cells in the selected cell area; otherwise each cell must contain a value.

Data

Choose here a comparative operator to use as a condition. If you select "between" or "not between", you will have two available fields for the value entries: minimum and maximum. Otherwise, you will see an input field with the names value, minimum or maximum.

Value

Enter the value that, according to the condition, the data has to correspond to.This is where you enter the value that, according to the condition, the data has to correspond to. If you selected "between" or "not between" under Data, enter the minimum value here.

Minimum

Enter a minimum value here.

Maximum

Enter a maximum value here.


Input help


Define an input message to appear when a cell is selected.

Show input help when cell is selected

Select this option to display a message defined under Contents as soon as a cell in the worksheet is selected.



If you entered text in the Contents section of the dialog, mark it and deselect it, the text will be lost.


Contents

Defines the contents of the input Help.

Title

Enter a title for the input message.

Input help

Enter the text of the input message to be displayed under the title. Use the enter key to break large rows manually.


Error Message


Define an error message to be displayed when invalid values are entered. It is also possible to start a macro with an error message.

Show error message when invalid values are entered.

Check this box if you want an error message to be displayed when invalid values are entered. Otherwise, a text will be displayed referring to the invalid entry.

In both cases, when you select "Stop" the invalid entry is canceled and the previous value is retained in the cell. The same applies to the "Warning" and "Information" actions when you end the warning or information dialog with Cancel. If you close the dialog with OK rather than Cancel, the invalid value remains.

Contents

Here you can define the error text or error action.

Action

Choose the type of error dialog to display, or a predefined macro to be run.(refer to the example macro) The "Stop" action displays a dialog, which you have to close with OK. The invalid entry is rejected. The "Warning" and "Information" actions display a dialog that can be closed with OK or Cancel. If you want to reject the invalid entry, click Cancel, if not, click OK.

Browse...

Click here to open the Macro dialog that allows you to choose the macro to be executed after an error message.

Title

Enter the name of the macro to be executed or the title of the dialog you want to display.

Error message

Enter the text to display in case of an error.

Sample macro:

Function ExampleValidity(CellValue as String, TableCell as String) 
Dim msg as string 
msg = "Invalid value: " & "'" & CellValue & "'" 
msg = msg & " in table: " & "'" & TableCell & "'" 
MsgBox msg ,16,"Error message" 
End Function 

Number Format: Currency


Click here to display cell contents in currency format.


Number Format: Currency

For formatting of cell contents, see also Format - Cell - Numbers.


Number Format: Percent


Click here to display cell contents in percentage format.


Number Format: Percent

For formatting of cell contents, see also Format - Cell - Numbers.


Number Format: Standard


Click here to display cell contents in the default number format.


Number Format: Standard

For formatting of cell contents, see also Format - Cell - Numbers.


Number Format: Add Decimal Place


Click here to add a decimal place to a number in the selected cell(s).


Number Format: Add Decimal Place


Number Format: Delete Decimal Place


Click this icon to delete the final decimal place of a number in the selected cell(s). The display will be updated with the rounded number.


Number Format: Delete Decimal Place


Sheet Area


The reference of the active cell or selected cell area is displayed in the sheet area. The area name will be displayed here if you select a named area.

If you enter a formula, you can select one of the ten last-used functions from this drop-down list box. You can include one of the functions in the entry field of the Formula bar. The program notices both entries, from the Function AutoPilots and the direct entries. In the latter, only functions will be considered that are not in parenthesis, for instance, functions that are a parameter of other functions.


Sheet area field

Cell references can be entered directly in the input line. For individual cells, enter the reference (e.g. F1), and the cursor jumps to the respective cell. For an area reference, enter the cell range (e.g. A1:C4), and the corresponding area of the sheet will be highlighted.


Sum


Click here to activate the sum function. First, place the cursor in the cell where you want the result of the formula to be inserted. Generally, StarOffice can automatically determine the area of cells to be totaled, as long as the cells contain data. Adjacent data areas are totaled by column or by row, unless the sum has already been calculated. In this case, the data area ends at this point. You can combine identical sums in the same column/row to subtotals or totals.

Click the Accept icon to apply the formula displayed in the input line. The Sum icon is only visible when the input line is ot activated.


All values in mathematical operations are rounded according to the settings defined under Tools - Options - Spreadsheet - Calculation. For this reason, slight deviations may occur. (See the following example.)


Example of rounded values:

10.3 *1.75 =

18.03 (rounded)

18.025 (actual)


15.7 *2.75 =

43.18 (rounded)

43.175 (actual)



61.21 (rounded)

61.20 (actual)



The result displayed is the rounded value 62.21.


Sum function


Function


Click here to insert a formula. Set the cursor in the cell that will contain the formula. Click this icon in the Formula Bar. Enter the formula in this bar or select the cell(s) which will contain the cell reference in the formula. You can toggle between both as often as you want.

This symbol is only visible if the input line is not activated.


Function


Input Line


Enter a formula directly into this section of the Formula Bar. Numbers or text can be entered here directly which are then adopted into the row or create your formula by using the Function AutoPilot. (Of course, you can also enter values and formulas directly into a cell, even if no cursor is visible.)

After entering the required values, press Enter or click Accept to insert the result in the active cell. If you want to clear your entry in the Input line, press (Esc) or click Cancel.



When you edit a formula containing references, the references and the corresponding cells in the sheet are highlighted with the same color. You can enlarge the reference range in the sheet by using your mouse to drag the area to the desired size, and the reference in the formula is automatically updated. Reference highlighting can be switched off under Tools - Options - Spreadsheet - Input.




If you would like to view the calculation of individual elements of a formula, select the respective elements and press (F9). For example, in the formula =SUM(A1:B12)*SUM(C1:D12) select the section SUM(C1:D12) and press (F9) to view the subtotal for this area.


If an error occurs when creating the formula, an error message appears in the active cell.


Input line


Cancel


Click here to reject the changes to the cell contents. The original contents will be displayed again in the formula field.


Discarding cell contents


Accept


Click here to accept the contents of the formula bar and insert it into the work area. The work area is then applied to the current sheet cell. Select this icon if you want to finish the edition of the sheet cell.


Apply


Theme Selection


The Theme Selection dialog enables you to replace certain cell styles. These cell styles are custom styles that have fix names and define among others things, fonts, border and background color of cells.

Open the dialog by clicking the Choose Themes icon on the Main Toolbar.


Theme Selection

Click an entry from the large list box and you'll immediately see the effects in the current spreadsheet. Click the OK button if you want to accept the theme's style.

Working with the Theme Selection


Document Position


The number of the active sheet and the total number of worksheets in the document is displayed here.

Sheet X/Y represents the number of the active sheet (X) according to the order of the sheet tabs, and the total number of sheets (Y) in the document.


Don't confuse the sheet number with the automatically assigned sheet name. If you insert two new sheets without changing the predefined names, Sheet3 will be the active sheet. However, since it appears at the far left in the order (new sheets are always inserted to the left of the active sheet, and automatically become active), the display will show: Sheet 1/3.



Standard Formula, Date/Time, Error Warning


The field at the right of the status bar displays information about the current document. Usually, the standard formula SUM is displayed here. The standard formula SUM allows you to always see the sum of the selected cells. If an error occurred in the current cell, the error code appears here, as soon as the cell is selected.

You can use the context menu in this field to choose another standard formula: Mean, Number of Values (COUNT2), Amount of Numbers (COUNT), Maximum, Minimum or None. If you choose None, the date and time will be displayed in this field instead of a formula

If you select entire rows or columns with very many numbers, it may be too time-consuming to calculate the total or another function and display the result in the status bar. In this case, select None.


Increase Scale


Click here to increase the scale of the page view. You can see the current scale in the scale field of the Status Bar. The maximum of the scale is 400%.


Zooming out


Reduce Scale


Click here to reduce the scale of the page view. You can see the current scale in the scale field of the Status Bar. The minimum of the scale is 20%.


Zooming in


Insert


Click here to open a floating toolbar to insert pictures and special characters.

Main Toolbar icon:


Insert

After selection of a function on the Floating Bar, the Main Toolbar shows the icon for the last inserted function. Drag the icon from the Main Toolbar to display a Floating Bar with various options.

You can select the following functions:

Insert Graphics

Insert Special Character


Insert Cell


Click here to open a floating toolbar to insert cells, rows and columns.

Main Toolbar icon:

After selection of a function on the Floating Bar, the Main Toolbar shows the icon for the last inserted function. Drag the icon from the Main Toolbar to display a floating bar with various options.

You can select the following functions:

Insert Cells Down

Insert Cell Right

Insert Rows

Insert Columns


Spreadsheet Shortcut Keys


This section contains the shortcut keys specifically for use in spreadsheets.

The shortcut keys ShiftOptionAlt + Enter are used in spreadsheets to fill selected areas with the content of the cell currently active. Select an area in the spreadsheet, enter a value or formula and conclude it by using these shortcut keys. The entire selected area will be filled. If you also hold down the Shift key at the same time, the cell format of the input cell will be applied to the whole of the area.

If, after you have created an area and entered a value or a formula, you press the shortcut keys ShiftCommandCtrl + Enter, you will create a matrix area in which all the cells will contain the same information as the entry. A matrix area is protected and its components parts cannot be modified.

The CommandCtrl key is used when selecting individual cells with the mouse to enable multiple areas to be defined. The cells selected with your mouse as you hold down the control key will combine to give a multiple area.

If you want to simultaneously edit or print several tables of the document currently active, select them together by holding down the CommandCtrl key and clicking the table rider** at the lower edge of the document. Light-colored table riders show the selected tables, gray table riders denote that they have not been selected. There is also a command in the context menu for selecting all of the tables. If you click the table rider of the currently selected table while holding down the Shift key, only this table will be selected.

Use the shortcut keys CommandCtrl + Enter to insert a manual line break. You can, independent of this, activate the check box Line break located under Format - Cells... Alignment, which automatically breaks the text flow at the right column margin.

Navigating in Spreadsheets

Shortcut Keys

Effect

CommandCtrl + Pos1

Positions the cursor in cell A1

CommandCtrl + End

Positions the cursor in the last cell containing data on the spreadsheet. If the last row containing data is row 10 and the last column in which values are found is column F, the cursor will be positioned in cell F10.

Pos1

Positions the cursor in the first column (A) of the row currently selected.

End

Positions the cursor in the last column containing data of the currently selected row.

CommandCtrl + x
* is the multiplication sign on the numeric key pad

Selects the entire range in which the cursor is located. A range is a contiguous area of cells containing data.

Enter - in a selected range

Positions the cursor in the next cell when in a range. The direction the cursor moves can be defined under Tools - Options... - Spreadsheet - Input.

CommandCtrl + Left Arrow

Goes to the column at the left of the current range or goes to the previous range. If a range does not exist, then goes to the first column (A).

CommandCtrl + Right Arrow

Goes to the column at the right of the range currently selected or goes to the next range. If a range does not exist then goes to the last column (IV).

CommandCtrl + Up Arrow

Goes to the uppermost row of the range currently selected or goes to the previous range. If a range does not exist, then goes to the first row (1).

CommandCtrl + Down Arrow

Goes to the last row of the current range or goes to the next range. If a range does not exist, it goes to the last row (32000).

CommandCtrl+ Page Up

Go to previous spreadsheet

CommandCtrl + Page Down

Go to next spreadsheet

OptionAlt + Page Up

Scrolls one screen page to the left

OptionAlt + Page Down

Scrolls one screen page to the right

Spreadsheet Functions Using Function Keys

Function Key

Modifier Key

Meaning/Function/Calls

F2


Toggles to Edit mode


CommandCtrl

Opens the Function AutoPilot

F3


Inserts names


CommandCtrl

Calls up the Define Names dialog

F4


Opens Beamer (current database)


Shift

Rearranges the relative/absolute references in the input field (A1, $A$1, $A1, A$1)

F5


The Navigator


Shift

Traces dependents

F6




Shift

Traces precedents

F7


Starts spell checking


CommandCtrl

Starts thesaurus

F8


Additional selection on/off


Shift

Extend Selection on/off


CommandCtrl

Value Highlighting

F9


Recalculates


CommandCtrl

Update Chart

F11


Stylist


Shift

Creates templates


Shift + Command + Ctrl

Updates templates

F12


Grouping on


CommandCtrl

Grouping off


Formatting Spreadsheets Using Shortcut Keys

The following cell formats can be set directly from the keyboard:

Shortcut Keys

Effect

CommandCtrl + Shift + 1

Two decimal places, thousands separator

CommandCtrl + Shift + 2

Standard exponential format

CommandCtrl + Shift + 3

Standard date format

CommandCtrl + Shift + 4

Standard currency format

CommandCtrl + Shift + 5

Standard percentage format (two decimal places)

CommandCtrl + Shift + 6

Standard format

Shift +OptionAlt + Enter

Use this to set completed entries into a selected range if the previous format is incompatible with the new number format.

CommandCtrl + *
(*) is the multiplication sign on the numeric key pad

Selects the current area

OptionAlt + Down Arrow

Increases the height of current row

OptionAlt + Up Arrow

Decreases the height of current row

OptionAlt + Right Arrow

Increases the width of the current column

OptionAlt + Left Arrow

Decreases the width of the current column

OptionAlt + Shift + Arrow Key

Sets the column width or row height to the optimal width or height based on the contents of the cell currently active



What do the error codes in incorrect inputs mean?


Here you can see an overview of the changes in StarOffice Calc in comparison to earlier StarOffice versions. These changes relate to computing behavior and may therefore affect the results of older StarOffice Calc documents. If you want to import older StarOffice Calc documents, you should note that not all functions or formulas are 100% compatible with earlier versions. Other values may thus be assumed in your document in some cases, or a value may be returned instead of an error code.

The changes are listed separately for each version. This gives you a better overview.





What do the error codes in incorrect inputs mean?


The most important changes in the computing behavior of StarOffice Calc introduced between StarOffice Version 3.1 and StarOffice 4.0 are listed here.

Comparison operators and matrices

It has become possible to compare ranges and matrices (for example A1:B3=1) and return a matrix in this case whose elements have the value 0 if the match criterion is not satisfied for this item, or 1 if it is met.

Range references in formulas

You can indicate a range reference (=A1:B3) without any additional operators in formulas. The value of the upper-left corner of the module. If used in a matrix formula, a matrix is returned ({=A1:B3}).

Other

Internal handling of passed parameters has been thoroughly revised to avoid rounding off errors, for example. Additional new features include:


What do the error codes in incorrect inputs mean?


The most important changes in the computing behavior of StarOffice Calc introduced in StarOffice Version 4.0 for ServicePack 2 are listed here.

Comparison operations with empty cells

Empty cells are interpreted in match procedures both as numeric 0 and as a null string, depending on whether a comparison of a number or a string is being performed. Please note that a numeric 0 must not be confused with a null string.

For example, if Cell A1 is a null cell, then the following applies: A1="" returns TRUE and A1=0 returns TRUE.

If Cell A1 contains the value 0, then: A1="" returns FALSE and A1=0 returns TRUE.

Search with regular expressions

When searching for regular expressions, the speed of the search has been considerably improved over previous versions. An added feature is that now functions of the database category can also process regular expressions as search criteria.

Searching with regular expressions is thus supported by the following functions:

Database category
DSUM, DCOUNT, DCOUNTA, DAVERAGE, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP

Table category
MATCH, LOOKUP, VLOOKUP, HLOOKUP

Mathematics category
COUNTIF, SUMIF

Text category
SEARCH.

Functions and parameters

From StarOffice 5.0 on, certain functions may exhibit incompatibilities with older versions in terms of their definition and error handling of parameters. You should note the following changes:

The INDIRECT table/spreadsheet function

As a parameter, in addition to a cell reference, you can also pass a range reference as a string parameter now. The parameter is always interpreted correctly as a reference so that the INDIRECT function can now be used in all functions that expect a reference.

Information functions

Error handling has been thoroughly revised for functions of the information category, making it possible to query errors within a formula. For example, =ISERROR(1/0) now returns TRUE instead of an error message. Previously error queries were only possible from a practical point of view by reference to a formula cell, for example =ISERROR(A1) if A1 is equal to 1/0.

This change affects the functions ERRORTYPE, ISREF, ISERR, ISERROR, ISFORMULA, ISNONTEXT, ISBLANK, ISLOGICAL, ISNA, ISTEXT, ISNUMBER, N, and TYPE.


What do the error codes in incorrect inputs mean?


The most important changes in the computing behavior of StarOffice Calc introduced between StarOffice Service Pack 2.0 and StarOffice 5.0 are listed here.

Rounding off accuracy

To avoid errors in rounding off, addition and subtraction with self-negating values that are "almost unequal" in respect to the amounts now return a plain 0.0 (example: 0.1 + 0.2 - 0.3 = 0.0).

It should be noted that rounding off accuracy for multiple summands can result in the system no longer determining an even 0.0.

In addition to all formulas in which the operators for addition (+) and subtraction (-) are used, this change affects the SUM, AVERAGE, DSUM, DAVERAGE and SUMIF functions.

Range references as cell references

Functions that expect an individual cell reference as a parameter and cannot process a range reference, determine a position-dependent cell reference from that range reference instead of setting an error code.

This cell reference is the result of an extended column/row position of the formula cell intersecting with the range indicated. If there is an exact cell at the point of intersection, it is taken. If there is no intersection of column/row position with the range or if there are several intersections (the range is multicolumn/multirow in "inverse direction"), an error is reported (#VALUE!).

The information =A1:B3 is now position-dependent on the referenced range if it is not used in a matrix formula.

Matrices and range references

The calculation behavior has been changed for the following operators if range references (for example A1:A3) are given as parameters:

Addition (+), subtraction (-), multiplication (*), division (/), Exponentiation (^), character chaining and comparison operators (=, <>, <, >, <=, >=).

These operators previously always generated a matrix with a range reference; now a matrix is generated only if the formula is a matrix formula. If it is not a matrix formula, an individual cell reference is generated from the indicated range in spite of that (see above) and is used (as for the other functions that expect cell references and do not handle ranges). The cell ranges are determined by the intersection of the reference range with the row or with the column in which the formula is located. If there is no intersection or if the range on the intersection includes several columns or rows, an error message is generated (#VALUE!). You will find an example of this in the description of Matrix Functions.

The incompatibility with previous versions is automatically resolved when importing old documents. This is done by converting formulas in which the the operators named above are used with range references to single-column or single-row matrix formulas.

Matrix comparison with empty cells

The same rules apply for a matrix formula (resulting from providing ranges with comparison operators within a matrix formula, {=A1:B3>0} for example) in regard to empty cells as for individual comparison, namely that they can be either numeric 0 or a null string.

Functions and parameters

From StarOffice 5.0 on, certain functions may exhibit incompatibilities with older versions in terms of their definition and error handling of parameters. You should note the following changes:

The logical functions AND and OR

The following error handling of parameters has changed for the functions AND and OR: A literal string in the formula results in an error message (#VALUE!), but a string in a referenced cell does not. If no logical value (internal number) can be found within any of the cell/range references provided, and error (#VALUE!) is reported. All parameters are traversed in order to set any resulting error codes (for example, by accepting values from referenced formula cells). Previously AND returned the value FALSE as soon as one value was FALSE and OR returned TRUE as soon as one value was TRUE.

The table functions COLUMN, ROW, COLUMNS, and ROWS

The COLUMN and ROW functions return a single-row or a single-column matrix if a range reference is given as a parameter. If they are not used in a matrix formula, the first value is always taken as the result. If you have used these formulas in your documents, please note that they are incompatible with StarOffice 5.0 due to the following changes:

Other

The POWER function has been extended to the "^" operator; the syntax is identical.

The parameter for indicating the numbering period (NPER) for the financial/mathematical functions CUMPRINC and CUMIMPT can now accept non-integer values.


What do error codes mean when entries are faulty?


This describes StarOffice error codes. In addition to the error message, the cleartext and a short description of the error will appear. The error code will be displayed along with the note Err:. If the cell with the error is active, the cleartext for the error will appear on the Status Bar.

Error Code

Cleartext

Explanation

501

Invalid character

The error is a character that is invalid in this context, e.g. =1Eq instead of =1E2.

502

Invalid argument

A function argument has an invalid value, e.g. a negative number for the root function.

503

Invalid floating point operation

Division by 0 or other calculation that would result in an overflow of the defined value range.

504

Parameter list error

A function parameter is an invalid type, e.g. text instead of number, or domain reference instead of cell reference

505

Internal syntax error

Not used

506

Invalid semicolon

Not used

507

Error: Pair missing

Not used

508

Error: Pair missing

For example, closing parenthesis without opening parenthesis or missing closing parenthesis in the middle of the formula (missing closing parenthesis at the end of a formula is added automatically)

509

Missing operator

E.g. =2(3+4) * operator missing between 2 and (

510

Missing variable

Two operators in a row, e.g. =1+*2

511

Missing variable

The function requires more variables than are provided, e.g. AND() and OR() without parameter

512

Formula overflow

Compiler: as the name indicates, the internal number of tokens that has nothing to do with the formula string length is indicated (max. 512) rather than the number of operators, variables, parentheses, etc. Interpreter: Formulas that create several matrices at once (max. 150), including basic functions that would receive too large an array as a parameter (max. 0xFFFE, i.e. 65534 bytes)

513

String overflow

Compiler: an identifier in the formula exceeds 255 characters. Interpreter: a result of a string operation exceeds 255 characters

514

Internal overflow

Sort operations with too much numerical data (max. 100000) or calculation stack overflow

515

Internal syntax error

Not used

516

Internal syntax error

A matrix is expected on the calculation stack but is not available

517

Internal syntax error

Unknown OpCode, e.g. a document with a newer function is loaded in an older version that does not contain the function

518

Internal syntax error

A variable was supposed to be popped from the calculation stack but is not available

519

No result (#VALUE is in the cell rather than Err:519!)

A function could not deliver a value that corresponds to the definition or a cell referenced in the formula contains text rather than a number

520

Internal syntax error

The compiler created an unknown compiler code

521

Internal syntax error

No result on the calculation stack

522

Circular reference

A formula refers directly or indirectly to itself and the iterations are not activated under Tools/Options/Table Document/Calculate

523

The calculation procedure does not converge

Some financial statistics functions miss a targeted value or iterations of circular references do not reach the minimum change within the maximum steps set

524

Invalid reference (#REF is in the cell rather than Err:524)

Compiler: a column/row description name could not be resolved. Interpreter: in the formula, a cell is referenced whose column, row or table was deleted.

525

Invalid name (#NAME is in the cell rather than Err:525)

Compiler; An identifier could not be resolved (no reference, no domain name, no column/row description, no macro, etc.) Interpreter: same as runtime, e.g. basic function or AddIn, not found

526

Internal syntax error

Obsolete, no longer used, but could come from old documents if the result was a formula from a domain

527

Internal overflow

Interpreter: references too encapsulated (cell references cell, the cell references, the cell references,...)



Contents of the StarOffice Calc Help


StarOffice Calc is the program for editing spreadsheet documents. In the following, you will find a description of the StarOffice help topics for working with spreadsheets. Click this hyperlink for learning to work using the help.










Back to StarDesktop


Menus


While working with spreadsheets you will find menu commands for editing your documents.












File


This menu contains the commands that are used on the document as a whole. For example, you can create a new document, open, close, print an existing one, enter document information and much more. If you wish to close you StarOffice session simply click on the menu command Exit.


Open...





Save As...



Versions...


Properties...



Print...

Printer Setup...



Edit


This menu contains the commands for undoing the last action, copying and pasting into the clipboard, calling up the Navigator that assists you in moving through the document and various other functions for editing directories and embedded objects. Furthermore you can find the commands for automatically filling cells, deleting content, cells and tables and for moving and copying tables.







Paste Special...



Compare Document...

Find & Replace...


Headers & Footers...



Delete Contents...

Delete Cells...

Delete Sheet...

Copy/Move Sheet...


Links...



Image Map



View


The View menu combines those commands that are used to determine how the spreadsheet window and the document contents are to be displayed on the screen. You can also determine which toolbars are to be shown and the size of the document, e.g. zoomed to the entire screen for easier editing.

Zoom...


Beamer










Insert


In this menu you will find a summary of all commands used to insert new elements into a document, e.g., cells, rows, columns, tables and cell names, as well as a list of the respective categories and functions. Special characters, graphics, and objects from other applications can also be inserted.


Cells...



Sheet...

Special Character...

Function...

Function List


NoteNote...



Chart...

Floating Frame


Format


The Format menu contains the commands needed for the formatting of the currently selected Object in your document. The commands displayed depends on the object that has been selected. If a sheet cell is activated then you will see the commands for formatting the elements contained in the sheet. If the object selected is a graphic or a frame then this menu contains the commands relevant to this object.

Furthermore you will find the functions for handling templates and styles in this menu. This includes, e.g the catalog and the Stylist.


Cells...





Line...

Area...

Text...

Position and Size...

Control Field...

Form...











Character...

Paragraph...

Page...


Style Catalog...


AutoFormat...

Conditional Formatting...


Tools


This menu is used to start the spellcheck for the text contained in the cells or the thesaurus which informs you about alternate words. You can set the Detective to tracing sheet references and errors, and start the Goal Seek or define Scenarios. The macro programming can be called up and the toolbar, keyboard and general display of the system can be configured.


Thesaurus...


AutoCorrect...

Goal Seek...

Scenarios...



Macro...

Configure...



Window


The Window menu contains commands for opening and closing windows and also a quick way to switch between open documents.










Further commands for handling windows can be accessed via the context menu of the status bar.


Data


This menu contains the commands for editing the data in your sheet document. You can define areas, sort and filter the data, have results calculated, outline data and call up the DataPilot.

Define Area...

Select Area...

Sort...


Subtotals...

Validity...

Multiple Operations...

Consolidate...



Refresh Area...


Attach


The Attach menu is only visible when the current document is a message and the cursor is not situated in the message text. You can attach one or more files to the new message. This could be a StarOffice document or a graphic.

The required files can also be dragged from the Beamer into the Attachment window of the message. If you wish to, you can even drag several files simultaneously.

File...


Toolbars


A description of the elements of the toolbars in an active sheet document can be found here.














Spreadsheet Object Bar


The Spreadsheet object bar provides most of the functions for hard formatting, that is formatting without using styles. Use the Format - Default command to remove hard formatting from the selected cells.




Font Color

Align Left

Align Center Horizontally

Align Right

Justify









Align Top

Align Center Vertically

Align Bottom


Object bar when an object is selected


The object bar includes the most important functions for formatting and aligning selected objects. This object bar is visible when you select an object, graphic or drawing that has been inserted into a spreadsheet. You can customize it as you want. You can insert or remove the icons you want. The View - Toolbars Customize... menu makes it easier than you think. You can also open this dialog via Tools - Configure... and the Customize... button on the tab Toolbars.



Line Style

Line Width

Line Color

Fill Style/Color








Object bar with text cursor in an object


This object bar displays the icons for formatting text when the cursor is positioned in an inserted text frame. This object bar is displayed when you set the cursor in an object by double-clicking it in order to write text in the object.




Font Color

Line Spacing: 1

Line Spacing: 1.5

Line Spacing: 2

Align Left

Centered

Align Right

Justify

Superscript

Subscript

Font Attributes

Format: Paragraph


Formula Bar


Enter calculation formulas in this toolbar.








Status Bar


The status bar displays information relevant to the active document and also offers some buttons with special functions.

The status bar can be customized (Tools - Configure...), as with all other toolbars. Normally, you will see the fields with the following meaning:










Page View Object Bar


The Page View bar contains icons relevant for the page view functions (accessible via the Page View command in the File menu).








Page Format



Main Toolbar


The Main Toolbar is on the left margin of the opened StarOffice document. This toolbar contains important functions for your daily work with StarOffice.





Form

Choose themes


Search On / Off


Sort

Euro Converter



What's new in this version?


This section describes the new as well as the modified program functions. The first part briefly explains the new functions in StarOffice Calc.

New AutoPilot Euro Converter

A new AutoPilot can convert all StarOffice Calc documents in a directory or certain cells in the current document into Euro. All cells which have a particular currency will be converted. You select which currency in a listbox of the converter.

The Currency Converter found among the StarOffice Calc sample documents is now capable of updating non-fixed exchange rates from the Internet daily. Of course, you can still enter the exchange rates by hand if you so choose.

Datapilot accesses StarOffice Base

The datapilot, which provides you with various views of the data from your tables, can now access StarOffice Base tables directly and access queries. You no longer have to first import the database tables to StarOffice Calc.

Less Complex Menu Structure

Also in StarOffice Calc, the Intuitive Use Technology will help you achieve desired results faster. Depending on the actions you carry out, you'll always find exactly those menu commands and icons that you need in that situation. Menu commands that aren't necessary are hidden. You can prevent this from happening if you want: Go to Tools - Options... - General - View and put a check in the check box at Inactive menu items.

Improved Export to HTML

When a hyperlink is contained in a cell, it will be written as a hyperlink when exported according to HTML. In the past it would have been exported as normal text.

New and Improved Templates and Samples

The Stock Manager in the StarOffice Calc templates now also supports zero buying and selling prices, e.g. for bonus shares or complete losses. Stock splits can also now be taken into account.

Themes let you change layouts in a wink

If your spreadsheet document was created on the basis of certain Cell Styles (e.g. as in all sample documents), you can switch the layout of the Style with a simple mouse click. In the new Themes Manager, numerous attractive layouts are available for you to choose from and you can also add your own.

Improved import and export of MS Excel 97/2000 documents

Also in StarOffice Calc, the MS Office import and export filters have been improved yet again. The Excel export filter has been completely rewritten and provides much more functionality.

Import

Export

  • DataPilot Spreadsheets

  • Charts

  • New Chart Types

  • Graphics

  • Conditional Formatting

  • Sections with Names

  • AutoShapes

  • AutoShapes

  • OLE Objects

  • OLE Objects

  • References to deleted sections (deleted tables, columns)

  • References to deleted sections (deleted tables, columns)

  • More Functions/Formulas


New Functions in the AutoPilot: Functions

The Convert function is used to convert units. The relationships between units are stored in the convert.ini file. It is now possible to use both, the global file share/convert.ini and the local file user/convert.ini. In this way, you have access to the global converter factors that are constantly updated by the system administrator in a network and be able to enter and use their converter factors.

The new FORMULA (reference) function enables you to convert a formula that's used in a cell as a character set.

New Form Functions Menu

Form functions now have their own format menu and toolbar.

New Optional Settings for Links

On the Other tab of the Options dialog, you can specify whether you want links to be automatically updated when a document is loaded.

RTF Clipboard Export

An RTF Clipboard Export was implemented so that Insert - Area - Link/DDE can also work for Calc areas. You can also select the RTF format with Edit - Insert Contents.

Currency Formats Applied to Formulas

In calculations using currencies, the formula line applies the currency format and displays it unless a different format was set. In the past, the system currency format was used. Old versions, in which such documents are loaded will continue to show this functionality; therefore, inconsistencies between currencies displayed might happen. The new functionality is also used for basic arithmetic operations (+-*/); only when dividing the divisor will not accept this format.


Overview of StarOffice Calc


This section contains a short overview of the extensive functionality of StarOffice Calc. If you already have a good working knowledge of an earlier version of StarOffice Calc then go to the section "What's new in this version?"

StarOffice Calc has the following features!

Calculate

StarOffice Calc is primarily a spreadsheet program. This means that you have a spreadsheet in front of you and you can enter text, digits and mathematics formulas and have the results displayed at any time. StarOffice Calc has numerous automatic functions to assist you in your work. If, for example, you wish to know the sum of several numbers in the spreadsheet then simply select these number and look in the status bar: the sum will already be on display.

Not only does StarOffice Calc provide the basic calculation types but also numerous calculation methods that can be entered interactively with the help of the Function AutoPilot. As you can also enter text and flexibly arrange the cells, you can comfortably style, fill out, calculate and print your own forms with StarOffice Calc.

Database Functions

If your data can be summarized into data records, such as addresses, stock levels, customer orders, video collection or similar then these can also be managed by StarOffice Calc. Even if you do not wish to calculate anything, you can, for example, quickly sort the database range and search your data from specific characteristics, such as maximum or minimum or other details.

Arrange Data

It is easy to display the data you entered (e.g. daily inflows of all registers in your business) in a threaded way. For example, with a few clicks of the mouse, you can arrange the display to show only the weekly or monthly totals or the register 1 totals.

Examining and extrapolating data

The data in your StarOffice Calc tables can be examined in detail in many different ways. StarOffice Calc is extremely useful for creating term papers because it supports many statistical procedures, from regression analyses to confidence intervals. And for daily calculations at work, the integrated time-adjusted financial Functions are helpful, because they can be used to print detailed tables of current credits and loans and other data.

What-if calculations

The ability to change individual factors in a calculation involving several different factors and immediately view the impact on the result is especially interesting. For example, simply by changing the timeframe, interest rate or payment in a credit payment calculation, you can easily see how other factors change simultaneously.

You can also manage larger tables with different scenarios based on different preset conditions.

Charts

It is easy to clearly display the data of the spreadsheet. Simply select the data you want to have shown and click the Insert Chart icon (tool bar Insert Object). The cursor will be given a small chart symbol which tells you that you can now insert a chart. Draw out a frame at the required position, define specific settings in the dialog that opens or simply use the default settings. The chart will then be inserted at the selected position.

Importing and exporting data

Of course, you're not limited to the StarOffice Calc format: You can import data from other spreadsheets (e.g. Lotus and Excel), calculate them in StarOffice Calc and, if you want, output them in different Export formats. Cells defined in Excel with the Center Across Selection attribute are imported as merged cells. Since StarOffice Calc backgrounds cannot contain patterns, they are converted to equivalent gray or color shades during import.



If you save a document containing charts in MS Excel format, the chart will appear in Excel without a title or other similar information.


Exchanging data via a network or the Internet

Instead of fixed values or calculation formulas that refer to other cells in the table, each StarOffice cell displays a Reference to contents of other documents. Even calculation formulas like the following are possible: "Take the value of the 'Sales' cell in document 'Sales96', subtract the value of the 'Cost' cell in document 'Cost96' and enter the result in the current cell. In this example, the documents mentioned do not need to reside on your computer's hard drive; they can be stored on any network server or anywhere on the Internet.

Integrating in StarOffice

The entire StarOffice Suite interacts smoothly. StarOffice Calc tables can easily be used in StarOffice Writer text documents and StarOffice Impress drawings, while text and drawings can be added to tables for illustration purposes. Controlling the modules does not require any user changes; everything is "streamlined."