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

To display the Navigator, open a document and choose Edit - Navigator. Alternatively, click the Navigator icon in the Function bar or press (F5). If the Navigator overlaps areas of text in your document, you can drag the window to a different position. Just click anywhere on the title bar and drag the window where you want it. If you don't want to dock the Navigator at the new position, keep the (Ctrl) key pressed 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 the letter of the row header in this spin box. To move the cursor to the specified row, press Enter.

Data Area

Use this command to select the current data area (dependent on the cursor 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.

Insert as Copy

In the Copy mode you can drag and drop a copy from the Navigator into a document.

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.

In the Navigator context menu you will find the Display command, which opens a submenu with the same entries as in the Open Documents list box. Select which document information you want to display in the Navigator. If you select Active Window, the display in the Navigator is automatically updated when you switch the active document. In addition, with the Drag Mode command you can specify whether to insert a hyperlink, a shortcut link, or a copy with the drag & drop function. Under Outline Level, you can define how many levels of the hierarchy to display in the Navigator.


Headers & Footers


Use this command to define and format the headers and footers in 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

Here you can insert strings or a format code to display or print the contents of the document.

Center Area

Here you can insert strings or a format code to display or print the contents of the document.

Right Area

Here you can insert strings or a format code to display or print the contents of the document.

Text Attributes

Use this dialog to assign formats to selected cells, or before you enter new text. This dialog is similar to the Format - Cells - Character page respectively tab(Depends on platform) . 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 actual document.


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




Down


Fills the cells below the selected cell or cells with the contents of the selected area.


Right


Fills the cells to the right of the selected cells with the contents of the selected area.


Up


Fills the cells above the selected cells with the contents of the selected area.


Left


Fills the cells to the left of the selected cells with the contents of the selected area.


Fill Sheet


Here you can select the options for copying sheets or areas of sheets.


Fill Series


Use the options in this dialog to create a data series.

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.

Type

Here you can specify the type: 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.

Day

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

Weekday

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

Month

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

Year

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

Start Value

Specify a start value for the series of data here.

Stop Value

Specify a stop value for the series of data here

Increment

Enter a step value (increment) for the series of data that you want to generate.


Delete Contents


Specify the contents of a cell or an area of cells to be deleted.

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.

Numbers

Check this box to delete only numbers.

Date & Time

Check this box to delete only date and time values.

Formulas

Check this box to delete only formulas.

Notes

Check this box to delete only notes.

Formats

Check this box to delete only applied format attributes of cells.

Objects

Check this box to delete only objects in cells.


Delete Cells


Here you can delete the selected cells, or entire columns or rows.

Selection

Specify here how to shift the remaining cells not 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 (at least one cell must be selected).


Delete Sheet


Deletes the entire sheet (after a confirmation dialog).


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.

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

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.


Row break


Removes the manual row break above the active cell.


Column Break


Removes a manual column break to the left of the active cell.


Column & Row Headers


Select this command to show or hide the column and row headers.


Value Highlighting


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



Formula Bar


Check this menu item to show or hide the Formula bar.


Page Break Preview


If this menu item is selected, the sheet will be displayed in a special preview mode.

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.

Reset Scale

Sets the scale in the page break preview to 100%.

Add Print Area

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


Row Break


Use this command to insert a manual row break above the active cell.


Column Break


Choose this command to insert a manual column break to the left of the active cell.


Insert Cells


With this dialog you can insert cells or a selected area of cells at the position of the active cell.

Selection

Under Selection you can specify how the cells are inserted.

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.



Rows


Choose this command to insert a new row above the active cell.


Columns


Use this command to insert a new column to the left of the active cell.


Insert Sheet


Enter a name for the new sheet here. You can create a new sheet, or insert an existing sheet from a file.

Position

Before selected sheet

Click here to insert a new sheet before the selected sheet.

After selected sheet

Click here to insert a new sheet after the selected sheet.

Create new

Select this option to create a new sheet, and 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 the Insert File dialog. This dialog is similar to the Open File dialog in the File menu. The box next to the button displays the sheet(s) to be inserted, and the path of the selected sheet appears below.

Link

Check this box to link your document to the file selected in the list box.


Function AutoPilot


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

Functions pagerespectively tab(Depends on platform)

When you activate the Function Autopilot, the Functions pagerespectively tab(Depends on platform) of the dialog is selected. After you choose a category (e.g., Mathematical) and a corresponding function (e.g., Round), you can enter the formula in the input field at the right.

List of Categories and Functions

Category

A list of all categories is displayed in this field. Select a category (e.g. Mathematical) to view a list of corresponding functions. Select All to view all functions in alphabetical order. In addition, you can select Last used to view all categories 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.

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 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 Function AutoPilot, 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 Function AutoPilot. You can then enter a function rather than a value or reference in the field next to the button.

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

The number of text fields visible 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 preview window at the bottom right of the dialog displays the selected function and any nested functions. You can also enter formulas and other information directly in the window.

<<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>>

Click this button to view the compound functions.



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 Function AutoPilot 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 pagerespectively tab(Depends on platform)

On this pagerespectively tab(Depends on platform) you can view the hierarchical structure of the function. This pagerespectively This tab(Depends on platform) appears if you activate the Function Autopilot when the cursor is positioned in a cell that already contains a function.


If you start the Function AutoPilot and the active cell already contains a function, the Structure tab page appears with the input range of the function. You can use this feature as a "Function Inspector," to check the function for accuracy.


Structure

The function is displayed hierarchically with all nested functions, similar to the Explorer display.



Categories and functions


Here you will find a description of the calculation functions in StarOffice.


Category: Database


DCOUNT

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

DCOUNT2

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

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!

DMAX

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

DMIN

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

DAVERAGE

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

DPRODUCT

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

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.

DSTDEV

DSTDEVP calculates the standard deviation of a population based on all cells of a data range which match the search criteria.

DSUM

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

DVAR

DVAR estimates the variance of all values in the cells of a database field in all records which match the specified search criteria.

DVARP

DVARP calculates the variance of all values in the cells of a database field in all records which match the specified search criteria.


Category: Date & Time



DATE

This function converts a date as year, month, or day to an internal number and displays it in the cell.

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.

TODAY

Returns the serial number of the current date.

YEAR

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

NOW

Returns the serial number of the current date and time (the current system setting).

WEEKNUM

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


MINUTE

For a given internal time, this function returns the MINUTE as a number between 0 and 59.

MONTH

Returns the month corresponding to a given serial date.

SECOND

Returns the second corresponding to a given serial time.

HOUR

Returns the hour corresponding to a given serial time.

DAY

Returns the day of the month corresponding to a serial date.

DAYS

Calculates the difference between two date values.

DAYS360

Returns the number of days between two dates based on a standard 360-day year.

WEEKDAY

Returns the day of the week corresponding to a serial date.


TIME

Returns the serial number of a particular time value in hours, minutes and seconds.

TIMEVALUE

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


Category: Financial


PV

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

SYD

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

EFFECTIVE

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

DDB

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

DB

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

IRR

Returns the internal rate of return for a series of cash flows.

NPER

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

CUMIPMT

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

CUMPPMT

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

DURATION

Returns the annual duration of an investment with periodic interest payments.

SLN

Returns the straight-line depreciation of an asset for one period.

NPV

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

NOMINAL

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

PMT

Returns the periodic payment for an annuity.

VDB

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

RATE

Returns the constant interest rate per period of an annuity.

PPMT

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

FV

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

NPER

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


Category: Information


CURRENT

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

ISREF

This functions tests if the content of one or several cells is a reference.

ISERR

Returns TRUE if the value refers to any error value except #N/A.

ISERROR

Returns TRUE if a value refers to any error value (#N/A, #VALUE!, #REF! or #NAME?).

ISFORMULA

Returns TRUE if a cell is a formula cell.

ISNONTEXT

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

ISBLANK

Returns TRUE if the reference to a cell is blank.

ISLOG

This function returns TRUE, if the number format is a logarithm.

ISNV

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

ISTEXT

Returns TRUE if the cell contents refer to text.

ISNUMBER

Returns TRUE if the value refers to a number.

N

Returns a value converted to a number.

NA

Returns the error value #N/A.

TYPE

Returns the type of value.


Category: Logical


FALSE

Returns the logical value FALSE.

NOT

Reverses the logical value.

TRUE

The logical value is set to TRUE.

IF

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


Category: Mathematics


ROUNDDOWN

Rounds a number down, toward zero.

ABS

Returns the absolute value of a number.

COUNTBLANK

Counts empty cells in a specified range of cells.

ARCCOS

Returns the arc cosine of a number.

ARCCOSH

Returns the inverse arc cosine of a number.

ACOT

Returns the inverse cotangent of the given number.

ARCCOTH

Returns the inverse hyperbolic cotangent of the given number.

ASIN

Returns the arcsine of a number.

ARCSINH

Returns the inverse arcsine of a number.

ATAN

Returns the arctangent of a number .

ATAN2

Returns the arctangent of the specified x and y coordinates.

ATANH

Returns the inverse hyperbolic tangent of a number.

ROUNDUP

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

COS

Returns the cosine of the given number (angle).

COSH

Returns the inverse hyperbolic cosine of a number.

COT

Returns the cotangent of the given angle.

COTH

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

DEGREES

Converts radians into degrees.

EXP

Returns e raised to the power of a number.

FACT

Returns the factorial of a number.

INTEGER

Rounds a number down to the nearest integer.

EVEN

Returns a number rounded up to the nearest even integer.

GCD

Returns the greatest common divisor of two or more integers.

ISEVEN

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

ISODD

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

LCM

Returns the least common multiple of one or more integers.

COMBIN

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

COMBIN2

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

TRUNC

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

LN

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

LOG

Returns the logarithm of a number to the specified base.

LOG10

Returns the base-10 logarithm of a number.

PI

Returns the value of PI (3.14).

POWER

Returns the result of a number raised to a power.

PRODUCT

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

SUMSQ

Returns the sum of the squares of the arguments.

RADIANS

Converts degrees to radians.

MOD

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

ROUND

Rounds a number to a specified number of digits.

SIN

Returns the sine of the given number (angle).

SINH

Returns the hyperbolic sine of a number.

SUM

Adds all the numbers in a range of cells.

SUMIF

Adds the cells specified by a given criteria.

TAN

Returns the tangent of the given angle.

TANH

Returns the hyperbolic tangent of a number .

SUBTOTAL

Caluclates subtotals in a table.

ODD

Returns a number rounded up to the nearest odd integer.

FLOOR

Rounds a number down to the nearest multiple of significance.

SIGN

Determines the sign of a number.

SQRT

Returns a positive square root of a number.

RAND

Returns a random number between 0 and 1.

COUNTIF

Counts the number of cells within a range that meet the given criteria.


Category: Matrix


MUNIT

Returns the unitary square matrix of a certain size.

FREQUENCY

Calculates how often values occur within a range of values, and then returns a vertical array of numbers.

MDETERM

Returns the matrix determinant of an array.

MINVERSE

Returns the inverse matrix for the matrix in an array.

MMULT

Calculates the matrix product of two arrays.

MINVERSE

Transposes the rows and columns of a matrix array.

LINEST

Returns the parameters of a linear trend.

LOGEST

In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve (y=b*m^x).

PRODSUM

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

SUMX2MY2

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

SUM2PY2

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

SUMXMY2

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

TREND

Returns values along a linear trend.

VARIATION

Calculates the points of an exponential trend in a matrix.


Category: Spreadsheet


ADDRESS

Returns a cell address (reference) as text, according to the specified row and column numbers.

AREAS

Returns the number of areas in a reference.

DDE

Returns the result of a DDE-based link.

ERRORTYPE

Returns the number corresponding to an error value occurring in a different cell.


INDEX

INDEX returns the content of a cell, specified by row and column number (or an optional area name).

INDIRECT

Returns the reference specified by a text string. This function can also be used to return the area of a corresponding string.

COLUMN

Returns the column number of a cell reference.

COLUMNS

Returns the number of columns in the given reference.

VLOOKUP

Vertical search with reference to adjacent cells to the right.

MATCH

Returns the relative position (as number) of an item in an array that matches a specified value in a ceratin order.

OFFSET

Returns the value of a cell offset by a certain number of rows and columns from a given reference point.

LOOKUP

Returns the contents of a cell either from a one-row or one-column range or from an array.

STYLE

Applies a style to the cell containing the formula.

CHOOSE

Uses an index to return a value from a list of up to 30 values.

HLOOKUP

Search for a value and reference to the cells below the selected area.

ROW

Returns the row number of a cell reference.

ROWS

Returns the number of rows in a reference or array.


Category: Text


Displayed here is the list of available functions under the Text category along with an example for each of them. Functions: CHAR, CLEAN, CODE, CONCATENATE, DOLLAR, FIND, FIXED, IDENTICAL, LEFT, LEN, LOWER, MID, PROPER, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, T, TEXT, TRIM, UPPER and VALUE.

BASIS

Converts a positive integer to text based on the specified number system. All numerals from 0-9 and letters from A-Z are valid.

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

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 that a number be formatted with a fixed number of decimal places and a thousands separator. This function can be used to apply a 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

Enter ...Hello in the Text field and the result will be Hello without the leading spaces???

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 with starcalc in the Text field and the result will be Have Fun With Starcalc.

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 StARcALc in the Text field. The result will be starcalc.

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.

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.

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


Statistics A-E


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.

Syntax

CHIINV(number; degrees_freedom)

Number is a probability associated with the chi-squared distribution.

Degrees_freedom of the chi-squared distribution.

Example

=CHIINV(0.1; 5) returns 9.23. If the chi-squared distribution of a sample with these parameters returns a higher value, it deviates from a coincidental distribution.

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.

Syntax

CHITEST(actual_range; expected_range)

Actual_range is the range of data that contains observations to test against expected data.

Expected_range is the range of data that contains the ratio of the product of row totals and column totals to the grand total.

Example

=CHITEST(A1:A30; B1:B30) compares both ranges of data and calculates chi^2 as equal to the sum of "(actual_range-expected_range)^2 / expected_range" for all data values.

CHIDIST, CHIINV

CHIDIST

Returns the one-tailed probability of the chi-squared distribution.

Syntax

CHIDIST (x; degrees_freedom)

X is the value of the distribution (quartile) whose probability is to be calculated.

Degrees_freedom of the chi-squared distribution.

Example

=CHIDIST10; 6) returns 0.12.

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 F-H


FINV

Returns the inverse of the F probability distribution.

FISHER

Returns the Fisher transformation for x and creates a function close to a normal distribution.

FISHERINV

Returns the inverse of the Fisher transformation for x.

FTEST

Returns the result of an F test.

FDIST

Calculates the values of an F distribution.

GAMMAINV

Returns the inverse of the gamma cumulative distribution.

GAMMALN

Returns the natural logarithm of the gamma function G(x).

GAMMADIST

Returns the probabilities of a gamma distribution.

GAUSS

Returns the standard normal cumulative distribution.

GEOMEAN

Returns the geometric mean of a sample.

TRIMMEAN

Returns the mean of an interior of a data set.

ZTEST

Returns the two-tailed P value of a z test.

HARMEAN

Returns the harmonic mean of a data set.

HYPGEOMDIST

Returns the hypergeometric distribution.


Statistics K-L


LARGE

Returns the k-th largest value in a data set.

SMALL

Returns the k-th smallest value in a data set.

CONFIDENCE

Returns the confidence interval for a population mean.

CORREL

Returns the correlation coefficient between two data sets.

COVAR

Returns the covariance the average of the product of paired deviations.

CRITBINOM

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.

KURT

Returns the kurtosis of a data set (at least 4 values required).

LOGINV

Returns the inverse of the lognormal distribution.

LOGNORMDIST

Returns the cumulative lognormal distribution.


Statistics M-Q


MAX

Returns the maximum value in a list of arguments.

MEDIAN

Returns the median, or the number in the middle of a set of given numbers.

MIN

Returns the minimum value in a list of arguments.

AVEDEV

Returns the average of the absolute deviations of data points from their mean.

MEAN

Returns the averge of the arguments.

MODE

Returns the most common value in a data set.

NEGBINOMDIST

Returns the negative binomial distribution.

NORMINV

Returns the inverse of the normal cumulative distribution.

NORMDIST

Returns the standard normal cumulative distribution.

PEARSON

Returns the Pearson correlation coefficient r.

PHI

Returns the values of the distribution function for a standard normal distribution.

POISSON

Returns the Poisson distribution.

PERCENTILE

Returns the k-th percentile of values in a range.

PERCENTRANK

Returns the percentage rank of a value in a sample.

QUARTILE

Returns the quartile of a data set.


Statistics R-Z


RANK

Returns the rank of a number in a sample.

SKEW

Returns the skewness of a distribution.

FORECAST

Returns the inverse of the normal cumulative distribution.

STDEV

Estimates the standard deviation based on a sample.

STDEVP

Calculates the standard deviation based on the entire population.

STANDARDIZE

Converts a random variable to a normalized value.

NORMSINV

Returns the inverse of the standard normal cumulative distribution.

NORMSDIST

Returns the normal cumulative distribution.

SLOPE

Returns the slope of the linear regression line.

STEYX

Returns the standard error of the predicted y value for each x in the regression.

DEVSQ

Returns the sum of squares of deviations based on a sample mean.

TINV

Returns the inverse of the t-distribution.

TTEST

Returns the probability associated with a Student's t-Test.

TDIST

Returns the t-distribution.

VAR

Estimates the variance based on a sample.

VARP

Calculates a variance based on the entire population.

PERMUT

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

PERMUT2

Returns the number of permutations for a given number of objects (repetition allowed).

PROB

Returns the probability that values in a range are between two limits.

WEIBULL

Returns the values of the Weibull distribution.


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.

In the Sheet Area drop-down box in the Formula bar, you will find a list of defined names. Just click a name to select a reference, and the corresponding area in the sheet will be highlighted.

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.

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

Click this button to add the defined name to the list.



Paste Name


In this dialog, you can insert an area of cells into your spreadsheet. Simply select the name of the area, and the referenced cell will be inserted.


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.


Create Names


You can automatically generate the area names from the cell contents of the selection.

Create name from

Specify the area from which you want to create a name here.

Header

Creates the area names from the header row of the selected area.

Left Column

Creates the area names from the entries in the first column of the selected sheet area.

Footer

Creates the area names from the entries in the last row of the selected sheet area.

Right Column

Creates the area names from the entries in the last column of the selected sheet area.


Define Label Range


In this dialog you can define a label range.

Label range

Here you will see the respective area reference of the existing label ranges.


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.


Add

Click here to add the current defined label area to the list.



Cell Attributes


Use this command to choose from a variety of formatting options and apply attributes to the selected cells.


Cell Protection


Choose various protection options to apply to sheets and cell areas.

Protected

To prevent certain cells from being modified, click here.

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 for Printing

If you choose this option, all selected cell areas in the sheet will be omitted in printing.


Optimal Row Height


Define the optimal row height for the selected rows.

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.


Hide


Use this command to hide rows, columns or individual sheets.



Show


Choose this command to show previously hidden rows or columns.


Optimal Column Width


Specify the optimal column width for selected columns. The optimal column width depends on the largest entry in the spreadsheet. StarCalc offers the use of various 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.


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.

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 previously hidden sheets.

Hidden sheets

Here you will see a list of all hidden sheets in your spreadsheet document.


Define


Merges the selected cell area and treats it as one cell.


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 pagerespectively this tab (Depends on platform) 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. Specify the number for the first printed page 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.

Maximum pages

Choose this option to specify the maximum number of pages on which to print the entire document. The document will be scaled to fit to the defined number of printed pages.


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 Area


Opens a dialog to edit the print area.

Print Area

In this field you can modify a defined print area.


Row title

Choose on or more rows to print as a horizontal title on every page.


Column title

Choose on or more columns to print as a vertical title on every page.



Add


Use this command to add the current selection to the defined print areas.


Style Catalog


Opens the dialog Style Catalog where you can create, edit and organize styles.

Style Type

In the drop-down list, choose whether to show cell or page styles.

List of Styles

Lists all existing styles of the selected style type; the current style in use is selected.

Style Category

To keep an overview, the styles are divided into various style categories.

New...

Click here to open the dialog Cell Style (similar to the Cell Attributes dialog) or Page Style, depending on the selected style type.

Modify...

Click here to open the respective dialog Cell Style (similar to Cell Attributes) or Page Style.

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. The Stylist aids you in applying styles to objects and text. You can either update a style based on the current selection, modify an existing style or create a new style. The Stylist 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 existing Page Styles. Page styles allow you to use soft formatting to define the structure of a page. If you specify a "next style," StarCalc automatically applies a style to each new page after an automatic page break.


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



List of Styles / Style Category / Context menu New... / Modify... / Delete...

Here you have access to the same functions 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.

Format

Choose a predefined AutoFormat to apply to a selected area in your sheet.


Insert

Add the formatting of a selected area (at least 4 x 4 cells) to the list of predefined AutoFormats. Click Insert... to open the Add AutoFormat dialog. Enter a name and press OK.


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 to fit.

Number Format

Clear this check box if the number format should not be changed.

Borders

Clear this check box if you want do not want to change the borders.

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.)

Orientation

Clear this check box if the orientation should stay as it is.

Adjust width and height to fit

Clear this check box if the width and height of the selected cells should not be changed.

More <<

Use this button as well to close the Formatting options section.


Conditional Formatting


Choose Conditional Formatting to define format styles dependent on certain conditions.

Condition 1/2/3

Check this box to define a condition.

Cell value/Formula

Specify if the conditional formatting should be dependent on a cell value or a formula.

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 is met.

Parameter field

In the parameter field (or in both parameter fields if you have selected a condition that requires two parameters) enter a reference, value or formula.

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.



Trace Precedents


This function shows the relationship between the active cell containing a formula, and the corresponding cells used in the formula. StarCalc draws tracer arrows in the spreadsheet and marks the area containing 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 tracer 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.



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.


Mark invalid data


This command marks all cells in the sheet that contain values outside the validation rules.


Refresh Traces


Use this command to redraw all traces in the sheet.


Refresh Traces


Use this command to redraw all traces in the sheet.


Goal Seek


Opens a dialog where you can specify a target value for a selected cell.

Formula cell

In the formula cell, enter the reference of the cell which contains the formula.

Target value

Enter the value you want to acheive 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


In the Create Scenario dialog, you can assign a name and comments to a scenario.

Scenario name

In this field, enter a descriptive name for the scenario. Later, you will select (and edit) the scenario view based on the name displayed on the sheet tabs or in the Navigator.

Comments

Under Comments, you can enter additional information about the scenario. This information will be displayed in the Navigator when you click the Scenarios icon and select the desired scenario.


Protect Sheet


Click here to protect the sheet from being modified.

Password (optional)

To protect your sheet from unauthorized changes, enter a password here.


Protect Document


Protects your entire document from modifications.

Password (optional)

To protect your entire document from unauthorized changes, enter a password here.


Auto Calculate


If you choose this option, formulas will be automatically recalculated, and if necessary, the results will be corrected.


Recalculate


Recalculates the current sheet immediately.


Auto Input


Activate the Auto Complete function to automatically complete the contents of a vertical list. The Auto Complete automatically fills in the remaining text if the first few characters you type match an existing entry in the column. The automatically generated text is displayed inverted.


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.


Freeze


Choose this command to freeze the active window. Freezing a worksheet can be especially useful when working with large sheets, because it keeps the column or row titles in view even when your cursor is in a different area of the worksheet.


Define Database Area


Define a database area in your sheet.

Name

In this field you can define a new database area or select an existing one.

Area

This field displays the area reference of the selected database area.


Add/Modify

Click here to add the new defined database area 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.

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.

Source:

Displays information about the source of the database and any existing operators.


Select Database Area


Select a previously defined database here.

Areas

Here you will find a list of all existing database areas defined in the current document.


Sort


In this dialog you can define the conditions for the sort function.





Sort Criteria


Define the sort criteria, such as sort order, direction and whether to sort columns or rows.

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 switch between ascending and descending sort order.


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 remains unchanged after being sorted, even if the cell contents change. Usually formatting is applied to an entire cell, not the cell contents.

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.

Custom sort order

Choose a custom sort order defined in Tools - Options - Spreadsheet - Custom Lists.

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.


AutoFilter


Use the AutoFilter to select certain values from a list or a databank to be displayed on the worksheet.


Standard Filter / Filter


In this dialog you can specify conditions for filtering data in a sheet.

Operator

Choose between the logical operators AND / OR.

Field name

Choose a field name from the current sheet to be used in the filter.

Condition

From this list box choose a comparitive operator to use between the entries Field name and Value.

Value

This list box contains all possible values for the specified field name.

More>>


Options


Case Sensitive

Choose this option to differentiate between upper and lower case text in a filtered list of values.

Include Column Headers

If you choose this option, the column headers in the first row of a sheet are included in the filter.

Copy results to

Click here to select an area where you want the filter results to appear.


Regular expression

Check this box if you want to use wildcards in the filter.

Unique records only

Check here to exclude duplicate rows in the list of filtered data.

Keep filter criteria

If a target area is specified in the field Copy results to, you can define the filter to be savedrespectively savedrespectively saved(Depends on platform) as filter criteria in a different area in the source area.

Data area

Displays a reference to the data area in the sheet which is to be filtered.

More<<

Click this button again to close the options dialog.


Special Filter


In this dialog you can define an advanced filter with more than three 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.



Remove Filter


Clears a filter set in the selected sheet area.


Hide AutoFilter


Click here to hide the buttons repesenting the AutoFilter defined in the sheet area.


Subtotals


In this dialog you can define the settings for automatic calculation of subtotals.





1st, 2nd, 3rd Group


Define the settings for one of the three subtotal groups.

Group by

Select the column which contains the items by which to subtotal values in other columns.

Calculate subtotals for

Choose the selected columns in the sheet which contain the values to be subtotaled.

Use function

Choose the mathematical function to use for calculating 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

If this option is selected, any changes in capitalization results in recalculation of subtotals.

Pre-sort area according to groups

Sorts the area defined in the Group pagesrespectively Group tabs(Depends on platform) under Group by according to the specified columns.

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 on the pagerespectively in the tab(Depends on platform) Tools - Options - Custom 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.


Table


In this dialog you can specify references to a previously defined sheet area. With a multiple operation you can calculate different values for a cohesive data area defined by only one formula referenced to the area. This formula is located in a cell unconnected to the data area.

:

If the data of the area is arranged in a column or a row, select the data area. Under Column/Row enter the cell reference to the first cell in the data area. In the Formula field enter the reference to the cell containing the formula which refers to the data area.


Formulas

Enter the cell reference containing the formula on which the data table is based.

Row input cell

Enter the input cell reference to be used as a variable for the rows in the data table.

Column input cell

Enter the input cell reference to be used as a variable for the columns in the data table.


Consolidate


With this function you can combine data from several independent table areas. From these areas, a new area can be calculated with a chosen mathematical function.

Function

In this list, select the mathematical function you want to use for the consolidation.

Consolidation areas

In this list, the selected range of cells for the consolidation are displayed.

Source data area

Select any previously defined areas with names, and add them to the list of Consolidation areas .


Copy results to

Here you can select the cell which will serve as the starting point of the data table. The consolidation table will be inserted at this point. In the list field, you can 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


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.


Hide Detail


Click here to hide an outline level in a grouped sheet.


Show Detail


Click here to display hidden sheet area.


Group


Click here to group a selected sheet area.

Rows

Combines selected rows to a group in an outline.

Columns

Combines selected columns to a group in an outline.


Ungroup


Clears the first level of the existing group inserted with the command Group.


Auto Outline


Click here to automatically group a selected range of cells in a sheet.


Clear Outline


Click here to remove a grouping defined manually or with the Auto Outline function.


DataPilot


Use the DataPilot to help summarize and organize large amounts of data in three or more dimensions.

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.

Layout

Here you can define the structure of the table.

More>>

Click this button to expand the dialog to display additional Result options.


Result


Customize how the results of the DataPilot are summarized.

Result

Define the settings for the result of the DataPilot table here.

Results to

To prevent the results table from being positioned directly below the original data, select a defined area from the list box. Then, select a sheet area with the mouse or enter a cell range directly.


If no results area is specified, the DataPilot table will appear directly below the source area, regardless if there is existing data there.


Ignore empty rows

If this option is selected, all empty cells in the original table will 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 of the columns to be displayed.

Total rows

Select this check box if you want the grand total of the rows to be displayed.

More<<

Click here to close the expanded options dialog.


Filter


In this dialog you can define conditions for filtering data in a sheet or table.

Criteria

Operator

Choose between the logical operators AND/OR.

Field name

From the list box, select the field name from the current sheet to be used in the filter.

Condition

Choose a comparative operator to link the entries in Field name and Value.

Value

This list box contains all possible values for the specified field name .

More>>


Options



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 wildcards in the filter.

Unique records only

Check here to exclude duplicate rows in the list of filtered data.


Data field


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.

Function

Click a function in this box to specify the type of subtotals to be calculated.

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.


Delete


Click here to delete the DataPilot table.


Refresh Area


Updates data inserted from an external database.


Validity


In this dialog you can define various validity criteria for a selected sheet area.


Values


Define which values to accept for the validity criteria.

Allow

Define the validity condition for cell data.

Ignore blanks

Click here to allow blank cells in the selected cell area; otherwise each cell must contain a value.

Data

In this list, choose a comparitive operator to use as a condition.

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.

Title

Enter a title for the input message.

Input help

Enter the text of the input message to be displayed under the title.


Error Alert


Define an error message to be displayed when invalid values are entered.

Show error message when invalid values are entered.

Select this check box if you want an error message to be displayed when invalid values are entered.

Action

Choose the type of error dialog to display, or a predefined macro to be executed.

Browse...

Click here to open the Macro dialog, where you can 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.


Currency


Click here to display cell contents in currency format.


Percentage


Click here to display cell contents in percentage format.


Default Number Format


Click here to display cell contents in the default number format.


Increase Decimal


Click here to add a decimal place to a number in the selected cell(s).


Decrease Decimal


Click here to delete the final decimal place of a number in the selected cell(s). The display will be updated to reflect the rounded number.


Sheet Area


The reference of the active cell or selected cell area is displayed in the sheet area. If you select an area for which a name has been defined, the area name is displayed here.


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.




Function


Click here to insert a formula.


Function


Input Line


Enter a formula directly in this section of the Formula bar. You can enter a formula consisting of numbers or text, or create a formula with the aid of the Function AutoPilot. (Of course, you can also enter values and formulas directly into a cell, even if no cursor is visible.)


Cancel


Click here to cancel the changes to the cell contents.


Accept


Click here to accept the contents of the formula bar and insert it into the work area.

Document Position


The number of the active sheet and the total number of worksheets in the document is displayed here.



Standard Formula, Date/Time, Error Warning


The field at the right of the status bar displays information about the current document. Normally, the standard formula SUM appears. 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.


Increase Scale


Click here to increase the scale of the page view.

Reduce Scale


Click here to reduce the scale of the page view.


Insert


Click here to open a floating toolbar to insert pictures and special characters.


Insert Cell


Click here to open a floating toolbar to insert cells, rows and columns.


Spreadsheet Object Bar


The Spreadsheet object bar provides most of the functions for hard formatting, without the use of styles. Use the Format - Standard command to remove hard formatting from the selected cells.


Object bar when an object is selected


The object bar includes the most important functions for formatting and aligning selected objects.










Object bar with text cursor in an object


This object bar displays the icons for formatting text, if the cursor is positioned in an inserted text frame.




Formula Bar


Enter calculation formulas in this toolbar.


Page View Object Bar


The Page View bar contains icons relevant for the page view functions (accessible via the Page View command in the FileFile menu).