OpenOffice Calc Tips

A quick way to fill a range of cells with a series of values that increase/decrease in a geometric fashion.

(more…)

You have lots of dates to enter in your spreadsheet. This can be tedious when you have to format it correctly. Here is a formula that allows to to quickly enter dates as text.

In the example below, we enter text in column B and convert to dates in column C. The conversion formula for C3 is
=DATEVALUE(LEFT(B3;2)&”/”&MID(B3;3;2)&”/”&RIGHT(B3;2))

Tomorrow, we will look at how to do this in a macro.

A basic function that retrieves information on a specified cell - in particular the formatting.

The syntax is CELL(INFO_TYPE; REFERENCE)

Info_type is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional.

Reference is the position of the cell to be examined. If Reference is a range, the cell moves to the top left of the range. If Reference is missing, OpenOffice.org Calc uses the position of the cell in which this formula is located. Microsoft Excel uses the reference of the cell in which the cursor is positioned.

The examples below show the different type of information that can be obtained for a cell

Often, in financial statements, we wish to express totals as ‘Thousands’ or ‘Millions’. In Calc, we use custom formatting to do this.

In the example below, we use the number 1234567 to illustrate how the custom formats affect how this is displayed.

Firstly, #,## controls the use of commas. Secondly, 0 controls the number of digits after the decimal point. Finally, the trailing commas control the rounding of the number to thousands and millions.

There are two methods available for concatenating text.

The ampersand symol & can be used as a concatenation operator on text - as shown below. This is analogous to the + operator for numbers.

There is also a CONCATENATE function - which can be used on up to 30 strings. The CONCATENATE function is also shown in the example below.